Reducing MSSQL query execution Time

Posted on 2006-03-20
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
                      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)

Question by:drunkenlogic
    LVL 75

    Accepted Solution

    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

    Expert Comment

    Try to place in VB code
    Ex: docmd.runsql " Select ......"
    broken sql in two query separatley

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    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…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now