Derived Table X SubQuery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Derived Table X SubQuery

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-20 : 07:26:15

Obede writes "I have two queries that return the same registers.
One uses subquery and the another uses derived table.
What's better? Subquery or Derived Table?

-- SubQuery
SELECT DISTINCT itm.po_func_cod
FROM po_itemfuncao itm (NOLOCK),
po_parametro par (NOLOCK)
WHERE itm.po_para_cod = par.po_para_cod
AND par.po_bloc_cod = 99
AND itm.po_func_cod NOT IN (
SELECT DISTINCT itm.po_func_cod
FROM po_itemfuncao itm, po_parametro par
WHERE itm.po_para_cod = par.po_para_cod
AND par.po_bloc_cod <> 99 )

-- Derived Table
SELECT DISTINCT fun.po_func_cod
FROM
(
SELECT DISTINCT itm.po_func_cod
FROM po_itemfuncao itm, po_parametro par
WHERE itm.po_para_cod = par.po_para_cod
AND par.po_bloc_cod = 99
) fun LEFT JOIN
(
SELECT DISTINCT itm.po_func_cod
FROM po_itemfuncao itm, po_parametro par
WHERE itm.po_para_cod = par.po_para_cod
AND par.po_bloc_cod <> 99
) fnc
ON fun.po_func_cod = fnc.po_func_cod
WHERE fnc.po_func_cod IS NULL

Thank you very much

Obede
Brazil"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-05-20 : 09:01:48

Neither of them is necessarily better, it depends upon a lot of things including the amount of data in the tables, indexes defined on them, etc. The best way to find out which one is better is to execute both queries together and see the execution plans in Query Analyzer. It should give you a good indication of which query is better. ANd then you try tweaking those queries by adding indexes to the relevant tables and measuring the impact on performance.

BTW, i hope you know what (NOLOCK) does...

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources