Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query help

Author

Topic

chih
Posting Yak Master

154 Posts

Posted - 2012-02-27 : 19:35:19

Can anyone please help in this query?

This query is for reporting purpose.
In the query, there is a common SELECT statement
"SELECT COUNT(DISTINCT (SampleID))
FROM dbo.ReportVRFlow
WHERE ProjectSettingID = @PsID "

Is there any way to remove/optimize it?
I have rewritten it in CTE. But it seems the same in Execution plan.

Thanks

-------------------------------------------------------
SELECT COUNT(vrf.ID) AS TotRequest,
COUNT(DISTINCT (vrf.SampleID)) AS TotUniqueSample,
COUNT(DISTINCT (vrf.MachineGuid)) AS TotUniqueMachine,
(SELECT COUNT(DISTINCT (SampleID))
FROM dbo.ReportVRFlow
WHERE ProjectSettingID = @PsID AND IsPass = 1) AS TotPass,
(SELECT COUNT(DISTINCT (SampleID))
FROM dbo.ReportVRFlow
WHERE ProjectSettingID = @PsID AND FCCheck = 3) AS TotDuplicate,
(SELECT COUNT(DISTINCT (SampleID))
FROM dbo.ReportVRFlow
WHERE ProjectSettingID = @PsID AND IsProjectExpired = 1) AS TotProjectExpire,
(SELECT COUNT(DISTINCT (SampleID))
FROM dbo.ReportVRFlow
WHERE ProjectSettingID = @PsID AND IsMachineExpired = 1) AS TotMachineExpire,
(SELECT COUNT(DISTINCT (SampleID)) FROM dbo.ReportVRFlow WHERE ProjectSettingID = @PsID AND CountryCodeCheck IN (2, 4)) AS TotCCodeFail
FROM dbo.ReportVRFlow AS vrf
WHERE vrf.ProjectSettingID = @PsID

--------------------------------------
CTE
--------------------------------------
WITH CTE (ID, SampleID, MachineGuid, IsPass, FCCheck, IsProjectExpired,IsMachineExpired,CountryCodeCheck)
AS
(
SELECT ID, SampleID, MachineGuid, IsPass, FCCheck, IsProjectExpired,IsMachineExpired,CountryCodeCheck
FROM dbo.ReportVRFlow
WHERE ProjectSettingID= @PsID
),
CTE2 AS (
SELECT COUNT(ID) AS TotRequest,
COUNT(DISTINCT (SampleID)) AS TotUniqueSample,
COUNT(DISTINCT (MachineGuid)) AS TotUniqueMachine,
(SELECT COUNT(DISTINCT (SampleID)) FROM CTE WHERE IsPass = 1) AS TotPass,
(SELECT COUNT(DISTINCT (SampleID)) FROM CTE WHERE FCCheck = 3) AS TotDuplicate,
(SELECT COUNT(DISTINCT (SampleID)) FROM CTE WHERE IsProjectExpired = 1) AS TotProjectExpire,
(SELECT COUNT(DISTINCT (SampleID)) FROM CTE WHERE IsMachineExpired = 1) AS TotMachineExpire,
(SELECT COUNT(DISTINCT (SampleID)) FROM CTE WHERE CountryCodeCheck IN (2, 4)) AS TotCCodeFail
FROM CTE)
SELECT *FROM CTE2;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-27 : 20:16:04

isnt this enough?


SELECT COUNT(ID) AS TotRequest,
COUNT(DISTINCT (SampleID)) AS TotUniqueSample,
COUNT(DISTINCT (MachineGuid)) AS TotUniqueMachine,
COUNT(DISTINCT CASE WHEN IsPass = 1 THEN SampleID END) AS  TotPass,
COUNT(DISTINCT CASE WHEN FCCheck = 3 THEN SampleID END) AS TotDuplicate,
COUNT(DISTINCT CASE WHEN IsProjectExpired = 1 THEN SampleID END) AS TotProjectExpire,
COUNT(DISTINCT CASE WHEN IsMachineExpired = 1 THEN SampleID END) AS TotMachineExpire,
COUNT(DISTINCT CASE WHEN CountryCodeCheck IN (2, 4) THEN SampleID END) AS TotCCodeFail
FROM dbo.ReportVRFlow
WHERE ProjectSettingID= @PsID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chih
Posting Yak Master

154 Posts

Posted - 2012-02-27 : 20:28:57

Thanks visakh16.

But is not using Case statement decrease the performance?
It consumes more resources in execution plan
cheers

quote:

Originally posted by visakh16

isnt this enough?


SELECT COUNT(ID) AS TotRequest,
COUNT(DISTINCT (SampleID)) AS TotUniqueSample,
COUNT(DISTINCT (MachineGuid)) AS TotUniqueMachine,
COUNT(DISTINCT CASE WHEN IsPass = 1 THEN SampleID END) AS  TotPass,
COUNT(DISTINCT CASE WHEN FCCheck = 3 THEN SampleID END) AS TotDuplicate,
COUNT(DISTINCT CASE WHEN IsProjectExpired = 1 THEN SampleID END) AS TotProjectExpire,
COUNT(DISTINCT CASE WHEN IsMachineExpired = 1 THEN SampleID END) AS TotMachineExpire,
COUNT(DISTINCT CASE WHEN CountryCodeCheck IN (2, 4) THEN SampleID END) AS TotCCodeFail
FROM dbo.ReportVRFlow
WHERE ProjectSettingID= @PsID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-27 : 21:06:35

even use of so many recursion will have an effect on performance. compare the execution times and see which one preforms better

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources