SQL - IN - EXISTS и има ли разлика между тях.

Posted by Stanislav Nedelchev on Fri, 10/31/2008 - 00:16

От опита ми преди време бях установил, че SQL оператора EXISTS работи по-бързо от IN.
Обаче реших с една проста заявка да видя как е положението в MS SQL сървър 2005.
Имената на таблиците за променени в последствие.
Но броят на редовете е верен за статистиката.
Въпреки че си мисля че в таблицата по която се търси са малко записите.
Може би трябваше да пробвам с по-голяма.
И ето какъв е резултата.
Оставям изводите на Вас.

SET STATISTICS IO ON
SET STATISTICS TIME ON
select count(p.sorsa) from dbo.podlozkata p
where p.sorsa in (select s.subekta from dbo.moiatsklad s)
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SET STATISTICS IO ON
SET STATISTICS TIME ON
select count(p.sorsa) from lcs.podlozkata p
where exists (select s.subekta from dbo.moiatsklad s where s.subekta = p.sorsa)
SET STATISTICS TIME OFF
SET STATISTICS IO OFF

Резултата:

-----------
412375

(1 row(s) affected)

Table 'moiatsklad'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'podlozkata '. Scan count 1, logical reads 1552, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 99 ms.

-----------
412375

(1 row(s) affected)

Table 'moiatsklad '. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'podlozkata '. Scan count 1, logical reads 1552, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 98 ms.

Happy SQLing

Tags: 

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.