We help IT Professionals succeed at work.

Reducing MSSQL query execution Time

drunkenlogic
drunkenlogic asked
on
Medium Priority
411 Views
Last Modified: 2008-02-01
i have this query which has result as paged and cross joined
but the execution time of the query is alarmingly  high ie 15 to 25 seconds
how can I reduce this
please help!!!!!!!!!1

SELECT     TOP 15 THREAD.threadID, COALESCE (POST.postDateTime, THREAD.threadDateTime) AS lastpostDatetime, COALESCE (POST.userID, THREAD.userID)
                      AS lastpostUserID
FROM         THREAD LEFT OUTER JOIN
                      POST ON THREAD.threadID = POST.threadID AND POST.postID IN
                          (SELECT     TOP 1 postid
                            FROM          post postfirst
                            WHERE      postfirst.threadid = thread.threadid
                            ORDER BY postdatetime DESC)
WHERE     (THREAD.forumID = 1) AND (THREAD.threadID NOT IN
                          (SELECT     threadid
                            FROM          (SELECT     TOP 15 A.threadid, COALESCE (B.postDateTime, A.threadDateTime) AS lastpostDatetimeA
                                                    FROM          THREAD A LEFT OUTER JOIN
                                                                           POST B ON A.threadID = B.threadID AND B.postID IN
                                                                               (SELECT     TOP 1 postid
                                                                                 FROM          post postfirstA
                                                                                 WHERE      postfirstA.threadid = A.threadid
                                                                                 ORDER BY postfirstA.postdatetime DESC)
                                                    WHERE      (A.forumID = 1)
                                                    ORDER BY lastpostDatetimeA DESC) DERIVEDTBL))
ORDER BY lastpostDatetime DESC


the first TOP 15 is the pagesize
and the second TOP 15 is the pagesize* (pagenumber-1)


AND THIS IS THE EXECUTION PLAN FOR IT
http://img24.imagevenue.com/img.php?loc=loc143&image=53861_executionplan.jpg
Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Your execution plan shows that, there are a lot of Table Scans. And you uses IN and NOT IN operations.
There are two things sthat you should do,
1. Try to replace the IN and NOT IN operations
2. Also run index tuning wizard for this query, to create proper indexes

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Try to place in VB code
Ex: docmd.runsql " Select ......"
or
broken sql in two query separatley
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.