SQL2005 Aggregate Multiple Update optimazation: How-To?

**ck9663** · Feb 22 '08, 05:49 PM

Originally posted by Rimsky

I have the need to optimize a piece of code. It updates a table, based on a detail table. The detail table (vpmod) contains 3*10^6 records, the base table (volcdet) contains 20 relevant records. Here's my code:

Code:

DECLARE @plannummer VarChar(20)
DECLARE	@MaxPrijswijziging Float
DECLARE	@MinPrijswijziging Float

SET @MaxPrijswijziging = 4.0
SET @MinPrijswijziging = 10.0

SET	@plannummer = '20080219'
UPDATE mcmain.volcdet
	SET vd_qbmaxed = (SELECT count(*) FROM mcmain.vpmod
						WHERE vm_updnmr = @plannummer
						AND	vm_volc = vd_volcnmr
						AND	vm_bprperc > @MaxPrijsWijziging)
	WHERE vd_upnmr = @plannummer
UPDATE mcmain.volcdet
	SET vd_qbmined = (SELECT count(*) FROM mcmain.vpmod
						WHERE vm_updnmr = @plannummer
						AND	vm_volc = vd_volcnmr
						AND	vm_bprperc < @MinPrijsWijziging)
	WHERE vd_upnmr = @plannummer

UPDATE mcmain.volcdet
	SET vd_qnmaxed = (SELECT count(*) FROM mcmain.vpmod
						WHERE vm_updnmr = @plannummer
						AND	vm_volc = vd_volcnmr
						AND	vm_nprperc > @MaxPrijsWijziging)
	WHERE vd_upnmr = @plannummer
UPDATE mcmain.volcdet
	SET vd_qnmined = (SELECT count(*) FROM mcmain.vpmod
						WHERE vm_updnmr = @plannummer
						AND	vm_volc = vd_volcnmr
						AND	vm_nprperc < @MinPrijsWijziging)
	WHERE vd_upnmr = @plannummer

It essentailly tries to count the number of priceraises above and below a border value.
Does anyone have an idea how to decrease the processing time? I would think less code would make it faster. But.... ????

Cees Cappelle

DECLARE @plannummer VarChar(20)
DECLARE @MaxPrijswijzig ing Float
DECLARE @MinPrijswijzig ing Float

SET @MaxPrijswijzig ing = 4.0
SET @MinPrijswijzig ing = 10.0

SET @plannummer = '20080219'

First, run this:

Code:

SELECT 
   vd_qbmaxed = 
         sum(case when vm_bprperc > @MaxPrijsWijziging then 1 else 0 end),
   vd_qbmined  = 
         sum(case when vm_bprperc < @MaxPrijsWijziging then 1 else 0 end),
   vd_qnmaxed   = 
         sum(case when vm_nprperc > @MaxPrijsWijziging) then 1 else 0 end),
   vd_qnmined    = 
         sum(case when vm_nprperc > @MaxPrijsWijziging then 1 else 0 end)
WHERE vd_upnmr = @plannummer
         and vm_updnmr = @plannummer
         AND	vm_volc = vd_volcnmr

And see if the numbers are correct by running each individual subquery from your UPDATE statement and comparing the result.

We're doing this so as you don't overwrite the value of your q?mined and q?maxed while you're doing some testing on your query. Or you can always have a backup.

If you're statisfied with the result, you can now run this:

UPDATE mcmain.volcdet
set
vd_qbmaxed = summary_list.vd _qbmaxed,
vd_qbmined = summary_list.vd _qbmined,
vd_qnmaxed = summary_list.vd _qnmaxed,
vd_qnmined = summary_list.vd _qnmined
from
(
SELECT vd_upnmr,
vd_qbmaxed =
sum(case when vm_bprperc > @MaxPrijsWijzig ing then 1 else 0 end),
vd_qbmined =
sum(case when vm_bprperc < @MaxPrijsWijzig ing then 1 else 0 end),
vd_qnmaxed =
sum(case when vm_nprperc > @MaxPrijsWijzig ing) then 1 else 0 end),
vd_qnmined =
sum(case when vm_nprperc > @MaxPrijsWijzig ing then 1 else 0 end)
from mcmain.vpmod
WHERE vm_updnmr = @plannummer
AND vm_volc = vd_volcnmr
group by vm_updnmr
) summary_list
where vd_upnmr = vm_updnmr

This query will update your vd_q?maxed and vd_q?mined whether there's a valid condition (<, >) or not. The subquery (summary_list) will always return a row for < and >, not for =. If the condition is false, it will 0 if true, it will be the count. If you don't want your vd_q?maxed and vd_q?mined overwritten add a CASE..WHEN function to check if the value you're using to update is zero, otherwise retain the value of the column.

Good luck.

-- CK

SQL2005 Aggregate Multiple Update optimazation: How-To?

SQL2005 Aggregate Multiple Update optimazation: How-To?

Comment