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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 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