Using # tables in a Stored procedure - Sybase

Posted on 2008-06-10
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 50 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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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