April 16, 2007 at 8:59 am
Hi all,
Whats is the fastest or most efficient way to update 15 million records from a table of 50 millions records (tablename: POS_FACT). Should i rebuild the indexes before i make an update and after i make an update. or should i drop the indexes on the POS_FACT table and then update and then build the index, which would be faster.
The query uses:
(1) Filters 50 million to 45million using where clause
(2) Then looks up a particular column value (timestamp and some other values) with another table (look up table which has a million records) .... it looks up based on timestamp and few conditions
(3) If for a particular timestamp match in the look up, i try to see the price if the prices in both the tables dont match up i would like to update the POS_FACT table.
-- update price of the POS_FACT table if it dosent match up with the inner query output
UPDATE
pf SET pf.SRP_FACT = ISNULL(mp.RATE,0)
FROM dbo.SRP_POS_FACT_ERROR pf
--SELECT pf.SRP_FACT,isnull(mp.RATE,0) FROM dbo.SRP_POS_FACT_ERROR pf
LEFT OUTER JOIN USLPDSQLPRD01.LPD_MASTER.dbo.MATERIAL_PRICING mp
ON mp.MATERIAL_NUMBER = pf.MATERIAL_NUMBER AND
(pf.RETAIL_WEEKENDING_DATE BETWEEN mp.VALID_FROM_DT AND mp.VALID_TO_DT) AND
mp.SALES_ORGANIZATION = pf.SALES_ORGANIZATION AND
mp.DIVISION = pf.DIVISION AND
mp.DISTRIBUTION_CHANNEL = pf.DISTRIBUTION_CHANNEL and
mp.CONDITION_TYPE = 'ZSRP'
--- subquery returns price for a particular record being paseed into it, if it dosent match update the price
WHERE pf.SRP_FACT <> ISNULL(
(SELECT mp.RATE
FROM USLPDSQLPRD01.LPD_MASTER.dbo.MATERIAL_PRICING mp
WHERE mp.CONDITION_TYPE = 'ZSRP' AND
mp.MATERIAL_NUMBER = pf.MATERIAL_NUMBER AND
(pf.RETAIL_WEEKENDING_DATE
BETWEEN mp.VALID_FROM_DT AND mp.VALID_TO_DT) AND
mp.SALES_ORGANIZATION = pf.SALES_ORGANIZATION AND
mp.DIVISION = pf.DIVISION AND
mp.DISTRIBUTION_CHANNEL = pf.DISTRIBUTION_CHANNEL
)
,0)
- Reddy
April 16, 2007 at 10:33 am
Hello Dinesh,
I think the best way to proceed with your task is to
1. Remove the existing indexes
2. Update your 15 million records
3. Create the indexes
Hope this helps.
Thanks
Lucky
April 16, 2007 at 11:06 am
Are the indexes in reference used in the update queries? If so, you need those indexes. You may or may not want to rebuild them before and after (and if you do, use one of those selective index rebuild scripts that are all over the place), but if the indexes are used in all those joins & queries, dropping them will kill your performance. On the other hand, if you don't need those indexes during this process, and you're not worried about any other queries coming in during this process, then dropping those unneeded indexes until the completion of this batch update will probably help.
In short, it depends.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply