Slow query help needed!!!!
-
Can't blame you... I can't speel very weil either!
. -
There is some very good informaton located here http://qa.sqlservercentral.com/columnists/gvijayakumar/whentousetheorderbyclause.asp
on INDEXEs. Since you have a clustered index you do not need the ORDER BY CLAUSE as your data is already sorted. I would love to know how much removing the ORDER by clause speeded up the processing.
HTH Mike
-
In spite of what that article states it is not a good practice.
From Books Online:
ORDER BY order_list [ ASC | DESC ]
The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted in an ascending or descending sequence.
ORDER BY is important because relational theory specifies that the rows in a result set cannot be assumed to have any sequence unless ORDER BY is specified. ORDER BY must be used in any SELECT statement for which the order of the result set rows is important. (Emphasis added )
-
You might want to check out this thread:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=181747&p=4
It starts on a different subject but ends with a debate about the necessity of the Order By clause.
-
Ron
One of the most critical factors in a RDBMS is the speed with which it can return data. Logically ordered data is faster to search than unordered data. When performing a sequential search on an ordered list you can terminate your search once you have passed the value you were seeking. For example a list of values 1,2,3,4,7,8 and we are searching for 6 we would have to compare each value to determine if it equaled 6 however when we reached 7 which is larger than 6 we could terminate the search because we know that 6 can not be in our ordered list. Requires 5 comparisions on this small data set.
Such a search is not efficient for a large database. If a database contained a million rows the average number of comparisons needed to find a given number would be 500,000.
A binary search of an ordered list is very fast. A binary search algorithm on an ordered list will in the worst-case require approximately log2 n-1 comparisons to find the item or eliminate the possibility of it being in the list. In a sorted list of a million rows a single value can be located using 20 comparisons with the average being 19. (see http://planetmath.org/encyclopedia/BinarySearch.html)
True there is no guarantee that Microsoft will continue to order data on the clustered index. But to think that they would give up the ability to perform binary searches is absurd.
Mike
-
“ORDER BY is important because relational theory specifies that the rows in a result set cannot be assumed to have any sequence unless ORDER BY is specified. ORDER BY must be used in any SELECT statement for which the order of the result set rows is important. (Emphasis added )”
From BOL
A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. TSQL also stores this data in continuous memory locations on the hard drive reducing I/O reads.
Relational theory specifies that the rows in a result set cannot be assumed to have any sequence. This is true. But overlooks the fact that after the data is entered TSQL sorts the data on the clustered Index.
[edited: for a discussion on binary searches OR why MS will not give up ordering data on a clustered index see my last post on the previous page
]Mike
-
I understand how clustered indexes work and they can be a very handy feature. The bottom line is that if you truly want to guarantee the order in which the rows are returned you must use ORDER BY.
-
Rob I agree that if you truly want to guarantee the order in which the rows are returned you must use ORDER BY. This covers the cases where the table has no index, un-clustered index(s) and or a clustered index. Works every time any time no mess no muss no fuss.
Efficient search algorithms require sorted data. This applies to both seek and scan operations. The reason for the existence of a clustered index is to store the data in an ordered condition so that it can be read back as ordered data. The alternative it to sort the data on the fly just before making a search which is not efficient. Any time savings created by the efficient search operations would be overshadowed by the time lost sorting the data.
If your clustered index is by date hired (DateTime Data) you will always, without fail, never miss, until the end of time, guaranteed have the data returned ordered by the date and time a person was hired. Inserting, deleting or changing any column will not keep the data from returning the rows in their proper order of date and time hired.
This behavior is guaranteed by the architecture of the system. If Microsoft were to break the system architecture they would lose their ability to perform binary searches and efficient scan operations. They would also loose most if not all of their customers the day after they broke the system. Not good my son works for them.
As to the documentation in BOL, I have been a fan of Microsoft Documentation for years. Their writing is always, clear, concise, easy to read with never an error or a vague or misleading statement. (Bet you can’t say that 3 times with a straight face)
I will believe my own eyes if anyone can produce an example that proves me wrong. You can never prove a theory you can only fail to disprove it.
-
"...If your clustered index is by date hired (DateTime Data) you will always, without fail, never miss, until the end of time, guaranteed have the data returned ordered by the date and time a person was hired...."
I guess this is where we disagree. If you read this thread (mentioned above) you will find examples that indicate your statement is not true.
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=181747&p=4
-
I already posted something that disproves that in this very thread... I think this is a lost cause. Let him not use the order by and get bitten later on so that he gets the point.
-
My mistake.. I was referring to this script :
CREATE TABLE [dbo].[TypesCommentairesADPS] (
[PkTypeCommentaireADP] [int] IDENTITY (1, 1) NOT NULL ,
[DescTypeCommentaireADP] [varchar] (50) COLLATE French_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TypesCommentairesADPS] WITH NOCHECK ADD
CONSTRAINT [PK_TypesCommentairesADPS] PRIMARY KEY CLUSTERED
(
[PkTypeCommentaireADP]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TypesCommentairesADPS] ADD
CONSTRAINT [IX_TypesCommentairesADPS_Unique] UNIQUE NONCLUSTERED
(
[DescTypeCommentaireADP]
) ON [PRIMARY] ,
CONSTRAINT [CK_TypesCommentairesADPS_DescTypeCommentaireADP] CHECK ([DescTypeCommentaireADP] '')
GO
Insert into dbo.TypesCommentairesADPS (DescTypeCommentaireADP) values ('Update')
Insert into dbo.TypesCommentairesADPS (DescTypeCommentaireADP) values ('Bug')
Insert into dbo.TypesCommentairesADPS (DescTypeCommentaireADP) values ('Amélioration')
Select * from dbo.TypesCommentairesADPS
/*
Index scan (IX_TypesCommentairesADPS_Unique)
PkTypeCommentaireADP DescTypeCommentaireADP
-------------------- --------------------------------------------------
3 Amélioration
2 Bug
1 Update
(3 ligne(s) affectée(s))
*/
ALTER TABLE dbo.TypesCommentairesADPS
drop constraint IX_TypesCommentairesADPS_Unique
GO
Select * from dbo.TypesCommentairesADPS
/*
Clustered index scan (PK_TypesCommentairesADPS)
PkTypeCommentaireADP DescTypeCommentaireADP
-------------------- --------------------------------------------------
1 Update
2 Bug
3 Amélioration
(3 ligne(s) affectée(s))
*/
drop table TypesCommentairesADPS
go
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply