Slow response when querying a View - Using Linq to SQL

Question

I have the following view:

SELECT     
poHeader.No_ AS PONumber, 
poHeader.[Buy-from Vendor No_] AS VendorNumber, 
poHeader.[Document Date] AS DocumentDate, 
vendor.Name AS VendorName, 
vendor.Contact AS VendorContact, 
vendor.[E-Mail] AS VendorEmail, 
vendor.Address AS VendorAddress, 
vendor.[Address 2] AS VendorAddress2, 
vendor.City AS VendorCity, 
vendor.County AS VendorCounty, 
vendor.[Post Code] AS VendorPostCode, 
vendor.[Phone No_] AS VendorPhone, 
vendor.[Fax No_] AS VendorFax, 
salesHeader.No_ AS SONumber, 
poHeader.[Order Date] AS OrderDate, 
salesHeader.[Crocus Comment] AS CrocusComment, 
salesHeader.GiftMessage, 
salesHeader.[Delivery Comment] AS DeliveryComment, 
salesHeader.[Shipment Date] AS DeliveryDate, 
COALESCE (salesHeader.[Ship-to Name], 
poHeader.[Ship-to Name]) AS DeliveryName, 
COALESCE (salesHeader.[Ship-to Address],
poHeader.[Ship-to Address]) AS DeliveryAddress, 
COALESCE (salesHeader.[Ship-to Address 2],
poHeader.[Ship-to Address 2]) AS DeliveryAddress2,
COALESCE (salesHeader.[Ship-to City], 
poHeader.[Ship-to City]) AS DeliveryCity, COALESCE (salesHeader.[Ship-to County], 
poHeader.[Ship-to County]) AS DeliveryCounty, 
COALESCE (salesHeader.[Ship-to Post Code], 
poHeader.[Ship-to Post Code]) AS DeliveryPostcode, 
salesHeader.DeliveryPhoneNo, poForEmailing.Processed, 
poForEmailing.Copied

FROM         
Navision4.dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing 
LEFT OUTER JOIN
Navision4.dbo.[Crocus Live$Purchase Header] AS poHeader ON poForEmailing.No_ = poHeader.No_ 
INNER JOIN
Navision4.dbo.[Crocus Live$Vendor] AS vendor ON poHeader.[Buy-from Vendor No_] = vendor.No_ 
LEFT OUTER JOIN
Navision4.dbo.[Crocus Live$Sales Header] AS salesHeader ON salesHeader.No_ = dbo.fnGetSalesOrderNumber(poHeader.No_)

This view is created in a database called NavisionMeta It queries a database (on the same server) claled Navision4

I have recently moved both databases on to new (better) hardware. Not sure if this is relevant, but the new hardware has SQL 2008, and the old hardware was running SQL 2000

If I query it using this query in SQL Management Studio it takes over 2 minutes:

SELECT * 
FROM [NavisionMeta].[dbo].[PurchaseOrders]
WHERE Processed=0 AND Copied=0

Which is wayyy too long!

The following query in LINQ times out all-together, even if I adjust the timeout to 5 mins!

            var purchaseOrdersNotProcessed = (from p in db.PurchaseOrders
                                              where p.Copied.Equals(0)
                                              && p.Processed.Equals(0)
                                              select p).ToList();

What puzzles me, is, on the previous hardware, it worked fine!

Just in case it is relevant, the udf used above is:

CREATE FUNCTION [dbo].[fnGetSalesOrderNumber](@PONumber varchar(20))
RETURNS varchar(20)
AS
BEGIN

RETURN (
SELECT 
    TOP 1 [Sales Order No_]
FROM 
    Navision4.dbo.[Crocus Live$Purchase Line]
WHERE 
    [Document No_] = @PONumber
)

Answer 1

A starting point might be to look at the execution plan for the query on both the old machine and the new machine. There will certainly be optimization differences with newer versions of SQL Server. The execution plan might show you that an index is necessary that, for some reason, was not as critical in the previous version.

