How to avoid Table scan from temp db

Question

How to avoid Table scan from temp db

Eswin

SSCrazy Eights

Points: 8320
More actions
February 11, 2009 at 7:54 am

#383920

hi experts
Can anyone please tell me how to avoid Table Scan from temp db .
Tanx 😀

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the same as any other table...after you create it, you'd need to create an index on the table, even if it is in tempdb. that's the only way to improve access and get an index seek or anything like that.

even then, the system will decide whether using an index is going to be more efficient than doing a table scan...if you select * from #tmp, regardless of an index, since it would need to get all columns, it might be a table scan anyway.

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!

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

Yep, it's like Lowell says. Most of the time temp tables are so small that even putting indexes on them and getting the index used in the execution plan doesn't really appreciably increase performance. It all depends of course.

"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

Eswin SSCrazy Eights Points: 8320 More actions · Answer 3

Tanx

I created indexes for my temp table but still the issue persists . table scan costs 100%.

Tanx 😀

Lowell SSC Guru Points: 323518 More actions · Answer 4

a table scan isn't necessarily evil....is the select very slow or anything?

how many rows are in the temp table?

what is the CREATE TABLE statement for the table?

is there a performance issue, or are you looking at the execution plan, and it says 100% table scan, and you just want to get rid of the table scan because you know index scan and index seeks are better?

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!

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 5

Hi,

You can check this code.

CREATE TABLE #Temp
(
TempID INT
)
GO
CREATE INDEX ID_Temp ON #Temp(TempID)
GO
SELECT * FROM #Temp
WHERE TempID <> 7
GO
DROP TABLE #Temp

Run this query with Execution Plan and Check..

Cheers!

Sandy.

--

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

Can you post the query and an execution plan? That would help. Also, can you show the STATISTICS IO and STATISTICS TIME for the query?

"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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

If you are returning large number of row sets then obviously table scan is better. Don't forget to post the query and the table structure.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Eswin (2/12/2009)
Tanx
I created indexes for my temp table but still the issue persists . table scan costs 100%.

The optimizer is pretty darn good. table scans can often be much less expensive than using an index and then doing a bookmark lookup to get to the data. You can see this for yourself by explicitly forcing the optimizer to use the index you think it should use. Then show the actual query plan in SSMS and set statistics IO on to see which is better - the optimizers plan or yours.

FROM mytable (INDEX=theindexIthinkshouldbeused)

Note that even if you DO get a less expensive plan, you REALLY REALLY REALLY need to avoid doing this stuff in production!!!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service