[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Reducing MSSQL query execution Time

Posted on 2006-03-20
4
Medium Priority
?
388 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
0
Comment
Question by:drunkenlogic
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 total points
ID: 16234586
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
 

Expert Comment

by:mari3719
ID: 16234720
Try to place in VB code
Ex: docmd.runsql " Select ......"
or
broken sql in two query separatley
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question