+ Post New Thread
Results 1 to 13 of 13
Coding Thread, SQL Command running *very* slowly! in Coding and Web Development; I have a view, which left outer join's 3 tables together yet it it being incredibly slow (as in, over ...
  1. #1

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,680
    Thank Post
    516
    Thanked 2,451 Times in 1,897 Posts
    Blog Entries
    24
    Rep Power
    832

    SQL Command running *very* slowly!

    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:

    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!

  2. #2

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,968
    Thank Post
    112
    Thanked 489 Times in 335 Posts
    Blog Entries
    2
    Rep Power
    283
    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.
    Last edited by jinnantonnixx; 26th November 2012 at 10:40 AM.

  3. 2 Thanks to jinnantonnixx:

    localzuk (26th November 2012), vikpaw (27th November 2012)

  4. #3

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,680
    Thank Post
    516
    Thanked 2,451 Times in 1,897 Posts
    Blog Entries
    24
    Rep Power
    832
    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!

  5. #4

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,968
    Thank Post
    112
    Thanked 489 Times in 335 Posts
    Blog Entries
    2
    Rep Power
    283
    It's a great tool, isn't it? A few more non-clustered indexes and we could even have SIMS running well.
    Last edited by jinnantonnixx; 26th November 2012 at 10:53 AM.

  6. #5

    AngryTechnician's Avatar
    Join Date
    Oct 2008
    Posts
    3,730
    Thank Post
    698
    Thanked 1,212 Times in 761 Posts
    Rep Power
    394
    Quote Originally Posted by jinnantonnixx View Post
    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!

  7. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,851
    Thank Post
    672
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  8. #7

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    21
    Quote Originally Posted by vikpaw View Post
    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.

  9. Thanks to ChrisMiles from:

    jinnantonnixx (27th November 2012)

  10. #8

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,851
    Thank Post
    672
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  11. #9

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,851
    Thank Post
    672
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    I've posted it here @ChrisMiles - cheers . Make SQL Cursor code more efficient

  12. #10

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,968
    Thank Post
    112
    Thanked 489 Times in 335 Posts
    Blog Entries
    2
    Rep Power
    283
    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.
    Last edited by jinnantonnixx; 27th November 2012 at 10:34 AM.

  13. #11

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,968
    Thank Post
    112
    Thanked 489 Times in 335 Posts
    Blog Entries
    2
    Rep Power
    283
    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
    Last edited by jinnantonnixx; 27th November 2012 at 10:14 AM.

  14. #12

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,851
    Thank Post
    672
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    Quote Originally Posted by jinnantonnixx View Post
    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 ?

  15. #13

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,968
    Thank Post
    112
    Thanked 489 Times in 335 Posts
    Blog Entries
    2
    Rep Power
    283
    Quote Originally Posted by vikpaw View Post
    Are you Pablo ?
    Maybe I is, maybe I ain't...

SHARE:
+ Post New Thread

Similar Threads

  1. Sims running very slow
    By wardsidjame in forum MIS Systems
    Replies: 7
    Last Post: 19th October 2009, 02:11 PM
  2. System clock running very fast
    By MK-2 in forum Windows
    Replies: 23
    Last Post: 8th August 2008, 12:59 PM
  3. unix and sql commands
    By danIT in forum General Chat
    Replies: 9
    Last Post: 14th February 2007, 01:19 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •