Using # tables in a Stored procedure - Sybase

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.

Who is Participating?
grant300Connect With a Mentor Commented:
#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.

Mark WillsTopic AdvisorCommented:
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...
Jan FranekCommented:
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.
maddyforumsAuthor Commented:
Thank you Bill, for a detailed explanation.

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).

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.