June 1, 2018 at 12:25 pm
Is there any kind of general rule or guidance on which kind of update is faster? I'm trying to figure out which is the better way to go in a scenario where the table in question has a primary key that is not the clustering key, nor is it included in same. I have to join this table to other tables to first determine the rows eligible to generate general ledger entries, and then keep track of the aggregation levels, and finally insert rows into the general ledger entry table based on two different levels of aggregation, and then go back and update that source table to set a bit flag as well as update a value that then points back to a secondary GL table that tracks the source of the GL entries. When I add an additional index on the source table, that index gets chosen for the update. When I index hint to force the clustered index, I got a 23 second execution duration instead of 31 seconds. Since then I've been experimenting with dropping out that other index, and in the process, have found myself unable to get back to where I was with the 23 second duration, and am stuck above 30 seconds. Any general ideas? I can't actually post the plan or the table structure, but am looking for ideas on the best way do this with the minimum necessary index hints.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 1, 2018 at 2:02 pm
Well, it figures. Just when I think I've got it nailed down and just needed a quick clue, I figure it out and dropped the unnecessary index and I'm back to just 24 seconds. Geez Louise...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply