Solved

Temp table vs Database table for performance?

Posted on 2010-11-09
12
175 Views
Last Modified: 2014-05-13
I have a procedure with many steps and lots of complex queries that I am trying to make run faster.

Calculations are made over 100 years of data. The math performed on any particular year's data is based in part, on the results of calculations from the previous year (so it's sequencial).

Since there are several different scenarios run, the total number of rows of data is around 12,000.  These data rows are summed and averaged into a few records which are used in a report.  The 12,000 records are no longer used after the summary tables are populated.

So, my question is... would I gain a *significant* amount of improved performance if I used Temporary tables for the 12,000 records of data..   Currently, I am using regular database tables.

I am thinking this may be helpful, but before doing all the work, want to see what experts say.  It is harder to debug with temp tables...    I also use WITH CTE... to sum sequentially, any restrictions on using this with temp tables?



0
Comment
Question by:gdemaria
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
"temp" as in # or @ ?
0
 
LVL 10

Assisted Solution

by:Humpdy
Humpdy earned 250 total points
Comment Utility
You're better off using db tables,
but this sounds like a classic case of where you need to use SQL Partitioning.

Are you running Enterprise edition ?

If you can't do partitioning because you're on standard edition, then I would look into archival, especially if data is not used after population.
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
For 12k, yes it would be significant to use #tmp with clustered key and indexes.
100 or so is when to stop using @ in memory tables.

Under rate circumstances (only) should you use regular tables for such a reporting function - they pollute the log and get involved unnecessarily log shipping, remote sync etc.
0
 
LVL 10

Expert Comment

by:Humpdy
Comment Utility
CyberKiwi,
Are you saying that using #tmp is going to be quicker than using his existing db tables which he has ?
0
 
LVL 39

Author Comment

by:gdemaria
Comment Utility
> "temp" as in # or @ ?

Let me rephrase and say, what do you think would be the best approach..

> Are you running Enterprise edition ?

No, standard

Could you briefly explain how/why SQL Partitioning or Archival would help in this situation?

> they pollute the log and get involved unnecessarily log shipping, remote sync etc.

I can see this.  It actually takes longer to delete the old records than any other single function

So you recommend #temporary tables?   No logging would be done with those?
0
 
LVL 3

Expert Comment

by:Birdbuster
Comment Utility
Hi there, if you know that 12,000 rows is never going to grow larger then lets say over 50,000 rows then I would be using a table variable @tbl as they are faster then temp tables #tbl when the row count is not too large.
Another approach might be to create a new database table and a SQL Server Job to populate this new table with calculated data that the report uses, so that you only have to the run the select to retrun the rows and not re-do calculation everytime.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
Comment Utility
> So you recommend #temporary tables?   No logging would be done with those?
There will be logging, but only in the tempdb.ldf file not your regular db.  tempdb uses simple logging and is lighter than bulk/full logging.  tempdb is not involved log shipping or replication.

This is how I read the question:
The report is crunching all data into about 12,000 records, which are further summarized into a few records for a report.
The temp storage is related to those 12,000 records.

Table variables can work, as long as you add a clustered index in the definition, e.g.

declare @t table (id int clustered, col1 varchar(10))

Table variables are backed by physical disk when large amounts of data are used, but other than the unique/clustered key, you cannot create indexes on them which is a big minus if the table is reused multiple times and indexes can help.
0
 
LVL 39

Author Comment

by:gdemaria
Comment Utility
I should add one more thing to the mix.

Mutliple users can run this report on different data at the same time or as often as they like.  

This is not a one-time run every twenty four hours, it is an on-demand, while-you-wait complex report of information selected by each user that needs to run as quickly as possible and display the results.

Does that change anyone's recommended solution?
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
You could start looking at SSAS cubes and some daily aggregation of the data, with some recalculation of the active portion to merge into pre-aggregated data.  It would negate the requirement to keep scanning all available data for an entire year.

Having said that, as you currently have it, it makes it even less desirable to use normal in-db tables, since you have to manage multi-user access (you could user spid as a key to partition that table).  The #/@ tables are session specific and won't have multi-user issues, but the select queries themselves should probably be using a session set to

SET transaction isolation level read uncommitted

to minimize locks on the tables.

That's all periphery, I still recommend using #temp tables and indexing the tables for fast access.
0
 
LVL 3

Expert Comment

by:Birdbuster
Comment Utility
If you really just trying to performance turn a sproc then you should be using # temp tables and adding teh appropiate nonClustered Indexes to that table and possibly oether tables that are used in the query. Look at the Joins you are using and create indexes based of those usually. Should be including the actual execution plan, and also use sql profiler to see your read and writes and exec time of the sproc.
0
 
LVL 39

Author Comment

by:gdemaria
Comment Utility
> You could start looking at SSAS cubes and some daily aggregation of the data, with some recalculation of the active portion to merge into pre-aggregated data.  It would negate the requirement to keep scanning all available data for an entire year.

The characteristics of the data would not allow for this type of processing as the combinations are infinite.   It has to be done on-demand once the user enters a series of criteria and parameters.

> Having said that, as you currently have it, it makes it even less desirable to use normal in-db tables, since you have to manage multi-user access (you could user spid as a key to partition that table).  The #/@ tables are session specific and won't have multi-user issues, but the select queries themselves should probably be using a session set to

This sounds good.  I have a "Report_ID" which will partition the table by user's session (the user has to be logged in)

> then you should be using # temp tables and adding teh appropiate nonClustered Indexes

Sounds like everyone agrees #temp tables.


Thanks very much!



0
 
LVL 39

Author Closing Comment

by:gdemaria
Comment Utility
Sorry, closing out some old questions
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now