Post reply

Whats Wrong

  • May 18, 2006 at 4:22 am

    #112252

    AS part of sql performance improvement i have to add clustered index on few table. after that i have noticed that overall select cost has gone up.

    Before

    ============================

    Table 'F_INVOICE'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

    Table 'F_PURCHASEORDER'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

    Table 'F_PURCHASEITEM'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

    Table 'F_ORDERITEM_TO_INVOICE'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0.

    Over All Cost:0.113

    After

    ============================

    Table 'F_INVOICE'. Scan count 1, logical reads 556, physical reads 0, read-ahead reads 0.

    Table 'F_ORDERITEM_TO_INVOICE'. Scan count 1, logical reads 134, physical reads 0, read-ahead reads 0.

    Table 'F_PURCHASEITEM'. Scan count 1, logical reads 3006, physical reads 0, read-ahead reads 0.

    Table 'F_PURCHASEORDER'. Scan count 1, logical reads 1645, physical reads 0, read-ahead reads 0.

    Over all cost:7.05

    Most of the cost is assocated with Hash Match/Inner join. How can i reduce this cost ??

  • May 18, 2006 at 7:51 am

    #638715

    Vinu,

    Could you post some code as well please?

    I'm guessing here, but I suspect that it may be to do with statistics (the values that let the optimizer decide how many rows in a table are going to match the WHERE clause in a query).

    Because your PurchaseOrder table used to be a heap, I suspect that any statistics on it were out of date. By building a clustered index on that table, you will have updated the statistics at the same time (this happens automatically). It could very well be that the difference in the SHOWPLAN output is simply because the optimizer is now getting a better (and more accurate) estimate of how many records are going to match the query.

    You can see that in the second display, the arrow leaving the PurchaseOrder table is much fatter, so the optimizer now thinks that more rows are going to match the query than it did when you produced the first showplan. My guess is that the first showplan was actually wrong, and was way too optimistic.

    If you drop the clustered index, does the showplan go back to the original picture, or does it still show a big fat arrow leaving the PurchaseOrder table? If it still shows a fat arrow, but now with a tablescan, then that is evidence that the statistics were wrong initially.

    The fact that you are seeing lots of Hash Match joins shows that there is a potentially useful index that is missing (the optimizer is creating this index dynamically as the query executes). This is why it would be helpful if you could post the code itself, together with the existing index definitions.

    Phil

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

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