Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: 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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
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…
Suggested Courses

636 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