SELECTING RECORDS TAKING TIME

Question

SELECTING RECORDS TAKING TIME

Naina_11

SSC Eights!

Points: 921
More actions
June 18, 2015 at 6:40 am

#301767

I have this simple select query in a proc and the query is taking approx. 30 seconds to return approx 35000 records.
I am a beginner. 🙁
Can you please suggest some other way out.
The select statement is similar to this -
SELECT column1,column2, ..................
FROM tab1
LEFT OUTER JOIN tab2 with (INDEX(qwerty))
ON tab1.abc = tab2.def
ORDER BY tab1.xyz

Viewing 6 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

nidhi.mahajan (6/18/2015)
I have this simple select query in a proc and the query is taking approx. 30 seconds to return approx 35000 records.
I am a beginner. 🙁
Can you please suggest some other way out.
The select statement is similar to this -
SELECT column1,column2, ..................
FROM tab1
LEFT OUTER JOIN tab2 with (INDEX(qwerty))
ON tab1.abc = tab2.def
ORDER BY tab1.xyz

Please post the actual execution plan and we can offer suggestions. It's likely that you are joining on columns which are not indexed.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

First thing you can do is remove the index hint. Then test the performance again. If it's still unacceptable, post the table definitions, index definitions and execution plan.

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

Naina_11 SSC Eights! Points: 921 More actions · Answer 3

Hey..

Thanks.

removing the index helped by bringing down the time to 6 seconds.

Can you please help me with why it might have been used in first place. (possible reason we use index this way!!!!)

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

nidhi.naina (6/18/2015)
Hey..
Thanks.
removing the index helped by bringing down the time to 6 seconds.
Can you please help me with why it might have been used in first place. (possible reason we use index this way!!!!)

The index was not removed. Only the 'HINT' that the optimiser uses the index to execute the query was removed.

Developers sometimes think that they can do a better job than SQL Server when determining how a query should be executed. This one of those cases, I suspect. The developer thought that index qwerty was better than the one which SQL chose, so the developer forced SQL to choose qwerty. Perhaps it worked for a while, but it seems that SQL now does it better.

Unless you really know what you're doing, I recommend not overriding the optimiser's choices like this.

Instead, post performance problems on this forum and some very knowledgeable people are likely to help you resolve them.

Naina_11 SSC Eights! Points: 921 More actions · Answer 5

Naina_11

SSC Eights!

Points: 921

June 19, 2015 at 12:24 am

#1806589

Thanks a lot guys........

real helpful