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.
>

Oracle Linked Server poor performance

I am currently querying two databases - One in SQL Server and one Oracle, to find records which are in one but not the other (essentially a reconcilliation) this is working fine in MS Access, using 2 passthrough queries to return the results of the 2 databases, then another query to find the data in one but not the other.

I decided to try the linked server approach as I thought this would give me enhanced performance - but strangely enough when I query the oracle database from MS Access the query takes about 22 seconds - whereas when I query the linked server from SQL Server the same query takes about 55 seconds.

The only difernce I can see is the passthrough query in MS Access is connecting using ODBC, an the linked server is connecting using Microsoft OLE DB Provider for Oracle.

Any ideas would be appreciated

You don't specify how you are querying the Oracle database but I would guess you are using a 4 part name query against the Oracle data source. Try changing your query to use OpenQuery instead. That is also closer to how a pass-through query in Access would execute.

-Sue

|||

Thanks I will give it a go. Is using th 4 part name a bad way to query?

<edit> WOW that is much better thanks very much for your help </edit>

|||

In and of itself, the four part name isn't necessarily a bad thing but you can send the query over to the server to be executed on the remote data source and force more processing, data filtering on the server. So you can push more processing off to the server. Some people have a hard time with Openquery as the flip side is that you aren't sending transact sql - you need to send the sql syntax used by the remote data source, the other database platform. I've found with Oracle data sources, it's generally better to just use Openquery.

-Sue

|||Sue - thanks very much you have been very helpful. My query now runs in under 15 seconds now.

Oracle linked Server Performance

Hello,
we have an Oracle Linked Server (8i) running linked from SQL Server 2000
SP4.
The performance is ok when selecting data from the sql server, but poor when
trying to insert data into the oracle database via linked server.
Even simple inserts (
insert into lserver..xxx.tablename
select * from sqlservertable
)
take pretty long (only a few thousand inserts per hour).
As we want to insert millions of datarows, this is not acceptable.
Any ideas on how to speed this up ?
if it does not work faster over the linked server, we'll need to do flat
file import / export ...
Thank you,
Markus.
Linked servers were never really intended for that much activity. A DTS
solution is probably more appropriate for something like that.
Andrew J. Kelly SQL MVP
"Markus Donath" <crourvaick@.kriocoucke.mailexpire.com> wrote in message
news:%23UCbEoh1FHA.1568@.TK2MSFTNGP10.phx.gbl...
> Hello,
> we have an Oracle Linked Server (8i) running linked from SQL Server 2000
> SP4.
> The performance is ok when selecting data from the sql server, but poor
> when trying to insert data into the oracle database via linked server.
> Even simple inserts (
> insert into lserver..xxx.tablename
> select * from sqlservertable
> )
> take pretty long (only a few thousand inserts per hour).
> As we want to insert millions of datarows, this is not acceptable.
> Any ideas on how to speed this up ?
> if it does not work faster over the linked server, we'll need to do flat
> file import / export ...
> Thank you,
> Markus.
>

Oracle linked Server Performance

Hello,
we have an Oracle Linked Server (8i) running linked from SQL Server 2000
SP4.
The performance is ok when selecting data from the sql server, but poor when
trying to insert data into the oracle database via linked server.
Even simple inserts (
insert into lserver..xxx.tablename
select * from sqlservertable
)
take pretty long (only a few thousand inserts per hour).
As we want to insert millions of datarows, this is not acceptable.
Any ideas on how to speed this up ?
if it does not work faster over the linked server, we'll need to do flat
file import / export ...
Thank you,
Markus.Linked servers were never really intended for that much activity. A DTS
solution is probably more appropriate for something like that.
Andrew J. Kelly SQL MVP
"Markus Donath" <crourvaick@.kriocoucke.mailexpire.com> wrote in message
news:%23UCbEoh1FHA.1568@.TK2MSFTNGP10.phx.gbl...
> Hello,
> we have an Oracle Linked Server (8i) running linked from SQL Server 2000
> SP4.
> The performance is ok when selecting data from the sql server, but poor
> when trying to insert data into the oracle database via linked server.
> Even simple inserts (
> insert into lserver..xxx.tablename
> select * from sqlservertable
> )
> take pretty long (only a few thousand inserts per hour).
> As we want to insert millions of datarows, this is not acceptable.
> Any ideas on how to speed this up ?
> if it does not work faster over the linked server, we'll need to do flat
> file import / export ...
> Thank you,
> Markus.
>

Oracle link performance is horible

I have a simple select statement that I execute against an Oracle database i
n
PL/SQL and the results are, for the purposes of this discuaaion,
instantaneous.
I have that Database linked to my SQL server. If I perform that same select
statement to the oracle server:
SELECT X,Y,Z FROM SERVER..SCHEMA.TABLE
it takes 1 minute 14 seconds.
What's up with that? Any ideas, folks?select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE')
"David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
news:0CA663E4-0A8F-435D-9087-861D6B5C4580@.microsoft.com...
> I have a simple select statement that I execute against an Oracle database
in
> PL/SQL and the results are, for the purposes of this discuaaion,
> instantaneous.
> I have that Database linked to my SQL server. If I perform that same
select
> statement to the oracle server:
> SELECT X,Y,Z FROM SERVER..SCHEMA.TABLE
> it takes 1 minute 14 seconds.
> What's up with that? Any ideas, folks?|||That helped a lot. Thanks.
However. I can't use a parameterized query...can I?
DECLARE @.someValue varchar(30)
select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE WHERE
x='+@.someValue)
"CK" wrote:

> select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE')
> "David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
> news:0CA663E4-0A8F-435D-9087-861D6B5C4580@.microsoft.com...
> in
> select
>
>|||You can do it, but you'd need to make the statement dynamic sql. I don't
have an oracle connection to test it. I've done this before and you really
have to play with the quoting to get it right...but it will work.
Something like this:
DECLARE @.someValue varchar(30)
declare @.cmd varchar(1000)
set @.cmd = 'select X,Y,Z FROM openquery(oraserver,'''select X,Y,Z from
oraTABLE WHERE x='''+@.someValue)''
exec (@.cmd)
"David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
news:13E1AA87-0199-4CFA-BCA5-DB09D0945321@.microsoft.com...
> That helped a lot. Thanks.
> However. I can't use a parameterized query...can I?
> DECLARE @.someValue varchar(30)
> select X,Y,Z FROM openquery(oraserver,'select X,Y,Z from oraTABLE WHERE
> x='+@.someValue)
>
>
> "CK" wrote:
>
database