appending/joining result sets of subqueries
-
I'm having difficulty with finding a way to append the result sets of subqueries together in a performance-wise manner. I'm using newid() to create random selections of the database, and here is the query that I have now:
select distinct customer_id from orders where order_id in
((select top 1000 order_id from orders where order_date>2005 order by newid()) or order_id in
(select top 1000 order_id from orders where order_date<1994))
This particular form is, for some bizarre reason, 20 times as slow as when there is only one subquery.
UNION doesn't work. You can't have order by inside unionized subqueries, and you can't have order by newid() outside, either (and you wouldn't want to, because newid() performance degrades above 1000 entries). Just a comma doesn't work because it is returns multiple entries.
Any thoughts?
-
Use derived tables instead of sub-queries
Use joins instead of IN() for improved efficiency
Use a UNION ALL for mutually exclusive resultsets, instead of an OR condition
select distinct customer_id
from orders As o
Inner Join
(
Select top 1000 order_id
from orders
where order_date > 2005 order by newid()
) dt1
On (o.order_id = dt1.order_id)
UNION ALL
select distinct customer_id
from orders As o
Inner Join
(
Select top 1000 order_id
from orders
where order_date < 1994 order by newid()
) dt2
On (o.order_id = dt2.order_id)
-
Thanks very much!
Jon
-
SQLServer gets the error:Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.
-
GOT IT! I added "newid() as newid" as the 2nd column to each of the derived tables. That satisfied SQLServer syntax.Thanks VERY much for your help
Jon Campbell
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply