Search code examples
databasejdbcsap-ase

How temporary tables behave in Stored Procedure of sybase

I have tried finding the answer for my issue but there were no appropriate answers found.

We have a Java web application where the data is loaded on the launch screen based on the user roles. The data on launch screen is fetched by executing a stored procedure which in turn returns a ResultSet and then data is processed from it to show on launch screen.

My Queries:

  1. If multiple people launch the Web Application simultaneously- will the stored procedure get executed multiple times? Will the execution be done parallely on single instance of Database Stored Procedure (or) for every request a new instance of stored procedure is created by Database. Basically I am curious to know what happens behind the scenes in this scenario.

Note: Inside this SybaseASE Stored Procedure we use a lot of temporary tables into which data is inserted and removed based on several conditions. And based on roles different users will get different results.

  1. What is the scope of temporary table with in Stored Procedure and as mentioned in point 1 if multiple requests parallely access the Stored Procedure what will be the impact on temporary tables.

  2. And based on point 2 is there a chance of database blockage or Deadlock situations occurring because of temporary tables with in a Stored Procedure?

Solution

  • 1: two executions of the same stored proc are completely independent (there may be some commonalities in terms of the query plan, but that does not affect the results)

    2: see 1. temp tables are specific to the stored proc invocation and the user's session; temp tables are dropped automatically at the end of the proc (if you didn't drop them already).

    3: there cannot be a locking/blocking issues on the temp tables themselves. But there can of course always locking/blocking issues on other tables being queried (for example, to populate the temp tables). Nothing special here.