Fast "Select" query slows way down when adding an "Into #temp" statement

Question

Fast "Select" query slows way down when adding an "Into #temp" statement

Libby

Ten Centuries

Points: 1175
More actions
July 25, 2011 at 9:55 am

#244801

I have a select query that contains a couple of sub-selects - but only takes 2-3 seconds to run resulting in 2,568 rows of data. However, when I add an "into #temp", it runs and runs. I finally cancel it because I don't think it should take this long. I tried creating a table and doing an insert to see if that would help, but it didn't.
Does anyone have any ideas on what could be causing my problem?
Thanks

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

There could be several things. Without anything to go on it is impossible to tell. We would need to see your select, ddl for the underlying tables and some sample data (insert statements) with desired result based on your sample data. Please see the link in my signature for best practices on posting data and questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 2

Have you compared execution plans between the regular SELECT and the SELECT...INTO queries?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Could be blocking for that specific run.

Could also be that tempdb is under a crapload of stress.

Libby Ten Centuries Points: 1175 More actions · Answer 4

Thanks for the ideas. I think you may have been on to something with the tempdb. All of a sudden without me changing anything, my script it running quick with the "into #temp" statement included now. Not sure what was going on.

Thanks again

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Hard to tell without being able to debug your server...