Huge performance issue

[AndyMoss]

Okay, here's my problem.

I have a massivley complicated query, that creates and populates two temp tables, then unions them - I want the output.

This is the easy bit - it takes about three minutes to crunch all this lot.

The problem arises when I either:

SELECT * FROM #Temp1

UNION ALL

SELECT * FROM #Temp2

or I

TRUNCATE tblOutput

INSERT INTO tlbOutput --all records must be current

SELECT * FROM #Temp1

UNION ALL

SELECT * FROM #Temp2

SELECT * FROM tblOutput

It takes over five minutes to bring back 50,000 rows.

I have created a key value on tblOutput, and have this as my PK_Index, but as I'm truncating this table each time, is this worthwhile? Is it worth having any other (clustered?) indexes on this table (there are no more single unique values, but combinations of fields are unique)? Is there any way I can get the output back faster?

Many thanks.

Andy Moss

 

[tlbroadbent]

If all you are doing is inserting into and selecting from tbloutput, the Primary Key will actiually slow the process. The only advantage tis that in curent versions of SQL wil return the result set in the order of the clustered index. You'd be better off sorting the output woith an order by clause. This will avoid the overhead of the index.

Are you running the query in SQL Query Analyzer? If so, are you returning the result to a grid or to the text window? Returning to a test window is very slow. Try returning to a grid. If you are already returning to a grid, turn on statistics IO and TIME to see what is causing the delay. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[AndyMoss]

Could this be a network issue as opposed to a SQL performance issue?

I ask because I did some more poking around, and the same SELECT * on my old (local) Prod server returned about the same rowcount in 45 seconds. We recently migrated our live db to a centralized server shop that is remote - T1 connection, but unlikely to be dedicated unless I run the query at 0400hrs!!

Any thoughts? Anyone else seen similar behaviour?

 

[SQLSister]

Oh yeah, remote connections can be exactly the problem. In one of my previous jobs we were accessing a database in our hq in Lexington KY from Norfolk, Va and found that we were only getting a small portion of the t1 line we were paying for. The phone company had oversubscribed the line.

 

[AndyMoss]

Our DBA (based at the same physical location as the box) ran the same query via a PCAnywhere connection, and it still took over 3½ minutes....

This is a new server - any DBA's out there have ideas about what setting may need 'tweaking'?

I don't have sa rights, so can't get to most options/properties settings, but my DBA is pretty open to suggestions from a code-head like me!!