Post reply

Addition of Clustered Index causing slower query times.

  • April 15, 2009 at 5:29 am

    #203018

    Hi,

    I would attempt to post the full DDL statements for the problem, but there are too many tables involved, and I am hoping someone can help just by seeing the specified part on an execution plan.

    I have a Vendor supplied database application that runs a query whenever a user is on their "HOME" page. Today I noticed that these querys were taking over 3 seconds to run per query per user, which seems ok, but they return only one record in most cases. So I ran the query and reviewed the execution plans.

    Overall cost was about 8.5. I added a few indexes where I noticed them missing.. and total cost started dropping down to around 6.

    Continuing working I notice that most of the cost is taken up by about 10 Table scans , always between the same 2 tables.

    The 1st table - RequestType is a heap, i.e. no Clustered Index Primary Key

    The other table Request has a field RequestTypeID that is indexed.

    The 1stPic.png shows the table scan.

    So it seems to me there are missing indexes on Request or RequestType

    So I make ObjectID on RequestType the Primary Key. The data is unique and numbered 1 , 2, 3, etc..

    The table scan remains, and my cost increases to over 9..

    It really looks to me like there is a requirement for both RequestType.ObjectID to be indexed and Request.RequestTypeID which they now both are, but they are still involved in a table scan. The data is small yes, but I don't like the length of the query for 1 record.

    Attached are 2 screenshots as well of the select's cost , one before adding the Clustered index on RequestType.

    This makes no sense at all to me.

    Then again, neither does the "SELECT * FROM (SELECT TOP 1000 * FROM (SELECT TOP 1000 * "

    (If you don't know what I mean, check the query in the 2nd 2 pictures.. I have no idea why it's like that.

    Does anyone have any idea's ?

    Thanks,

  • April 15, 2009 at 6:11 am

    #977998

    Can you at least post the DDL for Request and Request type, including indexes, and the query in question?

    By the looks of the screen shots this is a SQL 2000 server. Is that right? I ask, because you've posted in the 2005 forums and there are very different ways to get the exec plan for the two. A screen shot of just the last two operators is near-useless. It's the rest of the plan that's interesting.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 15, 2009 at 7:30 am

    #978057

    Two things.

    First, and most important, the costs displayed in execution plans are estimates. They are not accurate measures of anything. While you may or may not see a cost go down, it just doesn't mean much. Percentage of cost is a useful measure although even that is subject to error (see this post[/url]). They can be useful while tuning, but are not even remotely enough information for anyone to say, yes, you're on the right track or not.

    Second, if you're getting a table scan on the table you displayed, but that column has an index, it's possible that something in the query is preventing the index from being used. It could be a function on the column, an implicit conversion, a number of different things that prevent the index from being used. It's also possible that, as you say, the table is very small. It may just be easier for SQL Server to scan it.

    Without the query and a full execution plan (and more as Gail outlined) that's about all I can say at the moment.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 15, 2009 at 9:17 am

    #978163

    Hi Gail and Grant,

    Thanks for the replies..

    I started trying to create DDL statements for all the tables required, I was on 25 tables just now, as they query is from a view, which is from a set of views and so on. Painful, and slow.

    I actually didn't even think about the fact that is was SQL 2000 until Gail mentioned that I had posted in the wrong forum

    So I fired up Management studio 2008 and looked at the execution plan in , and whilst it listed the same table scan, it listed the outputs, and I could see the fields required for the index. The query is now down to 150ms instead of around 3 seconds, so its a lot quicker.

    I will take your advice and not take the Costs in the Execution plans too literally.

    Incidentally the execution plan still says "i.e. estimates" the cost higher when that one table has clustered indexes and primary keys, although execution times and reads are about the same.

    I am going to leave the Primary Key and Clustered Index on the table, as I hate to sea heaps in databases.

    I think I panicked when I saw the words "Table Scan" , as I have not seen one in a while.

    I now know why it was doing it , and it's resolved for now to my satisfaction.

    Thanks for the pointers and comments,

Viewing 4 posts - 1 through 3 (of 3 total)

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