The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Server: Nested queries vs. temp tables

Just trying to get a general feel for what everybody likes.  Would you rather have a single SQL select statement with nested queries, or would you rather have sequential actions on the data storing the results of individual select statements in temp tables for use in later select statements?  What are the performance considerations?  What about debugging difficulty?  And, of course, what about style?

Personally, I am nested query kind of guy.  I think it comes from the composed function feel of learning Scheme as my first academic language.  I am used to dealing with lists and operations on lists, especially composed functions, so having nested structures is more natural.  I also contend that the nested queries are quicker to write, but that might just be me.
Joshua Volz Send private email
Thursday, June 22, 2006
 
 
I was told by dbas that this depends on the specs for the server this is running on. Nested queries, table variables, and other in-memory operations work best when the server has a lot of RAM. Physical temp tables work best when the server has a lot of free disk space. This is just a simple rule, of course there are other constraints after this first rule.

You made an interesting point in that one's preferences are dependent on what/how we learn a specific technology. I learned T-SQL in a company that threw hard drive space at problems. 90% of the queries written at that shop used physical temp tables.
slartibartfast Send private email
Thursday, June 22, 2006
 
 
I've never checked for any speed difference (though I have my suspicions).  But I'm a nested-query person.  When I see a stored procedure that creates several temp tables and then performs some logic I'm always tempted to rewrite the thing as 1 query.  It's easier for me to follow (80% of the time) and I find that it's the way I write my own SQL simply because it's the way I think (end product first).
Lou Send private email
Thursday, June 22, 2006
 
 
As an Oracle person I find it's pretty rare that we use temporary tables -- when you come across an application that does make extensive use of them the first thought is always, "Ah, migrated from SQL Server!".

Can anyone say why it is that SQL Server programmers seem to use them so frequently? Is it a legacy practice from earlier versions or a genuine need?
David Aldridge Send private email
Thursday, June 22, 2006
 
 
queries
anon
Thursday, June 22, 2006
 
 
I've seen cases in Sybase where the query optimizer just chokes and starts making stupid decisions if you add enough complexity to a single query.  At that point, but not before, I refactor to use temp tables.
Chaz Haws Send private email
Thursday, June 22, 2006
 
 
David, I will point out that I would go much farther out of my way to avoid temp tables in Oracle.  Because they take more work there.

I'm a big fan of many of Oracle's features, but maybe I could turn that question around on you:  If it were easy, as easy as "select * into #x from y" and then it gets cleaned up at the end of the stored proc automatically, then what are the reasons why we should avoid temp tables?

As I said, I only use them as a last resort when the query is not cooperating anyway.  Just because I find them to be, well, less relational.  More disk use was mentioned.  So I can think of a couple of tradeoffs... I just see no smoking gun that looks worth complete avoidance.  Can you help me see your perspective on it?
Chaz Haws Send private email
Thursday, June 22, 2006
 
 
There can be advantages for using temp tables. When a query has more than 5 or 6 joins, along with heavy-duty where clauses, I sometimes do the filtering first and store the key values in a temp table. Then I go back with the key values and get their child data. I test the query both ways--sub-query and temp table--but often times the query is more performant using the temp table.

It's situational: they're both tools and you have to use the right tool for the right job.
Chris McKenzie Send private email
Thursday, June 22, 2006
 
 
If performance is OK then do what you feel more comfortable with. When performance dips then you may have to change. I like subqueries simply because I can do it in one statement, but perormance can degrade a lot.
son of parnas
Thursday, June 22, 2006
 
 
>Can anyone say why it is that SQL Server programmers seem to use them so frequently? Is it a legacy practice from earlier versions or a genuine need?

Comparing Oracle to SQL Server: SS lacks the overhead when creating temp tables that O has. In converse, cursors in O lack the overhead that they have in SS.
Peter
Thursday, June 22, 2006
 
 
Whether you make a temp table or not, in a nested query which has a large amount of data, the optimizer will, in all likelihood make a temporary table itself.

In cases where the same subquery or close subset is used multiple times in a query, an design using an explicit temporary table can be faster, because the optimizer is not always as smart as a programmer.

In any case, the rule should always be: no premature optimization.  I would use a nested query until the profiler indicated otherwise.
Cade Roux Send private email
Thursday, June 22, 2006
 
 
I use temp tables alot simply because (IMHO) it makes debugging easier.  if rows are getting filtered out, I can look at the individual parts, and break the problem down.

I will admit you can easily go too far with temp tables, but then again, I have found its easier to go awry when putting too many queries together simply to avoid a temp table.
Another Anonymous Coward
Thursday, June 22, 2006
 
 
Oracle does indeed automatically generate "temporary segments" when making a very very very complex query and decides that it's better to for example precalc a join and then use it multiple times for the rest of the query. But it's automatic and only depends on having statistics always up-to-date (for example weekly). It's a fact that people not acoustomed to functional programming (such as scheme) will have more trouble making a big-query-that-does-it-in-one-go but that's an education problem mainly and not a technological problem
antonio vargas
Friday, June 23, 2006
 
 
>> I'm a big fan of many of Oracle's features, but maybe I could turn that question around on you:  If it were easy, as easy as "select * into #x from y" and then it gets cleaned up at the end of the stored proc automatically, then what are the reasons why we should avoid temp tables?

Well it requires extra i/o, for one thing. And disk space too. Breaking up a complex query by using temporary tables to materialize intermediate result sets also prevents some optimisations, such as pushing predicates into in-line views.

I disagree that temporary tables are tricky in Oracle -- one effective method is the subquery factoring clause (aka WITH clause) which optionally allows the programmer to force the temporary materialization of a result set as part of a query ...

WITH sq1 as (select /*+ materialize */ ...)
WITH sq2 as (select /*+ materialize */ ...)
select ...
from sq1, sq2, ...
where ...

As a general rule I'd try and avoid them unless absolutely necessary, and it's very rare that I do need them -- my observation is really that there seems to be a lower standard of "necessary" in the SQL Server world.
David Aldridge Send private email
Friday, June 23, 2006
 
 
Okay, that's slick.  I've used nested views before, but not nested materialized views.

I don't think we're too far apart on this, though, really - temp tables, in some form or another, are for when the optimizer chokes.  It's not that often, but sometimes they're tremendously faster.
Chaz Haws Send private email
Monday, June 26, 2006
 
 
>> but not nested materialized views

Careful on the use of terminology there -- a "Materialized View" in Oracle is a very different thing from the temporary materialization of an in-line view's result set.
David Aldridge Send private email
Tuesday, June 27, 2006
 
 
Point taken.  I had assumed a relationship because Oracle used the same term, but I would expect some differences.
Chaz Haws Send private email
Tuesday, June 27, 2006
 
 
I tend to use temp tables where performance isn't an issue - eg on a stored proc being run overnight as a batch process - it's no skin off anyone's nose if it takes 10 minutes or 15 (lots of data/processing) - but it does make it a lot easier to understand/debug what is going on. If it made the difference between 10 minutes and 60 minutes then I might start to worry - but speed isn't always an issue!

Oh - and also on any occasion where the nested part is used more than once!!!

Wednesday, June 28, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz