Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Difference Expand / Collapse
Author
Message
Posted Thursday, June 18, 2009 4:05 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 3, 2011 4:58 PM
Points: 88, Visits: 191
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.
Post #737973
Posted Thursday, June 18, 2009 4:40 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 17,948, Visits: 15,947
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
Post #737980
Posted Thursday, June 18, 2009 4:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:25 PM
Points: 31,279, Visits: 15,740
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #737985
Posted Thursday, June 18, 2009 5:25 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 3, 2011 4:58 PM
Points: 88, Visits: 191
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.
Post #737991
Posted Thursday, June 18, 2009 6:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:25 PM
Points: 2,113, Visits: 877
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.
Post #737998
Posted Thursday, June 18, 2009 6:11 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 17,948, Visits: 15,947
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
Post #738000
Posted Thursday, June 18, 2009 11:13 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:27 PM
Points: 267, Visits: 386
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)
Post #738053
Posted Friday, June 19, 2009 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 14,004, Visits: 28,385
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #738312
Posted Friday, June 19, 2009 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 14,004, Visits: 28,385
hi_abhay78 (6/18/2009)
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


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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #738317
Posted Friday, June 19, 2009 8:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 2,125, Visits: 5,545
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

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/
Post #738329
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse