July 10, 2015 at 10:38 am
Is there any other way to write the below query:
SELECT
SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2, 3) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 3 AND STATUS IN (1, 2) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2) THEN Value - Discount ELSE 0 END)
FROM Orders
WHERE STATUS IN (1, 2, 3)
July 10, 2015 at 10:53 am
That's probably the best way to write it, I'd just add and additional condition for Type IN(3, 4).
Why do you want to change it?
July 10, 2015 at 10:59 am
Lets say someone asked me if there are any ways that this query can be improved by writing it differently and considering some indexing strategies, purely theoretical question. I couldnt think of anything so i thought I'll ask here, because it's Friday afternoon i'm sitting in the garden with a beer but this is still bothering me.
July 10, 2015 at 11:08 am
Yep. Just add the schema to the table and add the other column to the WHERE:
...
FROM dbo.Orders
WHERE STATUS IN (1, 2, 3) AND Type IN (3, 4)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 10, 2015 at 11:10 am
For indexing, you could use something like this:
CREATE NONCLUSTERED INDEX IX_Orders_Cover1
ON dbo.Orders(Type, Status)
INCLUDE (Value, Discount)
The Type and Status columns can change order depending on their selectivity.
Other options to write the query will be expensive, such as a multiple pivot or three queries using a cross join or an update that will scan the table several times. Basically, the cross tabs approach that you posted is probably the best method around, it just needed the extra filter in the WHERE clause.
July 10, 2015 at 11:11 am
Hmm... You must be right... I must be overcomplicating it in my head, i shouldnt be thinking about work anymore.
Thanks for quick replies, at leasti know i'm not going crazy and my initial thinking was correct. Have a good weekend guys.
July 10, 2015 at 12:04 pm
Kutang Pan (7/10/2015)
Is there any other way to write the below query:
SELECT
SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2, 3) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 3 AND STATUS IN (1, 2) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2) THEN Value - Discount ELSE 0 END)
FROM Orders
WHERE STATUS IN (1, 2, 3)
First thought is an index (Type) filtered on STATUS to satisfy the where clause, include the Value to make it a covering index.
Puzzles me though that the last condition will never be hit as it is a subset of the first one, typo perhaps?
😎
July 10, 2015 at 12:09 pm
Eirikur Eiriksson (7/10/2015)
Puzzles me though that the last condition will never be hit as it is a subset of the first one, typo perhaps?😎
It's more an optic illusion, as those are separate CASEs.
July 10, 2015 at 12:40 pm
Luis Cazares (7/10/2015)
Eirikur Eiriksson (7/10/2015)
Puzzles me though that the last condition will never be hit as it is a subset of the first one, typo perhaps?😎
It's more an optic illusion, as those are separate CASEs.
Me being dyslexic:rolleyes:
😎
July 10, 2015 at 1:11 pm
Luis Cazares (7/10/2015)
That's probably the best way to write it, I'd just add and additional condition for Type IN(3, 4).Why do you want to change it?
In support of this, here is a sample where you can get a bit of a boost - maybe. This is based on a 1 million row test table with random test data that I generated. Using the additional filter Luis specified, the performance of the query remains the same (as the OP posted) as without, but the query I changed it to gets a healthy boost to where it outperforms the OPs original (in other words as it was, it was performing rather well).
DECLARE @string VARCHAR(60) = '1,2,3';
CREATE TABLE #ordstatus (OStatus INT INDEX IX_OrdStatus CLUSTERED);
INSERT INTO #ordstatus
SELECT i.Item AS OStatus
FROM DBA.dbo.DelimitedSplit8K(@string, ',') i;
SELECT SUM(CASEWHEN Type = 4
AND STATUS IN ( 1, 2, 3 ) THEN Value
ELSE 0
END)
, SUM(CASEWHEN Type = 3
AND STATUS IN ( 1, 2 ) THEN Value
ELSE 0
END)
, SUM(CASEWHEN Type = 4
AND STATUS IN ( 1, 2 ) THEN Value - Discount
ELSE 0
END)
FROM Orders o
INNER JOIN #ordstatus os
ON o.status = os.OStatus
WHERE Type IN (3, 4)
/*with this predicate, this query now outperforms the original by 10% */
;
DROP TABLE #ordstatus;
GO
SELECT SUM(CASEWHEN Type = 4
AND STATUS IN ( 1, 2, 3 ) THEN Value
ELSE 0
END)
, SUM(CASEWHEN Type = 3
AND STATUS IN ( 1, 2 ) THEN Value
ELSE 0
END)
, SUM(CASEWHEN Type = 4
AND STATUS IN ( 1, 2 ) THEN Value - Discount
ELSE 0
END)
FROM Orders
WHERE STATUS IN ( 1, 2, 3 )
AND Type IN(3, 4)
;
FWIW - I also tested with CE 70 and 120 and got the same results as far as performance.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2015 at 8:54 am
Kutang Pan (7/10/2015)
Is there any other way to write the below query:
SELECT
SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2, 3) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 3 AND STATUS IN (1, 2) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2) THEN Value - Discount ELSE 0 END)
FROM Orders
WHERE STATUS IN (1, 2, 3)
Unless you care when other Types occur and since you only want 2 types I might would add
AND TYPE IN (3, 4)
Also I am trying to figure out what the intent is for the second value because you can have
1,0,1
0,1,0
But never
1,1,1 or 0,1,1 or 1,1,0
Otherwise indexing the Status column and maybe do a composite index with Status, Type if might improve performance on read. It will cause a hit on inserting, updating and deleting data so weigh that fact.
July 14, 2015 at 4:48 am
Antares686 (7/13/2015)
Kutang Pan (7/10/2015)
Is there any other way to write the below query:
SELECT
SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2, 3) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 3 AND STATUS IN (1, 2) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2) THEN Value - Discount ELSE 0 END)
FROM Orders
WHERE STATUS IN (1, 2, 3)
Unless you care when other Types occur and since you only want 2 types I might would add
AND TYPE IN (3, 4)
Also I am trying to figure out what the intent is for the second value because you can have
1,0,1
0,1,0
But never
1,1,1 or 0,1,1 or 1,1,0
Otherwise indexing the Status column and maybe do a composite index with Status, Type if might improve performance on read. It will cause a hit on inserting, updating and deleting data so weigh that fact.
+1
I was also thinking that adding a filter on Type would alter the original query to exclude zeroes for rows where the Type is neither 3 nor 4.
July 15, 2015 at 1:28 pm
Chris Wooding (7/14/2015)
Antares686 (7/13/2015)
Kutang Pan (7/10/2015)
Is there any other way to write the below query:
SELECT
SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2, 3) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 3 AND STATUS IN (1, 2) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2) THEN Value - Discount ELSE 0 END)
FROM Orders
WHERE STATUS IN (1, 2, 3)
Unless you care when other Types occur and since you only want 2 types I might would add
AND TYPE IN (3, 4)
Also I am trying to figure out what the intent is for the second value because you can have
1,0,1
0,1,0
But never
1,1,1 or 0,1,1 or 1,1,0
Otherwise indexing the Status column and maybe do a composite index with Status, Type if might improve performance on read. It will cause a hit on inserting, updating and deleting data so weigh that fact.
+1
I was also thinking that adding a filter on Type would alter the original query to exclude zeroes for rows where the Type is neither 3 nor 4.
Why would you think only with ones and zeros? It's possible to get a row as 1,1,1 or 1,1,0. A row as 0,1,1 wouldn't be logic as it would indicate negative discounts (a.k.a. charges).
Basically, this query will return either one row or no rows depending if there's data in the table. If zeros are needed, there are ways to add them without scanning the whole table.
July 16, 2015 at 7:38 am
Luis Cazares (7/15/2015)
Chris Wooding (7/14/2015)
Antares686 (7/13/2015)
Kutang Pan (7/10/2015)
Is there any other way to write the below query:
SELECT
SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2, 3) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 3 AND STATUS IN (1, 2) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2) THEN Value - Discount ELSE 0 END)
FROM Orders
WHERE STATUS IN (1, 2, 3)
+1
I was also thinking that adding a filter on Type would alter the original query to exclude zeroes for rows where the Type is neither 3 nor 4.
Why would you think only with ones and zeros? It's possible to get a row as 1,1,1 or 1,1,0. A row as 0,1,1 wouldn't be logic as it would indicate negative discounts (a.k.a. charges).
Basically, this query will return either one row or no rows depending if there's data in the table. If zeros are needed, there are ways to add them without scanning the whole table.
Recheck the logic, the Type cannot be both 4 and 3, which means 1,1,1 , 1,1,0 and 0,1,1 cannot occur. Which is why I bring it up to see if this is intended or if the goal may be something else. Just like saying use where Type in (3,4) if other values really don't need to be reported in the query. The issue is understanding the final goal of the output and what may be negatively happening overall.
July 16, 2015 at 8:33 am
Antares686 (7/16/2015)
Recheck the logic, the Type cannot be both 4 and 3, which means 1,1,1 , 1,1,0 and 0,1,1 cannot occur. Which is why I bring it up to see if this is intended or if the goal may be something else. Just like saying use where Type in (3,4) if other values really don't need to be reported in the query. The issue is understanding the final goal of the output and what may be negatively happening overall.
I just made up some sample data including the columns mentioned in the query and an additional to group by so I can show you the different examples which would generate the results that you say can't happen. I guess the problem is that you're missing the part that these are separate CASE statements in different aggregate functions, so no need to think row-by-row.
CREATE TABLE Orders(
OrderIdint IDENTITY,
OrderDatedate,
TYPEint,
STATUSint,
VALUEint,
Discountint
)
INSERT INTO Orders(OrderDate, TYPE, STATUS, VALUE, Discount) VALUES
('20150701', 3, 1, 1, 0),
('20150701', 4, 1, 1, 0),
('20150702', 3, 1, 1, 0),
('20150702', 4, 1, 1, 1),
('20150703', 3, 1, 1, 0),
('20150703', 4, 1, 0, -1),
('20150704', 1, 1, 1, 0),
('20150704', 2, 1, 5, 1),
('20150705', 3, 1, 51, 0),
('20150705', 4, 1, 61, 1),
('20150705', 3, 1, 19, 0),
('20150705', 4, 1, 11, 5);
SELECT OrderDate
,SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2, 3) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 3 AND STATUS IN (1, 2) THEN Value ELSE 0 END)
,SUM(CASE WHEN Type = 4 AND STATUS IN (1, 2) THEN Value - Discount ELSE 0 END)
FROM Orders
WHERE STATUS IN (1, 2, 3)
GROUP BY OrderDate;
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply