Sql – LinqToSql Producing Different Sql Queries on Different Machines for Identical Code

asp.netlinq-to-sqlnetsql

I have a website built using Asp.net and LinqToSql for Data Access. In a certain section of the site, LinqToSql produces a query that looks like this (from my dev machine):

select ...
from table1
left outer join table2 on table1 where ...
left outer join table3 on table2 where ...

Since the connection between table2 and table1 is not always there, the left outer join is appropriate in this situation. And since the link between table3 and table1 goes through table2, it also needs a left outer join. This sql returns the correct recordset.

I just put the code up to a server. Running the identical code in the same scenario, LinqToSql produces the following query:

select ...
from table1
left outer join table2 on table1 where ...
join table3 on table2 where ...

For some reason, it renders the join between table2 and table3 as an inner join, instead of an outer join. This results in zero records being returned from the query.

Both dev machine and server are using .Net 3.5 SP1. Dev machine is Vista64, Server is Windows Server 2003 SP2. A colleague of mine using Windows XP PRO also confirmed the same correct behavior on their dev machine.

Can anyone think of a reason why the server would create different sql? How can I fix this? It seems to be something tied into the way that Linq and .Net is running on the server. However, I can't think of any way to confirm and fix this.

Linq Code (I am only including the parts that are relevant to the section where the sql changed):

from Import_Table t in db.Import_Tables
select new {
   CheckedOutUser = (!t.IsCheckedOut) ? "--" : t.Import_CheckoutHistory.System_User.FirstName + " " + t.Import_CheckoutHistory.System_User.LastName,
   CheckedOutUserID = (!t.IsCheckedOut) ? 0 : t.Import_CheckoutHistory.System_UserID};

In the context of the description above, table1 = Import_Table, table2 = Import_CheckoutHistory, table3 = System_User. If I comment out the line here that begins with "CheckedOutUser = …" then it works on the server – so this is definitely the culprit.

Actual sql returned:

SELECT 
  (CASE WHEN NOT ([t0].[IsCheckedOut] = 1) THEN CONVERT(NVarChar(401),'--') ELSE ([t2].[FirstName] + ' ') + [t2].[LastName] END) AS [CheckedOutUser], 
  (CASE WHEN NOT ([t0].[IsCheckedOut] = 1) THEN 0 ELSE [t1].[system_UserID] END) AS [CheckedOutUserID]
FROM [dbo].[import_Table] AS [t0] 
LEFT OUTER JOIN [dbo].[import_CheckoutHistory] AS [t1] ON [t1].[import_CheckoutHistoryID] = [t0].[import_CheckoutHistoryID] 
LEFT OUTER/INNER JOIN [dbo].[system_User] AS [t2] ON [t2].[system_UserID] = [t1].[system_UserID]

On the dev machines, the last line begins with "Left outer". On the server, the last line begins with "Inner"

Update: My solution is below

Best Answer

Is your production database different to your development one, e.g. SQL Server 2008 instead of 2005? I believe LINQ to SQL will vary the SQL it generates based on the actual execution-time database it's talking to.

Also, are the schemas exactly the same on both databases?

Related Solutions

Sql – How to Join to first row

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY:

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
CROSS APPLY
        (
        SELECT  TOP 1 LineItems.Quantity, LineItems.Description
        FROM    LineItems
        WHERE   LineItems.OrderID = Orders.OrderID
        ) LineItems2

Please note that TOP 1 without ORDER BY is not deterministic: this query you will get you one line item per order, but it is not defined which one will it be.

Multiple invocations of the query can give you different line items for the same order, even if the underlying did not change.

If you want deterministic order, you should add an ORDER BY clause to the innermost query.

Example sqlfiddle

Sql – INNER JOIN vs LEFT JOIN performance in SQL Server

A LEFT JOIN is absolutely not faster than an INNER JOIN. In fact, it's slower; by definition, an outer join (LEFT JOIN or RIGHT JOIN) has to do all the work of an INNER JOIN plus the extra work of null-extending the results. It would also be expected to return more rows, further increasing the total execution time simply due to the larger size of the result set.

(And even if a LEFT JOIN were faster in specific situations due to some difficult-to-imagine confluence of factors, it is not functionally equivalent to an INNER JOIN, so you cannot simply go replacing all instances of one with the other!)

Most likely your performance problems lie elsewhere, such as not having a candidate key or foreign key indexed properly. 9 tables is quite a lot to be joining so the slowdown could literally be almost anywhere. If you post your schema, we might be able to provide more details.

Edit:

Reflecting further on this, I could think of one circumstance under which a LEFT JOIN might be faster than an INNER JOIN, and that is when:

Some of the tables are very small (say, under 10 rows);
The tables do not have sufficient indexes to cover the query.

Consider this example:

CREATE TABLE #Test1
(
    ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL
)
INSERT #Test1 (ID, Name) VALUES (1, 'One')
INSERT #Test1 (ID, Name) VALUES (2, 'Two')
INSERT #Test1 (ID, Name) VALUES (3, 'Three')
INSERT #Test1 (ID, Name) VALUES (4, 'Four')
INSERT #Test1 (ID, Name) VALUES (5, 'Five')

CREATE TABLE #Test2
(
    ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL
)
INSERT #Test2 (ID, Name) VALUES (1, 'One')
INSERT #Test2 (ID, Name) VALUES (2, 'Two')
INSERT #Test2 (ID, Name) VALUES (3, 'Three')
INSERT #Test2 (ID, Name) VALUES (4, 'Four')
INSERT #Test2 (ID, Name) VALUES (5, 'Five')

SELECT *
FROM #Test1 t1
INNER JOIN #Test2 t2
ON t2.Name = t1.Name

SELECT *
FROM #Test1 t1
LEFT JOIN #Test2 t2
ON t2.Name = t1.Name

DROP TABLE #Test1
DROP TABLE #Test2

If you run this and view the execution plan, you'll see that the INNER JOIN query does indeed cost more than the LEFT JOIN, because it satisfies the two criteria above. It's because SQL Server wants to do a hash match for the INNER JOIN, but does nested loops for the LEFT JOIN; the former is normally much faster, but since the number of rows is so tiny and there's no index to use, the hashing operation turns out to be the most expensive part of the query.

You can see the same effect by writing a program in your favourite programming language to perform a large number of lookups on a list with 5 elements, vs. a hash table with 5 elements. Because of the size, the hash table version is actually slower. But increase it to 50 elements, or 5000 elements, and the list version slows to a crawl, because it's O(N) vs. O(1) for the hashtable.

But change this query to be on the ID column instead of Name and you'll see a very different story. In that case, it does nested loops for both queries, but the INNER JOIN version is able to replace one of the clustered index scans with a seek - meaning that this will literally be an order of magnitude faster with a large number of rows.

So the conclusion is more or less what I mentioned several paragraphs above; this is almost certainly an indexing or index coverage problem, possibly combined with one or more very small tables. Those are the only circumstances under which SQL Server might sometimes choose a worse execution plan for an INNER JOIN than a LEFT JOIN.

Best Answer

Related Solutions

Sql – How to Join to first row

Sql – INNER JOIN vs LEFT JOIN performance in SQL Server

Related Topic