Posted by Roy Harvey on 02/19/07 22:33

Assuming I didn't make a mistake - the code is untested of course -
the following might perform slightly better. Or not, a lot depends on
the indexing and actual data.

SELECT SUM(Sent) AS Sent,
SUM(Received) AS Received,
[ID]
FROM (SELECT CASE WHEN DM.SenderID = @pp
THEN 1
ELSE 0
END AS Sent,
CASE WHEN DM.ReceiverPersonID = @pp
THEN 1
WHEN DM.ReceiverDepartmentID NOT IN
(SELECT PD.DepartmentID
FROM CF..PersonnelDepartment PD
WHERE PD.PersonID = @pp
AND PD.ObsoleteDate IS NULL)
THEN 0
WHEN DM.StationID IN
(SELECT PS.StationID
FROM CF..PersonnelStationsResponsibility PS
WHERE PS.PersonID = @pp
AND PS.ObsoleteDate IS NULL)
THEN 1
WHEN DM.InstructionNr IN
(SELECT I.InstructionNr
FROM WSDMS..InstructionsNewest I
WHERE I.PUID = @pp
AND I.PUID IN
(SELECT PU.ID
FROM WSDMS..PUs PU))
THEN 1
ELSE 0
END AS Received,
DM.*
FROM WSDMS..DocumentMessages DM
WHERE DM.ObsoleteDate IS NULL) as X
GROUP BY [ID]

I left all the IN tests as IN tests, (though one was reversed to a NOT
IN). Another change that could be worth trying is to rewrite each one
as an EXISTS test. In any case the use of subqueries in the CASE
should limit the number of times the subqueries are executed, which is
where any performance gain will come from.

Roy Harvey
Beacon Falls, CT

On 19 Feb 2007 13:21:45 -0800, bbla32@op.pl wrote:

>I'll show you my query which doesn't want to compile for some reason
>(Incorrect syntax near the keyword GROUP):
>
>DECLARE @pp INT
>SET @pp = 7
>
>
>SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>
>FROM (
>
>SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.SenderID = @pp
>
>UNION
>
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverPersonID = @pp
>
>UNION
>
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
>AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelStationsResponsibility PS
> WHERE PS.PersonID = @pp AND PS.ObsoleteDate IS NULL)
>
>UNION
>
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..DocumentMessages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepartmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDepartment PD
> WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
>AND DM.InstructionNr IN
> (SELECT I.InstructionNr FROM WSDMS..InstructionsNewest I
> WHERE I.PUID = @pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @pp)
> )
>
>)
>
>GROUP BY [ID]

[Back to original message]