Why does this stored procedure execute quickly in Management Studio, but not in an application?

Question

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 executes in 16 ms. When I have it in a typed dataset auto-generated by Visual Studio 2008, it executes in 64,453 ms. More than a minute.

Estimated and Execution plan are like this:

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 correct it?

Answer 1

It sounds like an incorrectly cached query plan.

Are your indexes and statistics up to date?

BTW, if tblTenant.UnitId is a Foriegn Key into tblRentalUnit.ID then your query 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

Answer 2

Did you flush the buffers before each test (from either client)? Make sure you are executing DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE between each test.

Answer 3

Is tblRentalUnit.ID properly indexed?