SQL Server Performance Forum – Threads Archive
redundant select stmts
I have a select stmt returns ONE and ONLY ONE record, looks like this: select * from table1 where_clause union allselect * from table2 where_clause but I prefer not to do 2 selects, if I find it in table1
then I’d like to skip the 2nd select, go to the 2nd one only
when the 1st one returns no record. I could do an if exists on the 1st select, again, I need to
execute if exists is true, resulting in 2 selects: IF exists (SELECT * FROM table1 where_clause)
BEGIN
SELECT * FROM table1 where_clause
END ELSE
SELECT * FROM table2 where_clause
END
I can’t have a NULL returned either, so
SELECT * FROM table1 where_clause
if ( @@rowcount = 0 )
SELECT * FROM table2 where_clause
is not what I want. Thanks for your help!
Just an idea, haven’t tried it yet.
SELECT TOP 1 *
FROM (
SELECT TOP 100 PERCENT 1 myorder, *
FROM table1
where_clause UNION ALL SELECT TOP 100 PERCENT 2 myorder, *
FROM table2
where_clause
order by myorder
) x
Bambola.
Brilliant!
It works.
One comment: The selected result is what I wanted, but I think it is
still does TWO selects, one from table1, one from table2
by UNION ALL. What is the execution sequence from select top 1 * from — (A)
(
select top 100 percent * from table1 whereclause — (B)
UNION ALL
select top 100 percent * from table2 whereclause — (C)
) B AND C, THEN A so 2 selects one on table1, one on table2, or
A, THEN B if B returns one or more record, and A THEN B THEN C if B returns NULL ?
Execution Plan shows a 50-50 costs on table1 and table2
Try this:
declare @table1 table (i int)
declare @table2 table (i int) insert into @table1 select 5 union select 4
insert into @table2 select 0 union select 4 SELECT TOP 1 *
FROM (
SELECT TOP 100 PERCENT 1 myorder, i/i col1
FROM @table1 UNION ALL SELECT TOP 100 PERCENT 2 myorder, i/i — deviding by 0 should return an error!
FROM @table2
order by myorder
) x
This will return one line. But if you will try to SELECT TOP 3 you will get an error. So I suppose it is not processing the second table. Bambola.
just to let you know,
the cost of the rpc call, that is, sending a packet over the network, interrupting sql server, parsing the request, and sending a packet back, far out weighs the cost of a single row select query (suitably indexed) or even two single row select queries.
try blasting your server with just 1 select statement, then 2 select statements
my bet is that there will be less than 10% performance difference,
hence i would find other queries to optimize
]]>