Temporary Table Accessable Only In Instance of Procedure

If I have a procedure that will be run simultaneously by several people
which requires the use of a temporary table is there a way for the table
to be accessible only in the instance in which the procedure is being
run so that multiple tables can have the same name but not have
collisions? I would normally think to name the table with a random
string but apparently SQL rules do not allow dynamically generated table
names. The other thing I would try is to have a permanent memory table
with the structure I need, with an additional column that would be
populated with a random number that indicates which batch is being
processed, with the contents being deleted right before the procedure
ends. I don't know what kind of impact that would have on the server,
and am hesitant to try as my test environment has nowhere near the
capacity of the production environment, in just about every way possible.

Re: Temporary Table Accessable Only In Instance of Procedure

No bother wrote:
> If I have a procedure that will be run simultaneously by several people
> which requires the use of a temporary table is there a way for the table
> to be accessible only in the instance in which the procedure is being
> run so that multiple tables can have the same name but not have
> collisions? I would normally think to name the table with a random
> string but apparently SQL rules do not allow dynamically generated table
> names. The other thing I would try is to have a permanent memory table
> with the structure I need, with an additional column that would be
> populated with a random number that indicates which batch is being
> processed, with the contents being deleted right before the procedure
> ends. I don't know what kind of impact that would have on the server,
> and am hesitant to try as my test environment has nowhere near the
> capacity of the production environment, in just about every way possible.

Creating and dropping tables is a relatively expensive operation
when compared to inserting and deleting rows in an existing
table. So the permanent table would be my choice.

I don't think random numbers to identify which batch is being
processed is a good idea because it can lead to orphaned rows in
the event the user drops out, or is dropped out, of the routine
before completion.

My personal preference is to have a permanent table with an
extra column for user id. This will allow you to reconnect
users to their batch data and resume processing while at the
same time the user id can be used to limit access to data.

Don't worry about the performance in your test environment. If
your procedure has tolerable performance in your limited
resource test environment it will be more than adequate in your
production environment. From a practical point of view MySQL is
a very fast RDBMS and properly tuned queries are amazingly
quick. Insert and delete performance tends to be subject to the
number of indexes and the number of rows being inserted or
deleted. If the number of rows is reasonable performance won't
be an issue.


HTH
Jerry