Complex Select Query Vs Creating a Temp Table and then inserting values in temp table and then selecting it

Question

I was just wondering which approach is better.

Creating a complex select query with many CASE WHEN THEN ... ELSE ... END statements or sub queries in select clause.
Create a temp table and then insert and update it. Finally select * from @tempTable

This question is only for complex queries not for simple joins or just when some small info has to be picked from other table.

Chris S · Accepted Answer · 2010-08-18 11:47:16Z

2

For ease and speed of development, 2 in my experience.

For performance - it depends on the number of rows and tables you're joining and retrieving back. In memory temporary tables (as it looks like you're suggesting) have an overhead, which is obviously the memory they're using.

I'd stick to number 2 as mentioned, unless you're working with Walmart sized data then worry about performance if it becomes a problem - "Hardware is cheap, writing software is expensive"

edited Aug 18, 2010 at 11:47

answered Aug 12, 2010 at 9:08

Chris S

65.6k53 gold badges225 silver badges240 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Complex Select Query Vs Creating a Temp Table and then inserting values in temp table and then selecting it

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related