Optimizing query

• LadyG

  Say Hey Kid

  Points: 675

  More actions

  July 20, 2010 at 1:47 am

  #1195326

  Yes, the clustered index on TransactionID and Date.

  I did try Chris' suggestion with the calendar table, it did make a huge difference. Anyway I can optimise it further looking at the execution plan??

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 20, 2010 at 2:24 am

  #1195348

  LadyG (7/20/2010)

  ---

  Yes, the clustered index on TransactionID and Date.

  I did try Chris' suggestion with the calendar table, it did make a huge difference. Anyway I can optimise it further looking at the execution plan??

  Did you try changing your WHERE clause as per this post?

  Did you try taking out the derived table? You need to ensure that removing this doesn't change the row count of the output.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• RichardDouglas

  SSCarpal Tunnel

  Points: 4029

  More actions

  July 20, 2010 at 6:55 am

  #1195475

  The query appears to be doing table scans on the tables TaxRules and Interface_CorrectionMethods. Ideally you would want to create some clustered indexes on these tables, do you have someone you can speak to internally about choosing the best candidates for the index?

  Remember you should keep your clustered indexes as narrow as possible as they will also be included in your nonclustered indexes.

  From looking at the plan once you have created you clustered indexes you may wish to create some nonclustered indexes to cover the fields you require in the query - obviously without knowing all the details about the table such as number of attributes and rows it would be difficult to recommend this from the outset.

  Hope this helps,
  Rich

  [p]
  [/p]