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
>.
>

Effectively using subqueries

Hi, I have searched through this forum and haven't found the answer to a problem I am having so I figured I will post it.

My SQL statement:

Select * FROM tblData WHERE LastName=
(SELECT tblData.LastName FROM tblData GROUP BY tblData.LastName HAVING (Count(tblData.LastName)>1))"

What I am trying to do: I am trying to make an SQL filter that I can apply to my form in order to only show records with duplicate last names. The sub query returns names that are already in the table. I then compare what is found to be duplicate with the original table in order to just show only the duplicate records. Everything works fine as long as there is only one name thats duplicated.

When there are multiple duplicate names then I run into an error.

When the statement is put into a string and executed in VBA I get this error:

"Run-time error '3021': No current record."

When the statement is put into a query and run against the DB I get this error:

"At most one record can be returned by this subquery"

So yeah, any help would be greatly appreciated. Am I going about this all wrong or am I just forgetting something? Thanks for any help.It is because ur subquery returns more than one value when u have more than one duplicates.This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Check the code below.

Select distinct t.* FROM tblData t
inner join
( SELECT LastName FROM tblData
GROUP BY LastName
HAVING (Count(*)>1)
) as tm
on t.LastName=tm.LastName|||I understand what you did and it works fine when I run it as a query, but when I try and apply it as a filter it doesn't work. It just returns all records.