July 6, 2004 at 5:04 pm
Hello,
I was wondering if anyone had some suggestions on how I can accommodate a temp table used for transaction processing (Quantity Adjustments) in a multi user system. I am currently utilizing a single table for this process which when stress tested becomes unstable. I basically create the table run the SQL compare the results if they are different I update the source then destroy the table. I was thinking about creating the table using a session ID and when the session was dropped drop the table but this seems to be less then efficient.
Any help would be appreciated.
Bob R.
July 6, 2004 at 5:51 pm
Hi Robert,
Local Temp Tables are session aware. So If each user has a unique session then it shouldn't be a problem. However, If you are using COM objects in the middle Tier, then you'll likely have to define Component Object ROLES and Security so that the object makes its connection to SQL Server with in the context of the caller.
I've actually done what you are proposing, and it does work (and it is a pain) but that was before I understood COM + security.
Hope this is on point,
Pete
July 8, 2004 at 6:55 am
Temp tables (those whose names start with a #) are session aware and are created in tempdb. In an environment with a lot of concurrency there can be locking issues with the system tables in tempdb.
If this is the case it may be worth considering memory tables (table variables whose names start with @). These do not cause locking problems in tempdb.
Sometimes problems occur with the procedure recompiling every execution. In this case consider using a permanent table and adding the session id as a key column.
July 8, 2004 at 8:42 am
Hello,
You have to take into account the available drive space and limit tempdb growth to the appropriate amount of space. I had a case recently when temdb that was not usually used and was no more then 10 MB grew to 10 GB in a matter of minutes filling up the drive. When we investigated it appeared that only 1 user was working at the time (early hours) and he was doing something new with his home-written report tool and he said he did see drive space warnings. The login that reports use has the only permission: db_datareader for the production database. But it is enough to be able to create temp tables in temdb and if a query requests a big amount of information and a developer stores intermediate results in Tempdb watch your drive space. Tempdb is also used under certain conditions for table-valued functions.
Regards,Yelena Varsha
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply