February 23, 2009 at 11:14 pm
I have Two T-SQL statement like that :
1. select top 1 end_time from Log_Network_circs order by end_time desc;
2. select max(end_time) from Log_Network_circs;
The table named "Log_Network_circs" describe like that :
CREATE TABLE [Log_Network_circs](
[log_id] [varchar](30) NOT NULL,
[node_code] [varchar](100) NULL,
[node_name] [varchar](100) NULL,
[server_name] [varchar](100) NULL,
[start_time] [datetime] NULL,
[end_time] [datetime] NULL,
[status] [varchar](30) NULL,
CONSTRAINT [PK_LOG_NETWORK_CIRCS] PRIMARY KEY CLUSTERED
(
[log_id] ASC
)
It has about 2650000 records
NO index on "end_time" field.
Question:
1. Which sql statement's performance is better?
2. "Top N Sort" and "Aggregate"(Max function) is has a big different at "estimated subtree cost "
, what is "estimated subtree cost " means?
February 23, 2009 at 11:33 pm
From BOL -
The total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree.
"Keep Trying"
February 23, 2009 at 11:56 pm
Chirag (2/23/2009)
From BOL -The total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree.
Thank you for your reply and Good Morning!
I found a Blog Post here talking about this :
But, is that true 😛
February 24, 2009 at 5:18 am
check these links.
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/query-cost.aspx.
This link contains another inner link. Read the comments section of the inner link.
"Keep Trying"
February 24, 2009 at 5:48 am
1) Without an index, I suspect both queries will result in a table scan. You would want an index there. With an index, I've generally found TOP to work better with these types of queries. That's through quite a lot of testing. However, circumstances vary and you might see different results. Testing is the key.
2) Estimated cost is a very useful comparison on queries, but it has a key word in it that points out why you can't rely on it, estimated. It's an estimated return value based on the optimizer's interpretation of the statistics (or lack thereof) on the column(s) in question. So, if your statistics are out of date, you can get bad values there. To really compare apples to apples, you need to look at STATISTICS IO and TIME to see how one query compares to another.
It's not available online (yet), but the last issue of SQL Server Standard has an article I wrote comparing the performance between TOP, MAX, and ROWVERSION. If you can get a copy it might be worth a read.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 24, 2009 at 6:22 am
Grant,
can you provide me the link of your article?
karthik
February 24, 2009 at 6:25 am
Like I said, it's not online. It was published in the print magazine SQL Server Standard, which is published by SQL PASS. They don't have the articles on line. Go to sqlpass.org and send them a message requesting it. Maybe it's somewhere that I can't find. Sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply