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:
- 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 @MaxPrijswijziging Float
DECLARE @MinPrijswijziging Float
SET @MaxPrijswijziging = 4.0
SET @MinPrijswijziging = 10.0
SET @plannummer = '20080219'
First, run this:
- 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 > @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)
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
3
