November 8, 2007 at 12:02 pm
Hi everyone,
I am running two sql queries and want to find a way that I might be able to combine them into one executable query, such that it runs once and produces one data set:
SELECT Email, 'Active' as Type FROM Subscriptions WHERE Expiration > '1/1/07'
SELECT Email, 'Expired' as Type FROM Subscriptions WHERE Expiration < '1/1/07'
Any ideas how to do this?
Thanks,
Wade
November 8, 2007 at 12:09 pm
Wade,
One thing, you're not handling the case when the EXPIRATION field is equal to 1/1/07, but my code below does. It will put those records into the expired group.
SELECT
,CASE WHEN Expiration > '1/1/07' THEN 'Active' ELSE 'Expired' END AS [Type]
FROM
Subscriptions
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 8, 2007 at 12:10 pm
Use "UNION ALL" or "UNION".
N 56°04'39.16"
E 12°55'05.25"
November 8, 2007 at 12:15 pm
Peter Larsson (11/8/2007)
Use "UNION ALL" or "UNION".
Peter, do you have any reasoning why UNION or UNION ALL would be preferred over my solution? Just curious.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 8, 2007 at 2:35 pm
I didn't see your suggestion when posting.
But it can make a difference depending on which indexes that are present.
N 56°04'39.16"
E 12°55'05.25"
November 8, 2007 at 2:50 pm
CREATE TABLE#Temp
(
eMail CHAR(36),
Expiration DATETIME
)
INSERT#Temp
SELECTTOP 1000
NEWID(),
29000 + ABS(CHECKSUM(NEWID())) % 20000
FROMmaster..syscolumns
CREATE INDEX IX_Temp ON #Temp (Expiration, eMail)
-- Peso
SELECTEmail,
'Active' as Type
FROM#Temp
WHEREExpiration > '1/1/07'
UNION ALL
SELECTEmail,
'Expired' as Type
FROM#Temp
WHEREExpiration < '1/1/07'
-- Jason
SELECTeMail,
CASE
WHEN Expiration > '1/1/07' THEN 'Active'
ELSE 'Expired'
END AS [Type]
FROM #Temp
DROP TABLE #Temp
-- Peso
|--Concatenation
|--Compute Scalar(DEFINE:([Expr1004]='Active'))
| |--Index Seek(OBJECT:([tempdb].[dbo].[#Temp]), SEEK:([tempdb].[dbo].[#Temp].[Expiration] > '2007-01-01 00:00:00.000') ORDERED FORWARD)
|--Compute Scalar(DEFINE:([Expr1009]='Expired'))
|--Index Seek(OBJECT:([tempdb].[dbo].[#Temp]), SEEK:([tempdb].[dbo].[#Temp].[Expiration] < '2007-01-01 00:00:00.000') ORDERED FORWARD)
-- Jason
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [tempdb].[dbo].[#Temp].[Expiration]>'2007-01-01 00:00:00.000' THEN 'Active' ELSE 'Expired' END))
|--Table Scan(OBJECT:([tempdb].[dbo].[#Temp]))
N 56°04'39.16"
E 12°55'05.25"
November 8, 2007 at 2:51 pm
Ah Ha!
Thanks.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 8, 2007 at 3:11 pm
Of course, the optimizer "likes" table scans when you're using every field in a table in a query....might be a little different with the other fields a "real" table might have.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 3:18 pm
The thing is that only Expiration and eMail columns are used in the query so the ix_temp index could be considered a covering index, right?
N 56°04'39.16"
E 12°55'05.25"
November 8, 2007 at 3:58 pm
True - but the overhead of the union balances the two processes out....
On a million records, the table scan matches the union (within .2 seconds. )
Peso's - 7743
Jason's - 7540
Now - if you just throw in another field, say - char(300) (to make the table scan unattractive), Peso's keeps its plan, but Jason's turns into an index seek, and you end up with
Peso's - 9670
Jason's - 11543
So - the optimizer does look pretty closely at the index as a choice, but if it's not clustered and the index key is large relative to the table size, it may choose not to use it. And STILL outperform an index seek by doing one single pass through the data.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 6:44 pm
Also - the 2 seconds disappear if you change the index to
CREATE INDEX IX_Temp ON #Temp (Expiration) INCLUDE (eMail)
Both then represent 50% of the execution plan, and BOTH take approx. 9.6 seconds (with a few ms edge to the index scan if that were to make a difference...)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply