Reducing MSSQL query execution Time

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
LVL 1
drunkenlogicAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
mari3719Commented:
Try to place in VB code
Ex: docmd.runsql " Select ......"
or
broken sql in two query separatley
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.