SQL Command running *very* slowly!

[localzuk]

I have a view, which left outer join's 3 tables together yet it it being incredibly slow (as in, over a minute to return 300 transactions). Last week, it was working fine though.

 

Anyone got any ideas what could be causing it?

 

Here is the code:

 

SELECT     dbo.Transactions.ID, dbo.Transactions.PupilID, dbo.Transactions.TransactionType, dbo.Transactions.Amount, dbo.Transactions.DateIn,                       dbo.Transactions.Operator, dbo.Transactions.AuthMethod, dbo.Transactions.Till, dbo.Transactions.FSM, dbo.Transactions.Complimentary_Meal, 
                     dbo.Transactions.[user], dbo.Transactions.Void, dbo.Transactions.Balance, dbo.Transactions.PurseType, dbo.ParentMailTransactionsSent.ID AS Expr1,
                      dbo.ParentMailTransactionsSent.TransactionID, dbo.ParentMailTransactionsSent.ParentMailSent
FROM         dbo.Transactions LEFT OUTER JOIN
                     dbo.ParentMailTransactionsSent ON dbo.Transactions.ID = dbo.ParentMailTransactionsSent.TransactionID LEFT OUTER JOIN
                     dbo.ParentMailAccount ON dbo.Transactions.PupilID = dbo.ParentMailAccount.PupilID
WHERE     (dbo.ParentMailTransactionsSent.TransactionID IS NULL) AND (dbo.ParentMailAccount.ParentMailAccountID IS NOT NULL) AND 
                     (dbo.ParentMailAccount.DateTime < dbo.Transactions.DateIn)

 

Basically, it gets all records in 'Transactions' which haven't got a record in 'ParentMailTransactionsSent' and also which are newer than the date in ParentMailAccount.

 

I know Left Outer Joins are a bit slow, but not this bad!

[jinnantonnixx]

Time to break out the execution plan.

 

Right-click the query window, then click Display Estimated Execution Plan.

 

It will open a diagram showing you the individual portions of your query, along with the 'cost' of each of the subquery items. Everything together adds up to 100% of course, but if you see a single item taking up say 50% you can concentrate on this.

 

More importantly, it will give you index hints and the code needed to create the indexes. I'm not one to sing Microsoft's praises often, but the tool set in SQL is very good.

 

When things perform poorly, the Execution Plan is a good friend.

Edited November 26, 2012 by jinnantonnixx

[localzuk]

Woop! Thanks for that! I had forgotten an index in a table. It caused a 64% increase in execution. Added the index in, and its all working properly.

 

Must pay attention in future!

[jinnantonnixx]

It's a great tool, isn't it? A few more non-clustered indexes and we could even have SIMS running well. Edited November 26, 2012 by jinnantonnixx

[AngryTechnician]

It's a great tool, isn't it? A few more non-clustered indexes and we could even have SIMS running well.

 

Now that's just wishful thinking!

[vikpaw]

Cool, now help me understand it. My query loops through my photo db using a cursor for any records modified after a fixed time, then it goes through the cursor items and dumps the photo to the C drive.

 

The cursor declaration takes up 76%! Made up of 47% Clustered Index Insert and 52% Clustered Index Scan. I have an index on the PK.

[ChrisMiles]

Cool, now help me understand it. My query loops through my photo db using a cursor for any records modified after a fixed time, then it goes through the cursor items and dumps the photo to the C drive.

 

The cursor declaration takes up 76%! Made up of 47% Clustered Index Insert and 52% Clustered Index Scan. I have an index on the PK.

 

Cursors are terrible, don't use them unless there is absolutely no other alternative. If you absolutely must use one, make sure you declare it properly, so it's read only and forward moving only to speed things up.

 

Maybe post full source in a new thread and we'll see what we can do.

[vikpaw]

To be fair it was a google for a solution hack job, which works quite well. Never used a cursor before, just thought it was a nifty way to loop through the data.

[vikpaw]

I've posted it here @ChrisMiles - cheers . http://www.edugeek.net/forums/coding/104929-make-sql-cursor-code-more-efficient.html#post899530

[jinnantonnixx]

The trouble is, you look at SQL forums and the replies invariably say that cursors are the worst thing in the world!, and yet the replies fail to provide a viable solution to the OP's problem. Instead they'll post a trivial example. It's infuriating.

 

You have to change your programming practices to think of things in sets rather than rows. I have a great site bookmarked (or so I thought) but for the life of me I can't find it. I'll post the link when I find it.

Edited November 27, 2012 by jinnantonnixx

[jinnantonnixx]

Found it.

 

Looking again, it's more an exercise in dynamic SQL, but it deliberately avoids cursors. It's not an ideal example, but it's good nonetheless.

 

Less Than Dot - Blog - How to get information about all databases without a loop

Edited November 27, 2012 by jinnantonnixx

[vikpaw]

Found it.

 

Looking again, it's more an exercise in dynamic SQL, but it deliberately avoids cursors. It's not an ideal example, but it's good nonetheless.

 

Less Than Dot - Blog - How to get information about all databases without a loop

 

Are you Pablo ?

[jinnantonnixx]

Are you Pablo ?

 

Maybe I is, maybe I ain't...