December 22, 2009 at 9:32 am
Have you tried creating the index as Gail mentioned? And after you do, can you post the new execution plan? It is far more helpful to see the plan then the actual query, which includes it, anyway. We can quickly see where you are missing an index, or where the index is not used for a particular table.
Cheers,
J-F
December 22, 2009 at 9:41 am
In that case I'll amend my index advice
CREATE NONCLUSTERED INDEX idx_TRNDTLSPR09_WKENDDT
ON [TRN].[TRNDTL_SPR09] ([WK_END_DT],[TRANS_ID],[SKU_NUM])
INCLUDE ([SLS_UNITS])
and drop the index that's just on WK_END_DT
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
December 22, 2009 at 9:56 am
I will do as you both suggest and then run the query with the execution plan.
Big table so it may take a little while to create the index.
I'll report back later.
Peter
December 22, 2009 at 1:56 pm
OK..I'm back.
I'm not going to include any execution plans because i think your work is done and done quite well. My 4 1/2 minutes query is now 2 minutes plus a little and my 2 and change is 1 and change. These queries btw power a front end tool i built so quickness is critical. Essentially i'm searching through 500 mil rows and giving them their answer.
I created an index on SKU/WK/TRANSID and included unit sales.
So here is my interpretation of the included column in a non-clustered index-
The included columns data of index exists at the leaf level. That column is not part of the index. - Yes?
And, if yes, or almost, does this mean i could get close to another clustered index (not in name) by including the columns that contain the data that i use for calculations as part of my index?
Again - Thanks so much for your help.
December 22, 2009 at 2:11 pm
PeterG-377490 (12/22/2009)
I'm not going to include any execution plans because i think your work is done and done quite well. My 4 1/2 minutes query is now 2 minutes plus a little and my 2 and change is 1 and change.
2 min is still a long time for a query. There were two table scans in your query. The index I recommended should have removed one. Are you going to do the analysis to get an index to remove the second?
So here is my interpretation of the included column in a non-clustered index-
The included columns data of index exists at the leaf level. That column is not part of the index. - Yes?
Yes. I referred you earlier to a series on indexes. It may be worth your time to read through all three.
And, if yes, or almost, does this mean i could get close to another clustered index (not in name) by including the columns that contain the data that i use for calculations as part of my index?
Yes, but....
The more columns in the include, the larger the index is. That affects DB size, backup size and time, index rebuild time, etc. Also, the columns in the index have to be updated when the table is updated. Lots of wide indexes = slow insert/update/deletes.
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
December 22, 2009 at 2:48 pm
2 execution plans attached
WithCTE
NoCTE
Both produce the same results.
WithCTE - 2 mins 46 seconds
NoCTE - 42 seconds
the index .....SWT is the SKU/WK/TRANSID index with untis as an uncluded column.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply