maddyforums
asked on
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.
Thanks
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.
Thanks
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thank you Bill, for a detailed explanation.
Jan,
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).
Regards,
Bill
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).
Regards,
Bill