1

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?

Malfist
  • 31,179
  • 61
  • 182
  • 269
  • If you need more information about the setup, please let me know! – Malfist May 26 '09 at 13:59
  • Why is this getting voted down? – Malfist May 26 '09 at 17:42
  • Probably something involving blue sky, green grass, and the sun rising in the east. – TheTXI May 26 '09 at 17:55
  • That's always a good reason...I guess. – Malfist May 26 '09 at 18:20
  • I honestly don't know why that is getting down-votes; looks OK to me (although possibly a dup, but nobody has voted dup)... you might also want to read the answers here: http://stackoverflow.com/questions/801909/ – Marc Gravell May 26 '09 at 20:04
  • I saw that, that's where I got the 'SET ARITHABORT ON', which I commented on Mitch Wheat's answer. That still doesn't bring me up to the same speed as I have in SSMS, although it improved it by about 59 seconds, it's still executing lots and lots slower than SSMS. About 366 times slower. – Malfist May 26 '09 at 20:22

3 Answers3

2

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
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • How do I check and see if my indexes and statistics are up to date? According to the properties of the database, they're auto-updated – Malfist May 26 '09 at 14:14
  • Switching to this query and setting ARITHABORT ON; dropped the execution time down to 5859 ms in the application, but it still isn't the same as how it would execute in Management Studio – Malfist May 26 '09 at 14:21
  • In SSMS take a look at the Estimated and Actual Execution plans for differences (maybe post here) – Mitch Wheat May 26 '09 at 14:47
  • There is no difference between the two. I'll append it to my question though – Malfist May 26 '09 at 15:00
0

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

James World
  • 29,019
  • 9
  • 86
  • 120
0

Is tblRentalUnit.ID properly indexed?

andrewbadera
  • 1,372
  • 9
  • 19