June 8, 2010 at 12:46 pm
Hello all.
I need some help in optimizing query with aggregate function and case statement
Here is the situation
I have a table with columns of Port_ID, Port_Value and Timestamp
Below is correct Table Structure
CREATE TABLE Port_Record(
Port_ID int,
Port_value int,
Port_Timestamp datetime
)
What I need to do with this table is that i need to get the port value data and assign plus or minus based on Port ID (at certain timestamp). then calculate the total. After the total is calculated, if the total is negative or zero, zero will be assigned as total. if the total has positive value, then the value will be taken as it is.
So I create a query below
select
case
when (sum(
case
when Port_ID in (1,2) then Port_value
when Port_ID in (3) then -Port_Value
end
) > 0)
then sum(
case
when Port_ID in (1,2) then Port_value
when Port_ID in (3) then -Port_Value
end
)
else 0
end
from Port_Record
where Port_Timestamp = '*********'
Although the query is working correctly, I want to know if there are any other ways to optimize the query like using sub query then use case in select statement.
thank you for all the help in advance.
June 8, 2010 at 1:40 pm
I probably would use a CTE to calculate the sum using a case condition like you did. I think the execution plan will be the same but it's easier to read and to maintain.
If you'd provide some sample data I'd post the tested code. 😉
June 8, 2010 at 2:29 pm
The most obvious optimization would be to make sure Port_Timestamp is an index. Also, if the PortRecord table has more than the 3 fields you stated, you may want to consider a covering index that includes the two fields you are using.
In case you're already using an index, I have a few thoughts on how you might be able to optimize it:
I'm not sure what kind of benefit it would have, but here's how I'd do it:
select
case when Total > 0 then Total else 0
from (
select
sum(case
when Port_ID in (1,2) then Port_value
when Port_ID in (3) then -Port_Value
end) AS Total
from PortRecord
where Port_Timestamp = '********'
) A
That'd at least keep it from calculating the sum twice.
If you still need more performance, it might be worth trying something like this:
DECLARE @lookup TABLE (
ID int
sign int
)
INSERT INTO @lookup (ID, sign) VALUES (1, 1)
INSERT INTO @lookup (ID, sign) VALUES (2, 1)
INSERT INTO @lookup (ID, sign) VALUES (3, -1)
select
case when Total > 0 then Total else 0
from (
select
sum(Port_value * sign) AS Total
from PortRecord
left join @lookup ON Port_ID = ID
where Port_Timestamp = '********'
) A
Again, I don't know what kind of performance benefit (if at all) you will get from this, but forgoing the CASE statement in favor of a simple join might be a little easier on the optimizer. I'm imagining it can sum the multiple of two field relatively quickly, but it may have to perform the CASE statement row-by-row.
Let me know if any of that works for you. If not, there may very well be other options.
--J
June 8, 2010 at 2:44 pm
Why not one more variation 🙂 :
SELECT CASE WHEN Port_Value_Total >= 0 THEN Port_Value_Total ELSE 0 END AS Port_Value_Total
FROM (
SELECT SUM(CASE WHEN Port_ID = 3 THEN -PortValue ELSE PortValue END) AS Port_Value_Total
FROM Port_Record
WHERE Port_ID IN (1, 2, 3)
AND Port_Timestamp = '*********'
) AS derived
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply