ORDER BY decreases performance by 40x?

I am astounded. I haven't read any where that adding a sort order to a query
would drastically increase read time for the same query.
This query performed on a table with 360,000 records:
SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
FROM [PLVWDIV_INV_SHORT]
ORDER BY [SEARCHKEY] DESC
Takes 40 seconds! While:
SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
FROM [PLVWDIV_INV_SHORT]
Takes 1 second. Wow.
For now we will query the server without the ORDER BY and do a sort in the
Client application.
It seems like adding an ORDER BY on a large table or view increases the read
time by an order of magnitude.
Removing DESC speeds up the query somewhat.
Any thoughts? Thanks in advance...Hi John
Do you have an index on ID?
What happens if you ORDER by the table column, rather than the column alias,
which is an expression ?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"John Kotuby" <johnk@.powerlist.com> wrote in message
news:ux1ET$K4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I am astounded. I haven't read any where that adding a sort order to a
>query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort in the
> Client application.
> It seems like adding an ORDER BY on a large table or view increases the
> read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
> Any thoughts? Thanks in advance...
>|||"John Kotuby" <johnk@.powerlist.com> wrote in message
news:ux1ET$K4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I am astounded. I haven't read any where that adding a sort order to a
>query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort in the
> Client application.
> It seems like adding an ORDER BY on a large table or view increases the
> read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
Take off the rtrim(), and is your ID column indexed in both directions? I
know that more ofther your index is ascending and we always want the max
value on top. So set your index to be descending instead.
HTH
__Stephen|||John Kotuby wrote:
> I am astounded. I haven't read any where that adding a sort order to
> a query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort
> in the Client application.
> It seems like adding an ORDER BY on a large table or view increases
> the read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
> Any thoughts? Thanks in advance...
Is there an index on SEARCHKEY? Have you looked at the execution plan to
make sure the index is being used?
The "TOP 1000 " causes the query to stop after 1000 rows are retrieved.
Using the ORDER BY forces the entire 360000 rows to be sorted before getting
the top 1000, This can be time consuming, especially when there is no index
to be used.
Your plan to to the sorting in the client has a drawback: you will have
different results doing it that way. Look at this set or data:
36
55
79
28
44
If you take the top 2 and then sort them in descending order, you get
55
36
If you sort them first and then take the top 2, you get
79
55
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||John Kotuby wrote:
> I am astounded. I haven't read any where that adding a sort order to
> a query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort
> in the Client application.
> It seems like adding an ORDER BY on a large table or view increases
> the read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
>
Oh, my bad, I did not notice you were sorting on the result of the
calculation. This can really slow things down as it prevents an index from
being used. See the difference if you take Kalen's advice and ORDER BY ID
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks for the responses guys...
All of them have merit. Bob, you hit the nail on the head when you reminded
me that the ORDER BY is performed on all 360,000 records before the TOP
1000 are selected. I feel like a real dope. Doing an ORDER BY on ID (which
is Indexed ascending) cut the time in half. I can see how Indexing
Descending will help even more.
What really fixed it for us is that we have an indexed INV_DATE field in the
view.
So I tried using
WHERE INV_DATE > DATEADD(DAY,-30,GETDATE()) and got it down to 2 seconds.
This gives me the last 30 days worth of invoices which will work in most
cases for a Browse. We supply other methods in the Client app to get to
specific records like exact Inv#, Date Range, etc in case the needed record
is not in the last 30 days.
Adding the WHERE clause immediately reduced the number of records that
needed to be sorted.
Once again, thank you all for your speedy replies.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:eDDoaKL4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> John Kotuby wrote:
> Oh, my bad, I did not notice you were sorting on the result of the
> calculation. This can really slow things down as it prevents an index from
> being used. See the difference if you take Kalen's advice and ORDER BY ID
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>