Which of these queries is more efficient?
Which开发者_开发技巧 of these queries are more efficient?
select 1 as newAndClosed
from sysibm.sysdummy1
where exists (
select 1
from items
where new = 1
)
and not exists (
select 1
from status
where open = 1
)
select 1 as newAndClosed
from items
where new = 1
and not exists (
select 1
from status
where open = 1
)
Look at the explain plan and/or profiler output. Also, measure it. Measure it using bind variables and repeated runs.
I think the second one is faster because on contrary to the first one, the sysibm.sysdummy1 table does not need to parse
From a simplistic point of view I'd expect query 2 to run faster because it involves fewer queries, but as Hank points out, running it through a Profiler is the best way to be sure.
They will produce different results if items
contains more than one element with new = 1
. exists
will only check first record which matches the condition. So I'd vote for first variant, if in actual query you don't have relation between items
and status
(as in your example).
P.S. Usually I use SELECT 1 WHERE 2==2
when I need only one result from nowhere. If I need more SELECT 1 UNION SELECT 2
.
I would personally say the second query.
First, it queries the table Items
directly and filter with the WHERE clause on a field of this table.
Second, it uses only one other subquery, instead of two.
In the end, the second example ends doing only two queries, when the first example does three.
Many queries will always be more expensive than less queries to manage for the database engine. Except if you perform EXISTS verifications instead of table joints. A joint is more expensive than an EXISTS clause.
精彩评论