Post reply

Query execute too long, any help

  • January 11, 2006 at 9:39 am

    #111202

    Hi guys...

    I have built up a query which takes 40-50 secs to run on server (xeon, raid 5, 2 gb ram).

    Based on client claim I should reduce the time to perform the query.

    I have tested it on test server () it takes +-2 min and execution plan was setted.

    Going through the Estimated Execution Plan I have found out that:

    lots of "Clustered Index Seek" show:

    Estimated Cost: >= 250%

    and some

    "Stream Aggregate / Aggregate" : Estimated cost >= 150%.

     

    This query has lot Query subQuery:

    SELECT field_1, field_2, select(select()), select(Select()), function(select()), field_n.

    What is the best strategy to reduce time of execution. (Rebuild index?) or what do you suggest?

    Any help will be very appreciated.....

  • January 11, 2006 at 10:31 am

    #614120

    >>SELECT field_1, field_2, select(select()), select(Select()), function(select()), field_n.

    >>What is the best strategy to reduce time of execution.

    When you place a sub-SELECT within a SELECT, you are building a performance nightmare-in-waiting that is essentially a cursor-based solution. The sub-SELECTs need to be evaluated for every row in the main SEELCT, which will always give orders of magnitude worse performance than an efficient set-based solution.

    You need to get those sub-SELECTS into the FROM clause, using derived tables if necessary.

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply