Using # tables in a Stored procedure - Sybase

Posted on 2008-06-10
Medium Priority
Last Modified: 2010-04-21
I am using # tables in a stored procedure and would like to know whether there will be any performance issues. Is it a good practice to create # tables in a stored procedure for performing some calculations rather than creating TEMP tables (TEMP tables physically exist and are created outside the stored procedure)?
 I have tested  a stored procedure using both # tables and TEMP tables and I see that the execution time of stored procedure is longer while using the # tables. I want to just confirm from the experts that its better to use TEMP tables and drop them at the end in the stored procedure rather than using # tables.

Question by:maddyforums
LVL 51

Expert Comment

by:Mark Wills
ID: 21753492
Well, some of that question depends on how the stored procedure is used. If dynamic and multiuser then the # temp table is better - it will be unique per user. However, if it is a batch / single thread then the TEMP table may afford you some performance improvements by modelling your table correctly...
LVL 19

Accepted Solution

grant300 earned 200 total points
ID: 21754883
#temp tables are always faster to populate than TEMP tables created before entering the stored procedure.  The best practice is to create and populate the #table at the same time using:
SELECT blahblahblah
   INTO #mytable

This works best because it is a minimally logged operation and will always be faster than doing INSERT/SELECT to a pre-existing table of any kind.

Your test of a single stored procedure can indicate a couple of different things but does NOT indicate a general rule that #tables are slower.  The first possibility is that the pre-defined table has a primary key or index on it that the #table does not have.  This can obviously help depending on the number of rows and what SQL statements the table is involved with.

The other possibility is that you need to tune the queries using the #table in the stored procedure.  There are some issues with #tables created inside a procedure as far as query optimization goes.  Since there are no statistics at compile time, it has to make some assumptions which may or may not be valid.  You can usually get around this issue with index hinting and perhaps using an occasional FORCE PLAN around the problem DML statement.

It is also possible to put indexes on #temp tables created in stored procedure.  This is kind of an advanced topic which would take up too much space here to talk about.

Bottom line, pretty much always use #temp tables and feel free to create them in your stored procedures.  You do have to plan on tuning things to get the best performance.  You can even pass #temp tables between procedures.

LVL 14

Expert Comment

by:Jan Franek
ID: 21757750
Just 2 comments:

1. I have seen situation under high multiuser load, when the bottleneck were locks on system tables in tempdb (creating #temp table probably requires lock on tempdb.dbo.sysobjects table). The solution was to use several tempdb databases (may not be available on very old versions of ASE).

2. I have seen quite impressive performance gain when tempdb was located on ramfs device (e.g. it was stored in RAM not on disk)

So my opinion is, that you probably have more options how to tweak your system for better performance when using #temp tables.

Author Closing Comment

ID: 31465795
Thank you Bill, for a detailed explanation.
LVL 19

Expert Comment

ID: 21780089

FYI, ASE v15 now supports row locking on system tables to present the issue you were seeing.

I have also seen great results with tempdb on tmpfs/ramfs.  The one thing you want to consider when doing that is separating and apportioning ASE cache appropriately.  Tempdb no longer needs as much cache in order to avoid I/Os allowing more cache to be allocated to the real database(s).


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

864 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