April 3, 2007 at 2:33 pm
Hi,
I have the following update in SQL 2000. This used to take 50 minutes to an hour to tun.Now for some reason it won't complete even overnight!
UPDATE
Services
SET Services.LagCategory = FinancialCategories.LagCategory
FROM FinancialCategories INNER JOIN Services ON FinancialCategories.ClaimType = Services.ClaimType
The FinancialCategories table has only 14 rows in it. The Services table has about 6.5 million rows in it. The CLAIMTYPE field in Services has a non-clustered index on it. The execution plan shows that it is using an Index Seek on the CLAIMTYPE column, and that the HASH JOIN is accounting for about 79% of the cost. Can anybody help with this issue or offer some advice? Any more info needed just ask. Thanks!
Pat
April 3, 2007 at 4:11 pm
Remove column LagCategory from Services and retrieve it via INNER JOIN to FinancialCategories every time you need it.
You may create a view with this join inside and replace all references to Services table to Services view.
You'll probably be surprised but your queries will perform better. Less physical reads.
_____________
Code for TallyGenerator
April 4, 2007 at 6:07 am
The last post is right - you should really normalize your database a bit more, but we all know that this is not always possible.
Since the update used to only take 50 minutes, it is samewhat safe to say that something has changed. Start with indexes - since you know the indexes for the join are there, look at indexes with LagCategory on your services table. Since you are updating all of these records, the indexes all have to be reordered. Even if you have not recently added indexes, you may need to reindex and use the fill factor to open up some space. Remember, making a change to a field in a clustered index can reorder your entire table and sorting 6.5 million rows and re-writing them to disk can be a big task.
I would also recommend you add a where clause:
Update S SET S.LagCategory = F.LagCategory FROM Services S INNER JOIN FinancialCategories F ON S.ClaimType = F.ClaimType WHERE S.LagCategory <> F.LagCategory
Why update it to the same value if it is not different? You can probably reduce the number of updates in your transaction this way. You will have to performance tune this a bit and may need to index the LagCategory fields.
April 4, 2007 at 7:05 am
Michael has good ideas. Be sure your statistics are up to date as well.
April 4, 2007 at 3:07 pm
Is there something else happening on the database at the same time that might be blocking the update?
-SQLBill
April 9, 2007 at 3:03 pm
Thanks guys. the indexes are dropped and recreated each load. the lagcategory field is always blank.
i don't know what happened that changed the update time so drastically. it's driving me nuts. this past weekend i rebooted the server and there were no connections to it and I ran the update. This was the only thing running, and it still took 11 hours!!! Is there a way to batch this update into smaller chunks? maybe that would help, although still doesn't explain what happened. help!
April 9, 2007 at 3:23 pm
You say 'each load'. Are you loading your Services table each day? Why don't you look at getting the LagCategory populated during the load?
April 9, 2007 at 3:25 pm
it's loaded each week, and I have no control over the data source it comes from (SAS in this case)...i wish...
April 9, 2007 at 3:26 pm
Are there any triggers on the table you are updating?
April 9, 2007 at 3:27 pm
no, i actually recreated the table to be sure it was blank and same result...
April 9, 2007 at 3:32 pm
Is LagCategory in any of your indexes? How long does it take to load the data?
April 9, 2007 at 4:11 pm
no, lagcategory isn't in any of the indexes. takes aboout 1/2 hour to load the data.
April 10, 2007 at 6:49 am
What happens if you do an update without the join?
UPDATE Services
SET Services.LagCategory = ???
WHERE Services.ClaimType = ???
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply