| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2007-07-25 : 12:17:47
|
| Hi, all,I have this query that I want to improve its performance.Currently, it took 50 min to update 110,000 records.The Exec Plan shows a 99% cost on Bookmark Lookup on tblFSales.One tip I found on dealing with Bookmark Lookups is changing the current clustered index, or consider using a covering index.The table has many non-clustered indexes but no clustered one. My questions are:1) All the columns involved has index. Why the plan did not use covering index? How to make it use?2) How to use a clustered index in this case? Create one on the fly then change it back? and,3) any other suggestions.Thanks!UPDATE F SET Sales = X.intSales FROM rptFcstDetails F LEFT JOIN ( SELECT FS.lngID, FS.lngLocationID, SUM( FS.intSales ) AS intSales FROM tblFPeriods FP INNER JOIN tblFSales FS ON FS.lngFPeriodID = FP.lngFPeriodID WHERE FP.lngFcstID = @FcstID AND FP.intMonth = @intMonth GROUP BY FS.lngID, FS.lngLocationID ) X ON X.lngID = F.lngID AND X.lngLocationID = F.lngLocationID |
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-25 : 12:32:14
|
| Covering indexes only help for selects. Indexes (typically) slow down updates. Having an index on every column that is updated by your query is slowing things down not speeding things up.Remember - if you change a column value and the column is indexed the index needs to be updated too. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-25 : 12:32:53
|
| Split up the query using temp tables then you will find it easier to optimise and spot what is causing the problem.After that you can consider combining to a single query again.SELECT FS.lngID, FS.lngLocationID, SUM( FS.intSales ) AS intSales FROM tblFPeriods FP INNER JOIN tblFSales FS ON FS.lngFPeriodID = FP.lngFPeriodID WHERE FP.lngFcstID = @FcstID AND FP.intMonth = @intMonth GROUP BY FS.lngID, FS.lngLocationID ) Xis an obvious candidate for a temp table - and then ut an index on lngID, lngLocationID if needed.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-25 : 12:33:55
|
| What are all the indexes you have on tblFSales?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-25 : 12:34:55
|
quote:
Originally posted by Hommer
2) How to use a clustered index in this case? Create one on the fly then change it back?
No - that will make it worse too.It would be a good idea to stop and read a few articles or even inside sql server by kalen delaney to get an understanding of indexes and how they work. The idea of an index is for the advantage (accessing rows quicker) to outway the costs (maintaining the index). Creating and removing an index for one query means the cost outweighs the benefits by quite some margin. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-25 : 12:39:36
|
| A covering index ontblFSales (lngFPeriodID, lngFcstID, intMonth)might help. Make sure the column you put first in the index is the most selective.I think you can omit any of those columns if they are part of the primary key, and the PK is CLUSTERED (because they will be represented in any secondary index already). Or maybe SQL Server is smart enough to realise that when you Create the Index and doesn't duplicate them ... [Would be nice to know the answer to that please, all you Guru-Lurkers!!]Kristen |
|
|
|
mattyblah
Starting Member
49 Posts |
Posted - 2007-07-25 : 12:56:24
|
| Maybe an indexed view if you're running the enterprise edition? Never used them but from what I've read the joined derived table appears to be a good candidate? Maybe someone with experience creating them would have some insight... |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-07-25 : 14:02:01
|
| Thank you again for all your replies.I will try to use a temp table, and/or drop the index on the field I want to update.Well, I am just maintaining somedoy else's desgin on the tables. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-25 : 15:34:32
|
| I'll 'fess up - I read the post and skimmed the SQL. An index covering columns that feed the update will be fine. I misread and thought you were talking about indexes on the columns to be indexed. The bit about the clustered index still stands. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-25 : 15:37:49
|
quote:
Originally posted by Kristen
I think you can omit any of those columns if they are part of the primary key, and the PK is CLUSTERED (because they will be represented in any secondary index already). Or maybe SQL Server is smart enough to realise that when you Create the Index and doesn't duplicate them ... [Would be nice to know the answer to that please, all you Guru-Lurkers!!]
Heh heh - not a guru lurker but if the nonclustered index is unique then yes - you don't need to include any clustered keys. I think (and am going on memory) that the clustered keys will be at the end of the index. If it is not unique I again think that they are inluded in the leaf level of the index (like if they were in the INCLUDES clause of an index) so cannot be part of a seek.I guess I need to swot up  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-26 : 01:40:56
|
Well, so long as you can tell me that SQL Server silents "drops" any columns in the CREATE INDEX that are redundant [i.e. repeated in the Primary Key Stuff] I don't care  It would need to be smart enough to put them back if I changed the PK of course Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-26 : 12:08:55
|
quote:
Originally posted by Kristen
Well, so long as you can tell me that SQL Server silents "drops" any columns in the CREATE INDEX that are redundant [i.e. repeated in the Primary Key Stuff] I don't care
I would be surprised. Dunno though. What if you include them out of order? Would the engine care? ....do I?  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-27 : 01:54:35
|
| "Would the engine care?"If one of the PK fields was created as the FIRST field in the Index then that would be used to generate the "selectivity" of the Statistics hint, so that could make a difference I suppose.Just a warning that I was potentially being a plonker would help!Kristen |
|
|
|
|
clustered index/covering index