Friday, February 17, 2012

Efficient Coding

I have a quite lengthy Update query that is working in
its current condition BUT I don't feel that I have coded
this query as effectively as I should to minimize the
transactions because of the many update statements that
I'm doing. The Updates all occur within a single table
but I don't see how I can combine the various WHERE
clauses in 1 of fewer UPDATES
Any help or suggestions would be appreciated
The statement follows:
-- Procedure to Update DR Critical, Essential and Vital
Servers
DECLARE @.today DATETIME
SET @.today = GETDATE()
DECLARE @.criticalrows INT
SET @.criticalrows = (SELECT COUNT(*)
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(DRServiceLevel = 'None'))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET DRCompliance = 'NA', RACompliance = 'NA',
TestCompliance = 'NA', TotalCompliance = 'NA'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(DRServiceLevel = 'None')))
END
ELSE
BEGIN
-- Check DR Compliance
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
DRPlanEffectDate)) AND
(@.today <= DATEADD(mm, 12,
DRSignEffectDate)) AND
(DRPlanName <> '') AND
(DRSignName <> ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET DRCompliance = 'Yes'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
DRPlanEffectDate)) AND
(@.today <= DATEADD(mm, 12,
DRSignEffectDate)) AND
(DRPlanName <> '') AND
(DRSignName <> '')))
END
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
DRPlanEffectDate)) OR
(@.today > DATEADD(mm, 12,
DRSignEffectDate)) OR
(DRPlanName = '') OR
(DRSignName = ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET DRCompliance = 'No'
WHERE (VARID IN (SELECT VARID AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
DRPlanEffectDate)) OR
(@.today > DATEADD(mm, 12,
DRSignEffectDate)) OR
(DRPlanName = '') OR
(DRSignName = '')))
-- End of DR Compliance
END
-- Check RA Compliance
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
RAEffectDate)) AND
(RAName <> ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET RACompliance = 'Yes'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
RAEffectDate)) AND
(RAName <> '')))
END
SET @.criticalrows = (SELECT COUNT (*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
RAEffectDate)) OR
(RAName = ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET RACompliance = 'No'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
RAEffectDate)) OR
(RAName = '')))
-- End of RA Compliance
END
-- Check Test Compliance
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
TestEffectDate)) AND
(TestName <> ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET TestCompliance = 'Yes'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
TestEffectDate)) AND
(TestName <> '')))
END
SET @.criticalrows = (SELECT COUNT (*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
TestEffectDate)) OR
(TestName = ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET TestCompliance = 'No'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
TestEffectDate)) OR
(TestName = '')))
-- End of Test Compliance
END
-- Check Total Compliance
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
DRPlanEffectDate)) AND
(@.today <= DATEADD(mm, 12,
DRSignEffectDate)) AND
(@.today <= DATEADD(mm, 12,
RAEffectDate)) AND
(@.today <= DATEADD(mm, 12,
TestEffectDate)) AND
(DRPlanName <> '') AND
(DRSignName <> '') AND
(RAName <> '') AND
(TestName <> ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET DRCompliance = 'Yes', RACompliance = 'Yes',
TestCompliance = 'Yes', TotalCompliance = 'Yes'
WHERE (VARID IN (SELECT VARID
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today <= DATEADD(mm, 12,
DRPlanEffectDate)) AND
(@.today <= DATEADD(mm, 12,
DRSignEffectDate)) AND
(@.today <= DATEADD(mm, 12,
RAEffectDate)) AND
(@.today <= DATEADD(mm, 12,
TestEffectDate)) AND
(DRPlanName <> '') AND
(DRSignName <> '') AND
(RAName <> '') AND
(TestName <> '')))
END
SET @.criticalrows = (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
DRPlanEffectDate)) OR
(@.today > DATEADD(mm, 12,
DRSignEffectDate)) OR
(@.today > DATEADD(mm, 12,
RAEffectDate)) OR
(@.today > DATEADD(mm, 12,
TestEffectDate)) OR
(DRPlanName = '') OR
(DRSignName = '') OR
(RAName = '') OR
(TestName = ''))
IF @.criticalrows > 0
BEGIN
UPDATE audServerDR
SET TotalCompliance = 'No'
WHERE (VARID IN (SELECT COUNT(*) AS x
FROM audServerDR d
WHERE (RACriticality IN
('Essential', 'Critical', 'Vital')) AND
(@.today > DATEADD(mm, 12,
DRPlanEffectDate)) OR
(@.today > DATEADD(mm, 12,
DRSignEffectDate)) OR
(@.today > DATEADD(mm, 12,
RAEffectDate)) OR
(@.today > DATEADD(mm, 12,
TestEffectDate)) OR
(DRPlanName = '') OR
(DRSignName = '') OR
(RAName = '') OR
(TestName = '')))
-- End of Total Compliance
END
END--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can get rid of the @.criticalRows check & run the UPDATEs as you have
them. If there are rows that satisfy the criteria the updates will
occur. Checking before-hand is redundant & inefficient. Also, the
procedure will not be compiled w/ an execution plan 'cuz of the IFs;
this slows down execution when the procedure runs.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQlWsH4echKqOuFEgEQL2agCggOJoyFX0Ct9c
LnY/5Vb50aH4wZUAoOWR
gaksMFOohIaLxP5oT1AjNi0S
=tApC
--END PGP SIGNATURE--
Jazz wrote:
> I have a quite lengthy Update query that is working in
> its current condition BUT I don't feel that I have coded
> this query as effectively as I should to minimize the
> transactions because of the many update statements that
> I'm doing. The Updates all occur within a single table
> but I don't see how I can combine the various WHERE
> clauses in 1 of fewer UPDATES
> Any help or suggestions would be appreciated
> The statement follows:
> -- Procedure to Update DR Critical, Essential and Vital
> Servers
> DECLARE @.today DATETIME
> SET @.today = GETDATE()
> DECLARE @.criticalrows INT
> SET @.criticalrows = (SELECT COUNT(*)
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (DRServiceLevel = 'None'))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET DRCompliance = 'NA', RACompliance = 'NA',
> TestCompliance = 'NA', TotalCompliance = 'NA'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (DRServiceLevel = 'None')))
> END
> ELSE
> BEGIN
> -- Check DR Compliance
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> DRPlanEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> DRSignEffectDate)) AND
> (DRPlanName <> '') AND
> (DRSignName <> ''))
>
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET DRCompliance = 'Yes'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> DRPlanEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> DRSignEffectDate)) AND
> (DRPlanName <> '') AND
> (DRSignName <> '')))
> END
>
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> DRPlanEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> DRSignEffectDate)) OR
> (DRPlanName = '') OR
> (DRSignName = ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET DRCompliance = 'No'
> WHERE (VARID IN (SELECT VARID AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> DRPlanEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> DRSignEffectDate)) OR
> (DRPlanName = '') OR
> (DRSignName = '')))
> -- End of DR Compliance
> END
>
> -- Check RA Compliance
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> RAEffectDate)) AND
> (RAName <> ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET RACompliance = 'Yes'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> RAEffectDate)) AND
> (RAName <> '')))
> END
> SET @.criticalrows = (SELECT COUNT (*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> RAEffectDate)) OR
> (RAName = ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET RACompliance = 'No'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> RAEffectDate)) OR
> (RAName = '')))
> -- End of RA Compliance
> END
> -- Check Test Compliance
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> TestEffectDate)) AND
> (TestName <> ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET TestCompliance = 'Yes'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> TestEffectDate)) AND
> (TestName <> '')))
> END
> SET @.criticalrows = (SELECT COUNT (*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> TestEffectDate)) OR
> (TestName = ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET TestCompliance = 'No'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> TestEffectDate)) OR
> (TestName = '')))
> -- End of Test Compliance
> END
> -- Check Total Compliance
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> DRPlanEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> DRSignEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> RAEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> TestEffectDate)) AND
> (DRPlanName <> '') AND
> (DRSignName <> '') AND
> (RAName <> '') AND
> (TestName <> ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET DRCompliance = 'Yes', RACompliance = 'Yes',
> TestCompliance = 'Yes', TotalCompliance = 'Yes'
> WHERE (VARID IN (SELECT VARID
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today <= DATEADD(mm, 12,
> DRPlanEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> DRSignEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> RAEffectDate)) AND
> (@.today <= DATEADD(mm, 12,
> TestEffectDate)) AND
> (DRPlanName <> '') AND
> (DRSignName <> '') AND
> (RAName <> '') AND
> (TestName <> '')))
> END
> SET @.criticalrows = (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> DRPlanEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> DRSignEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> RAEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> TestEffectDate)) OR
> (DRPlanName = '') OR
> (DRSignName = '') OR
> (RAName = '') OR
> (TestName = ''))
> IF @.criticalrows > 0
> BEGIN
> UPDATE audServerDR
> SET TotalCompliance = 'No'
> WHERE (VARID IN (SELECT COUNT(*) AS x
> FROM audServerDR d
> WHERE (RACriticality IN
> ('Essential', 'Critical', 'Vital')) AND
> (@.today > DATEADD(mm, 12,
> DRPlanEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> DRSignEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> RAEffectDate)) OR
> (@.today > DATEADD(mm, 12,
> TestEffectDate)) OR
> (DRPlanName = '') OR
> (DRSignName = '') OR
> (RAName = '') OR
> (TestName = '')))
> -- End of Total Compliance
> END
> END
>|||What you want to do is put all of this procedural logic into CASE
expressions and do the whole thign in *one* UPDATE statement. You do
not need the the @.today variable and the correct Standard syntax is
CURRENT TIMESTAMP. Stop adding aliases that are never references; the
guy maintaining the code will have to take time to look for them.
UPDATE AudServerDR
SET DRCompliance
= CASE WHEN <pred_1>
THEN 'Yes'
ELSE 'No' END,
RACompliance
= CASE WHEN <pred_2>
THEN 'Yes'
ELSE 'No' END,
TestCompliance
= CASE WHEN <pred_3>
THEN 'Yes'
ELSE 'No' END,
TotalCompliance
= CASE WHEN <pred_4>
THEN 'Yes'
ELSE 'No' END,
WHERE varid
IN (SELECT varid
FROM AudServerDR
WHERE RACriticality IN ('Essential', 'Critical', 'Vital')
AND DRServiceLevel = 'None');
END;
Since I do not have specs or know your application, I am guessing that
these flags are set to either 'Yes' or 'No', but if you have a 'N/A'
value, just add another WHEN..THEN to each assignment expression.
Something like this:
TotalCompliance
= WHEN RACriticality IN ('Essential', 'Critical', 'Vital')
AND CURRENT_TIMESTAMP > DATEADD(mm, 12,
DRPlanEffectDate)
OR CURRENT_TIMESTAMP > DATEADD(mm, 12,
DRSignEffectDate)
OR CURRENT_TIMESTAMP > DATEADD(mm, 12, RAEffectDate)
OR CURRENT_TIMESTAMP > DATEADD(mm, 12, TestEffectDate)
OR '' IN (DRPlanName, DRSignName, RAName, TestName =
'')
THEN 'No' ELSE 'Yes' END|||Thank you very much. This makes a lot of sense and
achieves what I though should be done didn't see haw to
do. I appreciate your help it's really helping me to
learn how to write better queries.
>--Original Message--
>What you want to do is put all of this procedural logic
into CASE
>expressions and do the whole thign in *one* UPDATE
statement. You do
>not need the the @.today variable and the correct
Standard syntax is
>CURRENT TIMESTAMP. Stop adding aliases that are never
references; the
>guy maintaining the code will have to take time to look
for them.
>UPDATE AudServerDR
>SET DRCompliance
> = CASE WHEN <pred_1>
> THEN 'Yes'
> ELSE 'No' END,
> RACompliance
> = CASE WHEN <pred_2>
> THEN 'Yes'
> ELSE 'No' END,
> TestCompliance
> = CASE WHEN <pred_3>
> THEN 'Yes'
> ELSE 'No' END,
> TotalCompliance
> = CASE WHEN <pred_4>
> THEN 'Yes'
> ELSE 'No' END,
>WHERE varid
> IN (SELECT varid
> FROM AudServerDR
> WHERE RACriticality IN
('Essential', 'Critical', 'Vital')
> AND DRServiceLevel = 'None');
>END;
>Since I do not have specs or know your application, I am
guessing that
>these flags are set to either 'Yes' or 'No', but if you
have a 'N/A'
>value, just add another WHEN..THEN to each assignment
expression.
>Something like this:
>TotalCompliance
> = WHEN RACriticality IN
('Essential', 'Critical', 'Vital')
> AND CURRENT_TIMESTAMP > DATEADD(mm, 12,
>DRPlanEffectDate)
> OR CURRENT_TIMESTAMP > DATEADD(mm, 12,
>DRSignEffectDate)
> OR CURRENT_TIMESTAMP > DATEADD(mm, 12,
RAEffectDate)
> OR CURRENT_TIMESTAMP > DATEADD(mm, 12,
TestEffectDate)
> OR '' IN (DRPlanName, DRSignName,
RAName, TestName =
>'')
> THEN 'No' ELSE 'Yes' END
>.
>

No comments:

Post a Comment