Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

derived vs temp table performance

Posted on 2011-02-11
5
Medium Priority
?
937 Views
Last Modified: 2012-06-21
select col1,col2,col3 etc.. into temp from tabel1 where x=1 and y=2
select * from temp, table2 where temp.x = table2.x and etc..
drop table temp
vs

select * from
(select col1,col2,col3 etc..  from tabel1 where x=1 and y=2 ) table1, table2
where table1.x = table2.x and etc..

lets say both tables have large amount of rows.

1) which is better performance wise?  and best practice

2) assume we put an index on table2.x and #temp.x,  will the performance change since you cant put an index on derived tables (table1).    I guess the question really is creating an index + do a select on the index  vs select on an non-index column for large tables.

3) What if instead of just two tables, now you have 10 tables you need to join.

thanks
0
Comment
Question by:gagaliya
  • 2
  • 2
5 Comments
 
LVL 27

Expert Comment

by:wilcoxon
ID: 34873112
Where I work, we hardly ever use derived tables.  I'm pretty sure nobody has looked at them in a long time so the practice probably started a long time ago when Sybase had performance issues or bugs (or possibly didn't support) derived tables.

With temp tables used the way you are above, the times when I've compared performance, I've never noticed any performance difference with adding an index or not.  That said, I almost always add an index anyway (it takes very little time to add an index on a temp table).
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 34873757
I think many factors may effect overall performance at the end. It is better to test both and see which way you would get a better performance. ASE performance and tuning guide suggests using derived tables as I have quoted below. However, a temp table with an index might improve overall performance.

Also it depends on how large your tables are. If you have tens of millions of rows, you need to explore more options like parallelism and do more testing.

"Use of SQL derived tables

"Queries expressed as a single SQL statement exploit the query processor better than queries expressed in two or more SQL statements. SQL-derived tables enable you to express, in a single step, what might otherwise require several SQL statements and temporary tables, especially where intermediate aggregate results must be stored. For example:

select dt_1.* from
   (select sum(total_sales)
        from titles_west group by total_sales)
               dt_1(sales_sum),
   (select sum(total_sales)
         from titles_east group by total_sales)
               dt_2(sales_sum)
where dt_1.sales_sum = dt_2.sales_sum

"Here, aggregate results are obtained from the SQL derived tables dt_1 and dt_2, and a join is computed between the two SQL derived tables. Everything is accomplished in a single SQL statement.

"For more information, see Chapter 9, “SQL Derived Tables,” in the Transact-SQL User's Guide."
0
 
LVL 1

Author Comment

by:gagaliya
ID: 34874448
we have over 300 tables, many of them have over 10 million rows with the large ones at 700 million +.

the project i am working on needs to dynamically generate the SQL that joins x number of random tables based on user selection. So it's very difficult to benchmark performance as the table set is dynamic in nature.

Just trying to figure out the best way to approach such a problem.  the legacy code uses temp tables but i am trying to explore better options and best practice.
0
 
LVL 13

Accepted Solution

by:
alpmoon earned 2000 total points
ID: 34874918
If you have proper indexes, derived tables should give the same performance without creating temp tables. ASE creates work tables instead of explicit temp tables and they are more efficient.

Does legacy code create any index?

If the volume is that big, you should check whether parallelism is enabled and tuned properly or not.
0
 
LVL 1

Author Closing Comment

by:gagaliya
ID: 34883176
thank you!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will be showing you how to subnet the easiest way possible for IPv4 (Internet Protocol version 4). This article does not cover IPv6. Keep in mind that subnetting requires lots of practice and time.
Exchange administrators are always vigilant about Exchange crashes and disasters that are possible any time. It is quite essential to identify the symptoms of a possible Exchange issue and be prepared with a proper recovery plan. There are multiple…
Loops Section Overview
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question