Answer 2

You also might consider updating statistics.

Answer 3

EDIT

I'm not sure what you are trying to do, but if you are trying to get information on PO's I think the changes below will help, You have

FROM Navision4.dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing 
  LEFT OUTER JOIN Navision4.dbo.[Crocus Live$Purchase Header] AS poHeader ON poForEmailing.No_ = poHeader.No_ 
  INNER JOIN Navision4.dbo.[Crocus Live$Vendor] AS vendor ON poHeader.[Buy-from Vendor No_] = vendor.No_ 
  LEFT OUTER JOIN Navision4.dbo.[Crocus Live$Sales Header] AS salesHeader ON salesHeader.No_ = dbo.fnGetSalesOrderNumber(poHeader.No_)

Try this:

FROM Navision4.dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing 
  LEFT JOIN Navision4.dbo.[Crocus Live$Purchase Header] AS poHeader ON poForEmailing.No_ = poHeader.No_ 
  LEFT JOIN Navision4.dbo.[Crocus Live$Vendor] AS vendor ON poHeader.[Buy-from Vendor No_] = vendor.No_ 
  LEFT JOIN Navision4.dbo.[Crocus Live$Sales Header] AS salesHeader ON salesHeader.No_ = dbo.fnGetSalesOrderNumber(poHeader.No_)

As far as speed goes, you probably need to update some indexes... esp the No_ field. In addition make the following change to get rid of the fuGetSalesOrderNumber() called on each row:

;WITH PurchaseLineByPO AS
(
  SELECT MAX([Sales Order No_]) as SO, [Document No_] as DNum
  FROM Navision4.dbo.[Crocus Live$Purchase Line]
  Group By [Document No_]
)
--blah blah whole select goes here.. with
JOIN PurchaseLineByPO ON DNum = poHeader.No_
--in the join and 
LEFT OUTER JOIN
Navision4.dbo.[Crocus Live$Sales Header] AS salesHeader ON salesHeader.No_ = PurchaseLineByPO.SO
--replaces what you had

See if that works for you.

Ignore old stuff below...

It is hard for me to see exactly what you are doing here, but it is rare OUTER joins are needed -- is this actually what you want to do? If it is, you can reverse the order and have an inner join. For example you say:

FROM         
Navision4.dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing 
LEFT OUTER JOIN
Navision4.dbo.[Crocus Live$Purchase Header] AS poHeader ON poForEmailing.No_ = poHeader.No_ 

and you could say

FROM         
Navision4.dbo.[Crocus Live$Purchase Header] AS poHeader 
LEFT INNER JOIN
Navision4.dbo.[Crocus Live$Purch_ orders for e-mailing] AS poForEmailing ON poForEmailing.No_ = poHeader.No_ 

Depending on your data this could have a significant impact on your run time.

Answer 4

Left Inner Join? Hmm, not sure if that would help at all...

If this query was working before (data not performance) then the query above would benefit from turning all of your joins into INNER JOINs. Because you're INNER JOINing vendor on a value from poHeader (which is outer joined), you have essentially made an inner join requirement on poHeader as well (except with a potential performance hit for the outer join). Vendor can't return a value unless poHeader has a value and since vendor is inner joined, the entire row will be disregarded if there is no value in poHeader. Same with salesHeader. The function used in the join requires a value in poHeader (which has to have a value according to above logic), so this join would also benefit from being made into an explicit INNER JOIN instead of just an implied one.

Other than that, I do agree with the statements about indexes (except for the one about outer joins rarely being needed. That's like saying you don't need a screwdriver if you have a hammer). Indexing sounds like the most logical explanation for the weaker performance. Specifically you should check if your poForEmailing aliased table has an index on ([processed],[copied]). Without that index you can expect your query time to at least double as the data size doubles because every record in that table will need to be tested against those predicates. Regarding your original question, I haven't noticed anything in SQL Server 2008 that would suggest such a change in performance, all other things being equal.