[thelist] Quick SQL question
Joshua Olson
joshua at waetech.com
Mon Feb 10 18:42:01 CST 2003
----- Original Message -----
From: ".jeff" <jeff at members.evolt.org>
Sent: Monday, February 10, 2003 5:42 PM
> actually, i just ran the results on a table with 8400 records and am
seeing
> some difference in times. your version executes consistently about 2/3 of
> the time it takes mine to execute.
Jeff, et al,
In case you are wondering why Jason's query ran faster I may offer a
layman's explanation. Jeff, your subquery returns 15 values from the table
of 8400 records and then executes a second query against the same table of
8400 records. Jason, your query pulls 15 records from the table and then
simply returns the 15 records in reverse order. If Col_ID is an index on
the table, then the reversal is a trivial transformation.
Usually, though not always, the syntax offered by Jason will be faster.
FWIW, the syntax offered by Jason is basically creating a view and then
executing a query against that view. The view being:
SELECT TOP 15 * FROM MyTable ORDER BY Col_ID DESC
To illustrate that the inner query is semantically equal to a view in SQL
Server, try removing the "TOP 15" from the query:
SELECT * FROM
(SELECT * FROM MyTable ORDER BY Col_ID DESC) MyTable
ORDER BY Col_ID ASC
Obviously this is an asinine query, but it illustrates the fact that the
inner SELECT is being treated as a view. As you would expect (as with a
view) the parser returns an error on the ORDER BY clause. A common trick
around this with SQL Server is to use TOP 100 PERCENT.
-joshua
More information about the thelist
mailing list