Temp table or permanent tables?

For my company I am redesigning some stored procedures. The original p开发者_StackOverflowrocedures are using lots of permanent tables which are filled during the execution of procedure and at the end, the values are deleted. The number of rows can extend from 100 to 50,000 rows for calculation of aggregations.

My question is, will there be severe performance issues if I replace those tables with temp tables ? Is it feasible to use temp tables ?

It depends on how often your using them, how long the processing takes, and if you are concurrently accessing data from the tables while writing.

If you use a temp table, it won't be sitting around waiting for indexing and caching while it's not in use. So it should save an ever so slight bit of resources there. However, you will incur overhead with the temp tables (i.e. creating and destroying).

I would re-examine how your queries function in the procedures and consider employing more in procedure CURSOR operations instead of loading everything into tables and deleting them.

However, databases are for storing information and retrieving information. I would shy away from using permanent tables for routine temp work and stick with the temp tables.

The overall performance shouldn't have any effect with the use case you specified in your question.

Hope this helps,

Jeffrey Kevin Pry

Yes its certainly feasible, you may want to check to see if the permanent tables have any indexing on them to speed up joins and so on.

I agree with Jeffrey. It always depends. Since you're using Sql Server 2008 you might have a look at table variables.
They should be lighter than TEMP tables.

I define a User Defined Function which returns a table variable like this:

    CREATE FUNCTION .ufd_GetUsers ( @UserCode INT )
    RETURNS @UsersTemp TABLE
        (
        UserCode INT NOT NULL,
        RoleCode INT NOT NULL
        )
    AS
    BEGIN
        INSERT @RolesTemp
            SELECT     
                dbo.UsersRoles.Code, Roles.Code
            FROM    
                dbo.UsersRoles 
                INNER JOIN
                                    dbo.UsersRolesRelations ON dbo.UsersRoles.Code = dbo.UsersRolesRelations.UserCode 
                INNER JOIN
                                    dbo.UsersRoles Roles ON dbo.UsersRolesRelations.RoleCode = Roles.Code
            WHERE dbo.UsersRoles.Code = @UserCode

        INSERT @UsersTemp VALUES(@UserCode, @UserCode)



RETURN
END

A big question is, can more then one person run one of these stored procedures at a time? I regularly see these kind of tables carried over from old single user databases (or from programmers who couldn't do subqueries or much of anything beyond SELECT * FROM). What happens if more then one user tries to run the same procedure, what happens if it crashes midway through - does the table get cleaned up? With temp tables or table variables you have the ability to properly scope the table to just the current connection.

Definitely use a temporary table, especially since you've alluded to the fact that its purpose is to assist with calculations and aggregates. If you used a table inside one of your database's schemas all that work is going to be logged - written, backed up, and so on. Using a temporary table eliminates that overhead for data that in the end you probably don't care about.

You actually might save some time from the fact that you can drop the temp tables at the end instead of deleting rows (you said you had multiple users so you have to delete rather than truncate). Deleting is a logged operation and can add considerable time to the process. If the permanent tables are indexed, then create the temp tables and index them as well. I would bet you would see an increase in performance usless your temp db is close to out of space.

Table variables also might work but they can't be indexed and they are generally only faster for smaller datasets. So you might try a combination of temp tables for the things taht will be large enough to benfit form indexing and table varaibles for the smaller items.

An advatage of using temp tables and table variables is that you guarantee that one users process won;t interfer with another user's process. You say they currently havea way to identify which records but all it takes is one bug being introduced to break that when using permanent tables. Permanent table for temporary processing are a very risky choice. Temp tables and table variabels can never see the data from someone else's process and thus are far safer as a choice.

Table variables are normally the way to go. SQL2K and below can have significant performance bottlenecks if there are many temp tables being manipulated - the issue is the blocking DDL on the system tables.

Sql2005 is better, but table vars avoid the whole issue by not using those system tables at all, so can perform without inter-user locking issues (except those involved with the source data).

The issue is then that table vars only persist within scope, so if there is genuinuely a large amount of data that needs to be processed repeatedly & needs to be persisted over a (relatively) long duration then 'static' work tables may actually be faster - it does need a user key of some sort & regular cleaning. A last resort really.