Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    1

    Unanswered: What is taking so long?

    Hello.

    I have a query that takes 1,5second to execute, but only 150ms of CPU. The query is quite simple, just one where statement against a clustered index.

    SQL Server Execution Times:
    CPU time = 156 ms, elapsed time = 1595 ms.


    Code:
    SELECT column1, column3, column4, ..., column10 FROM table WHERE column2 IN (37, 41, 43, 45, 49, 53, 55) ORDER BY column3 DESC


    Code:
    |--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
         |--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=37 OR [u].[searchparamid]=41 OR [u].[searchparamid]=43 OR [u].[searchparamid]=45 OR [u].[searchparamid]=49 OR [u].[searchparamid]=53 OR [u].[searchparamid]=55 OR [u].[searchparamid]=59) ORDERED FORWARD)



    I have tried to rewrite the query to an INNER JOIN instead.

    Code:
    |--Sort(TOP 1000, ORDER BY:([u].[LastActivityDate] DESC))
         |--Nested Loops(Inner Join, OUTER REFERENCES:([spal].[number]))
              |--Index Seek(OBJECT:([MP].[dbo].[__search_parameters_lookup].[IX___search_parameters_lookup] AS [spal]), SEEK:([spal].[hash]=-1726604993) ORDERED FORWARD)
              |--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS [u]), SEEK:([u].[searchparamid]=[spal].[number]) ORDERED FORWARD)

    but the query still takes 1,5 seconds.


    It spends 59% (according to execution plan) of sorting. 14% for the index seek of the __search_parameters_lookup table and then 24% of a clustered index seek of the __searchtest table.


    How come it only uses that small of CPU but it still takes 1,5 seconds? It seems to be reading from memory as well so it shouldnt be an IO-problem?

    The index I have on the table is a clustered index on (column 2).

    Any ideas of how I can improve this? I have tried with DTA, also with a non clustered index on column3.

    If I remove some columns from the SELECT-list the query will execute alot faster:

    SQL Server Execution Times:
    CPU time = 32 ms, elapsed time = 32 ms.

    Booth the CPU and the elapsed time goes down and now appears to be more normal.

    So there seems to be a problem caused by data transfer.
    I tried to do a remake and normalize the table and when I do that I get the query execute with a speed of 400ms CPU and 400ms total. And this is still the exact same result, so why does it only spend 400ms of "rendering" or fetching the data when the tables are normalized but 1500ms when its denormalized?

    Any ideas?

    I am running Microsoft SQL Server 2000 - 8.00.2039

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do you have to order the results or can the client handle that?

    you might want to look into a covering index.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Posts
    26
    Yes I need to order the result.

    I want to get 1000 posts from a table of ~150000 posts and to return 150000 posts to the client and then sort does not seems like a good option.

    I could try with a covering index but that means nearly all columns in the table

    Any other ideas?

  4. #4
    Join Date
    Jan 2003
    Posts
    26
    The problem with a covering index is that I have to cover 25 columns. SQL-Server 2000 can only handle 16 as I understand it.

    I understand that SQL-Server 2005 has a feature to include columns called nonkey columns that works almost like covering indexes and supports up to 1023 columns.

    But until we upgrade, what other choices may we have?

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am sorry, how many records are you returning to the client? Unless you are doing detailed reporting, it's really not best practice to return large record sets to a clinent. What is the client doing with the data?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jan 2003
    Posts
    26
    You are probably right.

    The best with this design would be to return less rows to the client.

    Thanks!

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    stupid question, defrag or rebuild indices lately ?.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •