none
Generated SQL Different between Server and Localhost Using Same LINQ Query RRS feed

  • Question

  • Hi,  I have the following sub which works like a charm when ran on my local machine (windows xp sp3) but throws an sql timeout exception when run from my development server (windows server 2003 R2 SP2 ).  I noticed that the version provider has a very minor difference but when I run windows update on the server it reports back that no SQL server updates are available.  Here is the code along with the generated sql.  I have tried cleaning, rebuilding, & running custom tool on the dbml file all to no avail.  And yes, both instances are using the exact same connection string. Any ideas?


    '*********************** VB Sub ****************************************************

    Private Sub LoadData()

    Dim db = DataAccessLayer.Context
    Dim sw = New System.IO.StringWriter

    db.Log = sw

    Dim thrityDays = New TimeSpan(30, 0, 0, 0)
    Dim thrityDaysAgo = DateTime.Now.Subtract(thrityDays)

    Dim goodEmps = From p In db.tblPEMains _
    Where p.FirstPE = True _
    And p.EditLocked = True _
    Select p.Badge

    Dim noObjectives = From p In db.Employees, z In db.Employees, x In db.tblEmpBus _
    Where Not goodEmps.Contains(p.BadgeNbr) _
    And p.StartDate < thrityDaysAgo _
    And p.EmpMgrs.Mgr1Badge = z.BadgeNbr _
    And x.BuCode = p.BuCode _
    Select p.LastName, p.FirstName, p.BuCode, p.Title, p.BusinessUnit, _
    HireDate = Format(p.StartDate, "short date"), ManagerID = p.EmpMgrs.Mgr1Userid, _
    ManagerFirst = z.FirstName, ManagerLast = z.LastName, HRGeneralist = x.HRGenUserId

    Try
    Me.GridView1.DataSource = noObjectives.ToList
    Me.GridView1.DataBind()
    Catch ex As Exception

    Finally
    Me.TextBox1.Text = sw.ToString
    'Me.TextBox1.Text += db.Connection.ConnectionString

    End Try

     

    End Sub




    '********************* Localhost Generated SQL (Works Great) ***************************************
    SELECT [t0].[LastName], [t0].[FirstName], [t0].[BuCode], [t0].[Title], [t0].[BusinessUnit], [t0].[StartDate] AS [Expression], [t3].[Mgr1Userid] AS [ManagerID], [t1].[FirstName] AS [ManagerFirst], [t1].[LastName] AS [ManagerLast], [t2].[HRGenUserId] AS [HRGeneralist]
    FROM [dbo].[Employee] AS [t0]
    CROSS JOIN [dbo].[Employee] AS [t1]
    CROSS JOIN [dbo].[tblEmpBu] AS [t2]
    LEFT OUTER JOIN [dbo].[EmpMgr] AS [t3] ON [t3].[BadgeNbr] = [t0].[BadgeNbr]
    WHERE (NOT (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[tblPEMain] AS [t4]
        WHERE ([t4].[Badge] = [t0].[BadgeNbr]) AND ([t4].[FirstPE] = (@p0)) AND ([t4].[EditLocked] = (@p1))
        ))) AND ([t0].[StartDate] < @p2) AND ([t3].[Mgr1Badge] = [t1].[BadgeNbr]) AND ([t2].[BuCode] = [t0].[BuCode])
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    -- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [4/1/2009 10:32:54 AM]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1


    '********************* Server Generated SQL (Times Out)   *****************************************
    SELECT [t0].[LastName], [t0].[FirstName], [t0].[BuCode], [t0].[Title], [t0].[BusinessUnit], [t0].[StartDate] AS [Expression], [t3].[Mgr1Userid] AS [ManagerID], [t1].[FirstName] AS [ManagerFirst], [t1].[LastName] AS [ManagerLast], [t2].[HRGenUserId] AS [HRGeneralist]
    FROM [dbo].[Employee] AS [t0]
    CROSS JOIN [dbo].[Employee] AS [t1]
    CROSS JOIN [dbo].[tblEmpBu] AS [t2]
    LEFT OUTER JOIN [dbo].[EmpMgr] AS [t3] ON [t3].[BadgeNbr] = [t0].[BadgeNbr]
    WHERE (COALESCE(
        (CASE
            WHEN (NOT (EXISTS(
                SELECT NULL AS [EMPTY]
                FROM [dbo].[tblPEMain] AS [t5]
                WHERE ([t5].[Badge] = [t0].[BadgeNbr]) AND ((COALESCE(
                    (CASE
                        WHEN ([t5].[FirstPE] = (@p0)) AND ([t5].[EditLocked] = (@p1)) THEN 1
                        WHEN NOT (([t5].[FirstPE] = (@p0)) AND ([t5].[EditLocked] = (@p1))) THEN 0
                        ELSE NULL
                     END),@p2)) = 1)
                ))) AND ([t0].[StartDate] < @p3) AND ([t3].[Mgr1Badge] = [t1].[BadgeNbr]) AND ([t2].[BuCode] = [t0].[BuCode]) THEN 1
            WHEN NOT ((NOT (EXISTS(
                SELECT NULL AS [EMPTY]
                FROM [dbo].[tblPEMain] AS [t5]
                WHERE ([t5].[Badge] = [t0].[BadgeNbr]) AND ((COALESCE(
                    (CASE
                        WHEN ([t5].[FirstPE] = (@p0)) AND ([t5].[EditLocked] = (@p1)) THEN 1
                        WHEN NOT (([t5].[FirstPE] = (@p0)) AND ([t5].[EditLocked] = (@p1))) THEN 0
                        ELSE NULL
                     END),@p2)) = 1)
                ))) AND ([t0].[StartDate] < @p3) AND ([t3].[Mgr1Badge] = [t1].[BadgeNbr]) AND ([t2].[BuCode] = [t0].[BuCode])) THEN 0
            ELSE NULL
         END),@p4)) = 1
    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    -- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
    -- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [4/1/2009 10:34:06 AM]
    -- @p4: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

    Friday, May 1, 2009 4:16 PM

Answers

All replies