Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Very slow ORDER BY

Posted on 2006-06-12
13
250 Views
Last Modified: 2008-01-09
Hello,

When I tried to ORDER BY anything in one of my tables, it takes a lot of time to do it even for not too many records (about a thousand).

I have two tables, say Contracts and Names table, the query is:

SELECT
contracts.key,
contractor_names.Name AS contractor_name,
client_names.Name AS client_name

FROM
Contracts as contracts      
JOIN
Names as contractor_names            
      on contracts.Contractor_Name_Key = contractor_names.Name_Key
JOIN
Names as client_names            
      on contracts.Client_Name_Key = client_names.Name_Key
      
order by contracts.key

Everything works fine UNLESS I try to order this query by anything... In which case it does it, but extremely slow, like sorting 100 rows for a minute. I presume it's because of my double join, so can it be fixed somehow?

The query and all names have been changed as originally it's a bit bigger, so pay no attention to that.

Thanks,
Yurich
0
Comment
Question by:Yurich
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 16889251
how much RAM does your server have?
how much RAM does your sql server take from that?
0
 
LVL 21

Author Comment

by:Yurich
ID: 16889367
frankly, I got no idea as it's a remote server and I got no rights to browse it. but for other querries it can take  10-20 seconds to fetch and sort a million or two records.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16889399
How do you connect to the server (which tools)?
can you get a execution plan for that query?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 6

Assisted Solution

by:davbouchard
davbouchard earned 125 total points
ID: 16889456
Is there a index on contracts.key column?
0
 
LVL 21

Author Comment

by:Yurich
ID: 16889522
I connect through Query Analyzer, if that's what you're asking about and I'm not sure what teh execution plan is...

Actually, I'm not trying to sort by contracts.key in my original query, I trried to do it by dates, but later I tried by this key and by then by the names - the result is the same.

If I go to the contracts table properties (and that's the only place I have priveledges to go), I can see that there's a tick under ID, but no tick under key for contracts.key

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16889544
>I connect through Query Analyzer, if that's what you're asking about and I'm not sure what teh execution plan is...

look in the view menu of the QA, there is a option "Display Execution Plan".
Activate it, run the query, and you will get an additional tab with the execution plan (graphical)
tell us what you get there.
0
 
LVL 21

Author Comment

by:Yurich
ID: 16889862
ok, found it.

when I add order by, i have table spool/lazy spool operation to appear, which generated by table scan, connects to Nested Loops/Inner Joins, costs 69% and generates 11 mil rows... that must be my problem, but what do we do about it?

thanks,
yurich
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 125 total points
ID: 16891101
Yurich,
> which generated by table scan
Now make sure that you have indices on these columns

 contracts.Contractor_Name_Key , contractor_names.Name_Key  
contracts.Client_Name_Key , client_names.Name_Key
contracts.key


0
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 125 total points
ID: 16894842
Create index on the field you are using in your order by .
0
 
LVL 21

Author Comment

by:Yurich
ID: 16898285
guys, i cannot change the table structure
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16900160
We are not asking you to change the schema of the table but if you can you have to create index on the table

create nonclustured index ix_index1 on contracts (key)

0
 
LVL 21

Author Comment

by:Yurich
ID: 16930988
thanks,

I had a look at the sp that creates this table and actually they do create indeces for this fields and for other "key" fields.

I probably will check if there're indeces for other fields.

regs,
yurich
0
 
LVL 21

Author Comment

by:Yurich
ID: 17034250
i haven't sorted this problem, but I just did for this query all sorting right in the report (and that was for a report), and that was just fine. I had all indeces for all entities that I had to order by, but it must be something else. I found that in some cases (in the same database) joining table on itself and then to something else can cause the same problem with no order by at all...

anyway, thanks again,
yurich
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL query and VBA 5 46
SQL Query 2 34
Help With SQL Query 9 31
interpreting data from function COLUMNS_UPDATED 2 17
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question