Efficient Code round 2

I have taken the recommendations that I was given with my
first posting of this procdure code. I greatly
appreciate the help. I have written my 2nd attempt and
would like to get feedback on this version for anything
else that can optimize the queries.
Here is the code:
-- Procedure to Update DR Compliance values for all
Servers
-- *** This update handles records with a DRServiceLevel
of None for all RACriticalities
UPDATE AudServerDR
SET DRCompliance = 'NA',
RACompliance = 'NA',
TestCompliance = 'NA',
TotalCompliance = 'NA'
WHERE DRServiceLevel = 'None'
-- *** This update a RACriticality CASE of Critiacal,
Essential or Vital
UPDATE AudServerDR
SET DRCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRSignEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
THEN 'Yes'
ELSE 'No'
END,
RACompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END,
TestCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END,
TotalCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRSignEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
RAEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
AND RAName <> ''
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END
WHERE varid IN (SELECT VARID
FROM AudServerDR
WHERE RACriticality IN
('Essential', 'Critical', 'Vital'))
AND DRServiceLevel <> 'None'
-- *** This update a RACriticality CASE of New Device
UPDATE AudServerDR
SET DRCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRSignEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
THEN 'Yes'
ELSE 'No'
END,
RACompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(dd, 30,
RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END,
TestCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END,
TotalCompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRSignEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(dd, 30,
RAEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
TestEffectDate)
AND DRPlanName <> ''
AND DRSignName <> ''
AND RAName <> ''
AND TestName <> ''
THEN 'Yes'
ELSE 'No'
END
WHERE varid IN (SELECT VARID
FROM AudServerDR
WHERE RACriticality = 'New Device')
AND DRServiceLevel <> 'None'
-- *** This update a RACriticality CASE of Non-Critical
UPDATE audServerDR
SET DRCompliance = 'NA',
RACompliance = CASE
WHEN CURRENT_TIMESTAMP <= DATEADD
(mm, 12, RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END,
TestCompliance = 'NA',
TotalCompliance = CASE
WHEN CURRENT_TIMESTAMP <=
DATEADD(mm, 12, RAEffectDate)
AND RAName <> ''
THEN 'Yes'
ELSE 'No'
END
WHERE VARID IN (SELECT VARID
FROM audServerDR
WHERE RACriticality = 'Non-critical')Much better, but you still missed the point of the CASE Expressions.
Roll all of these UPDATEs into one UPDATE with case expressions that
return ('yes', 'no,' N/A'). If that looks too complex, then at least
consolidate the ones after the mass 'N/A' update, like this:
UPDATE AudServerDR
SET DRCompliance
= CASE
WHEN DRPlanName <> ''
AND DRSignName <> ''
AND (CURRENT_TIMESTAMP <= DATEADD(mm, 12, DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 12,
DRSignEffectDate))
OR (CURRENT_TIMESTAMP <= DATEADD(mm, 6,DRPlanEffectDate)
AND CURRENT_TIMESTAMP <= DATEADD(mm, 6,
DRSignEffectDate))
THEN 'Yes'
ELSE 'No'
END,
.
WHERE var_id
IN (SELECT var_id
FROM AudServerDR
WHERE RACriticality
IN ('Essential', 'Critical', 'Vital'))
AND DRServiceLevel <> 'None' ;
The goal is to make as few passes over the table as possible.|||On Tue, 12 Apr 2005 07:52:13 -0700, Jazz wrote:

>I have taken the recommendations that I was given with my
>first posting of this procdure code. I greatly
>appreciate the help. I have written my 2nd attempt and
>would like to get feedback on this version for anything
>else that can optimize the queries.
(snip)
Hi Jazz,
I had started an attempt to rewrite this to do all updates in one single
pass, but I found that some things are unclear:
(snip)
>WHERE varid IN (SELECT VARID
> FROM AudServerDR
> WHERE RACriticality IN
>('Essential', 'Critical', 'Vital'))
> AND DRServiceLevel <> 'None'
(snip)
If varid the primary key column in AudServerDR? If yes, then this is
unnecessary complex. If no, then I don't understand your queries.
Note - this question could have been avoided if you had posted DDL (that
is: CREATE TABLE statement, including constraints and properties) for
the table.
(snip)
>-- *** This update a RACriticality CASE of Non-Critical
>UPDATE audServerDR
>SET DRCompliance = 'NA',
> RACompliance = CASE
(snip)
>WHERE VARID IN (SELECT VARID
> FROM audServerDR
> WHERE RACriticality = 'Non-critical')
Here, I am missing the "AND DRServiceLevel <> 'None'" part that was
present in the previous subqueries. Does this imply that this UPDATE
will overwrite some of the rows already affected by the very first
update (the one that sets all compliance columns to 'NA' for rows with
service level equal to 'None')? Have you forgotten to include it but
should it be there? Or is there some constraint on your data that makes
the combination of RACriticality 'Non-critical' and ServiceLevel 'None'
impossible?
Answer these questions, and I think I'll be able to cough up an UPDATE
statement that makes all required modifications in one single pass.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)