March 27, 2008 at 9:42 am
Query Analzer's Execution Plan gives %85 for the first query below and %15 for the second one.
ID is the primary index on myTable. Do you know a better method to get the last added record in a table?
select * from myTable where id = (select max(id) from myTable)
select * from myTable where id = (select top 1 id from myTable order by id desc)
March 27, 2008 at 9:46 am
SELECT TOP 1 * FROM myTable order by myKey DESC
ST
March 27, 2008 at 10:01 am
God! What did I think when writting this query! 🙂
Anyway, why Query Analyzer gives that results for these queries?
46% : select * from myTable where id = (select max(id) from myTable)
7% : select * from myTable where id = (select top 1 id from myTable order by id desc)
%46 : select top 1 * from myTable order by id desc
or
14% : select * from myTable where id = (select top 1 id from myTable order by id desc)
85% : select top 1 * from myTable order by id desc
or
50% : select * from myTable where id = (select max(id) from myTable)
50% : select top 1 * from myTable order by id desc
It means "select * from myTable where id = (select top 1 id from myTable order by id desc)
" is faster than "select top 1 * from myTable order by id desc" ?
March 27, 2008 at 10:15 am
I have no idea. I just did a similar test on my machine and got expected results. FYI the execution plan isn't always the best guide. You should turn on statistics IO and see the results there.
Example:
Set Statistics IO ON
-- Execute this. Table has 316,000 records
select top 1 * from tblBICost order by fldBICostKey desc
-- Results
logical reads 3, physical reads 0, read-ahead reads 0.
-- Execute
select * from tblBICost where fldBICostKey = (select TOP 1 fldBICostKey from tblBICost order by fldBICostKey desc)
-- Results
logical reads 6, physical reads 0, read-ahead reads 0.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply