Slow running queries

Question

Post reply

Slow running queries

b_boy

SSCertifiable

Points: 5145
More actions
August 19, 2008 at 4:20 am

#196521

Hello
I have several join queries that seems to be running slow and spending at time 5min to fully excute. is there a way i can improve this,?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

thasteve SSCrazy Points: 2707 More actions · Answer 1

query, table structure, execution plan a little more info would help

b_boy SSCertifiable Points: 5145 More actions · Answer 2

When you say table structure, do you mean the create table query?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yes, and the index creation scripts would also be useful please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

thasteve SSCrazy Points: 2707 More actions · Answer 4

it does not have to be the complete table scripts if you can provide the query, execution plan and any possible indexe on the tables you are accessing with the joins or partitioning if any use just some basic information to be able to help you out there

b_boy SSCertifiable Points: 5145 More actions · Answer 5

the execution plan is graphical, how will i be able to send that to you?

b_boy SSCertifiable Points: 5145 More actions · Answer 6

Not able to attached execution query plans, file type not permitted

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 7

Do one thing, SET SHOWPLAN_TEXT ON and then execute the query. SQL will give you the execution plan in TEXT. Copy and paste it here for further support.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

b_boy (8/19/2008)
Not able to attached execution query plans, file type not permitted

if you're using SQL 2005, right click the exec plan, choose save as. Save the file as a .sqlplan file. Zip the file and attach

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

b_boy SSCertifiable Points: 5145 More actions · Answer 9

b_boy

SSCertifiable

Points: 5145

August 19, 2008 at 5:25 am

#859917

Thanks Gail

Was able to attache it as a zip file

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Great. Can you post the table's create statement, the index definitions and the query itself please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 11

First, you don't have a where clause, so you're returing all the data from all the tables (not counting the DISTINCT statement, which, we'll get to). This will pretty much force table scans and index scans, such as you see. Is this part of some ETL process? If not, why would you need to pass 17000 rows to the end user? That alone is going to cause the query to run slow. You're running the DISTINCT because you're getting duplicate rows, right? That suggests either your joins are incorrect, you need a WHERE clause (again) or possibly the structure is off.

As long as you're returning everything, you're going to be dealing with table scans. You might see a performance boost if you put a query hint on to force parallelism to only one processor, but it might actually slow it down.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning