Tuning a plain select statement

Question

Post reply

Tuning a plain select statement

sql_butterfly

Ten Centuries

Points: 1264
More actions
February 24, 2011 at 4:27 am

#388928

We have one procedure where after join some tables the records are put into one temporary table (this step is processed fast). Now at the end when we are doing a select statement from that temp table to show data on our frontend, it takes around 10 seconds for mere 1000 records. Can a plain select statement be made faster ?

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

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

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 1

vishal.gamji

SSCertifiable

Points: 6126

February 24, 2011 at 7:56 am

#1290999

Can u send your queries pls.

Lowell SSC Guru Points: 323518 More actions · Answer 2

sql_butterfly (2/24/2011)
We have one procedure where after join some tables the records are put into one temporary table (this step is processed fast). Now at the end when we are doing a select statement from that temp table to show data on our frontend, it takes around 10 seconds for mere 1000 records. Can a plain select statement be made faster ?

the devil is in the details...

some hints while we are waiting for more info:

@temp table variables can cause slow results if they contain a lot of records...my rule of thumb is more than 500 records should be in a #temp table and not a table variable

the WHERE statement is where most of the tuning decisions are made. you might even need to index the temp table, ore remove the temp table and use a CTE to get the final results to take advantage of indexes ont he original table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChazMan SSCrazy Points: 2259 More actions · Answer 3

I would ask why you need to use a temp table at all. The CTE option described would likely help (but again we need the details.

--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Are you 100% sure the slow part of the process is the select statement? Also, if it's just a straight select, no joins, no where clause, then you're dependent on scanning the temp table and there is no real way to improve a scan.

But as everyone else has said, hard to know without knowing what we're talking about. Execution plans would be good too.

"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

sql_butterfly Ten Centuries Points: 1264 More actions · Answer 5

Sorry Folks, After investigations we found it was because of rendering at frontend and not backend. Query is doing good.