|
|
Posted by Erland Sommarskog on 06/04/05 01:26
DA Morgan (damorgan@psoug.org) writes:
> It is all available, including the data, at:
> http://www.psoug.org
> click on Morgan's Library
> click on Explain Plan
>
> #5 is a piece of work. It is the realization, in SQL, of a double
> negative. In Oracle #6 is definitely the superior solution with the
> specific data set I created. But I've never met a front-end programmer
> that knew enough SQL to write it.
Thanks for the scripts. After some tweak I got the script running on
SQL Server. On SQL 2005, #1, #2, #4 and #6 produced the same plan. I
tried adding the missing foreign key, and also a non-clustered index
on serv_inst.srvid. All plans were still the same.
On SQL 2000, #2, #4 and #6 produced the same plan. #1 and #5 did
not produce any plan at all, as INSERSECT and EXCEPT are not supported
on SQL 2000.
Thus, while your script for Oracle is a good demonstration of Explain Plain,
and that different constructs may affect the query plan, for SQL Server
it may rather demonstrate that SQL Server is quite good at rewriting
queries internally. (But don't worry. Funny tweaks with queries to good
performance are commonplace with SQL Server as well.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|