Have Geek Brain

Why is this stored procedure fast in Management Studio but not in the application?

I have this query as a stored procedure:

SELECT     ID
    FROM         dbo.tblRentalUnit
    WHERE     (NOT EXISTS
        (SELECT     1 AS Expr1
        FROM          dbo.tblTenant
        WHERE      (dbo.tblRentalUnit.ID = UnitID)))

In Microsoft SQL Server Management Studio Express, it runs in 16ms. When I have this on a typed dataset automatically generated by Visual Studio 2008, it runs in 64,453ms. More than a minute.

Estimated and executed plans are as follows:

Select [0%] <- Filter [1%] <- Merge Join (Left Outer Join) [28%] <- Index Scan [16%]
                                                                 <- Sort [43%] <- Clustered Index Scan [12%]

Why is this difference here and how can I fix it?

+1


a source to share


3 answers


This sounds like an incorrectly cached query plan.

Are your indices and statistics up to date?



BTW, if tblTenant.UnitId is the Foriegn key to tblRentalUnit.ID then your request can be rewritten as:

SELECT ru.ID    
FROM         
    dbo.tblRentalUnit ru
    LEFT JOIN dbo.tblTenant t ON ru.ID = t.UnitID
WHERE
    t.UnitID IS NULL
+2


a source


Have you loaded buffers before every test (from any client)? Make sure you do DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE between each test.



0


a source


Is tblRentalUnit.ID indexed correctly?

0


a source






More articles:


All Articles