June 18, 2009 at 4:05 pm
Does following query returns the first record from the table?
SELECT TOP 1* FROM TicketActivity
I think NO. The resultant TicketId of following query is different from the ticketId we fetched from first query.
SELECT MIN(TicketId) FROM TicketActivity
The clustered key for this table is @ following columns:
TicketId, ActDtim, PostActName
How both are different? Any heads up!!
-MJ
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
June 18, 2009 at 4:40 pm
Is TicketID an identity field?
What kind of data is in ActDtim?
Since the clustered key is the three columns combined, and if you have datetime as the data type in ActDtim, then it is perceivable that the lowest identity is not listed as the first record in the clustered index. A misaligned insert statement could have inserted into your table with an invalid date that predated the lowest identity value you have in the table and produce the irregular results.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2009 at 4:59 pm
Keep in mind that the order of rows returned is never guarenteed without an ORDER by clause. You should include that if you are looking for the first anything.
June 18, 2009 at 5:25 pm
The clustered Key is the primary key only. One of the column is Ticketid and other two columns as per my previous post.
I have executed SELECT TOP 1* FROM TicketActivity at least 10 times but I got the same response. I don't see any role of order by clause here. This fetches data from somewhere everytime we execute this command. What is that criteria when this doesn't fetch the first data from table ie Min(TicketId)?
-MJ
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
June 18, 2009 at 6:08 pm
Without an ORDER BY clause there is no guarantee that SQL Server will return the same row every time.
As a very simplistic example, say someone has just done a query to select all the rows from the table ordered by a different field. All the data is therefore in memory (if it's a small enough table) but the first row that SQL Server might read in that case is the first row based on the sort order of the earlier query.
If you want the smallest TicketId then you MUST say that, either using a min(TicketId) or an ORDER BY TicketId: you can't assume that you'll always get the same row.
June 18, 2009 at 6:11 pm
Which of the fields is the identity field? What are the datatypes for the remaining fields in your Key?
When comparing these queries, it is difficult to answer your question without knowing the datatypes we are dealing with. Having a key combined of three different fields is going to play into your query results. Since you said the three fields comprise a clustered key - that indicates to me that your clustered index is based on those fields - this will make a difference in your queries. Since the key is used in the Select top 1 * query and the entire key is not being used when performing a select min(id).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 18, 2009 at 11:13 pm
SELECT TOP 1* FROM TicketActivity
SELECT MIN(TicketId) FROM TicketActivity
The above 2 queries are different.
But you can also run the min query as below :
select top 1 ticketid from TicketActivity order by ticketid
Check the sub-tree cost of bot the queries and see which one is more beneficial.Most probably the cost would be same but i am confident that in any case the query with orderby clause would not be using Stream Aggregates and should be a better choice .
HTH
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 19, 2009 at 7:49 am
What you've got are an ordering query, TOP, and an aggregate query, MIN. The ordering query must have an ORDER BY statement to work. You can't know what order things will come out of the db in. So, to compare these two, you'd need to write the first one like this:
SELECT TOP 1 * FROM TicketActivity
ORDER BY TicketID ASC
The ORDER BY statement will establish the order of the records returned and the ASC will make them ascend from the lowest to the highest. So this query will return the same value as the other.
BTW, depending on your indexes, this query is likely to perform better than the other. The reason for this is that ordering the values from an index is a pretty inexpensive operation, but aggregating values is usually more expensive. But your mileage may vary, so test within your environment.
"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
June 19, 2009 at 7:52 am
hi_abhay78 (6/18/2009)
SELECT TOP 1* FROM TicketActivitySELECT MIN(TicketId) FROM TicketActivity
The above 2 queries are different.
But you can also run the min query as below :
select top 1 ticketid from TicketActivity order by ticketid
Check the sub-tree cost of bot the queries and see which one is more beneficial.Most probably the cost would be same but i am confident that in any case the query with orderby clause would not be using Stream Aggregates and should be a better choice .
HTH
Just a note, sub-tree costs are estimated values. They don't accurately represent performance. Better to compare the scans, reads and actual execution times of the two queries to determine which is faster.
"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
June 19, 2009 at 8:03 am
The fact that you always got the same results at the past doesn’t guarantee that you’ll always get the same results at the future. If something would change in the future you’ll might be surprised with different results that you’ll get. This could be due to a service pack or new version of SQL Server or could even be because of modifications in the table’s indexes. Hugo Kornelis had a great post that showed that shows that data won’t always be returned according to the clustered index’s order. You can read that post at http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx%5B/url%5D
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply