November 13, 2009 at 11:16 am
[This is all in vb.net & SQL Server 2005]
I have a rather large stored procedure that creates and uses about 15 different tables. Another script then uses these tables for post-processing. The tables are then deleted after this post-processing script runs so the database is not cluttered.
This worked great for a single user, but if more users start using it, I found that the stored procedure was "bumping" into other iterations of itself and causing all sorts of drama.
Anyway, I can't use memory tables because they would be erased after the execution of the stored proc stopped, which would mean the post-processing script would have no tables to work it's magic with.
So I have this crazy idea of using a random number to append to the table names. So like "myTableA_384871198". That one random number would be used for each table generated by a single instance of the stored procedure.
So this way, if another user tried to use a program that used this stored procedure, a new random number would be generated and they wouldn't "bump" into each other.
And since these tables are deleted at the end of the post-processing script, then my database won't look messy.
What do you think? Am I nuts?
Thanks!
November 13, 2009 at 11:23 am
Thatt'l probably put a decent amount of strain on your server... you should be careful.
Why don't you instead just keep all of the stuff being used for post-processing on the local machine? Either within the application code itself, through in-memory variables, or by writing temporary XML or TXT files and then reading them for the post-processing?
November 13, 2009 at 11:32 am
Are you calling the 2nd script from the same program? We used to add the SPID to a table, and then each call from a program could insert rows and work with them across batches knowing it's SPID. When it first connected, it would ensure that all rows from the table was removed based on the current SPID
November 13, 2009 at 12:07 pm
I think I'm thinking along the same lines as Steve. Why don't you create a single table that they all share and their data is seperated by some id column, so each run has its own ID, and when the run is done it deletes its data. If when there is no data in the table the last run removes it. Also, are these in the database proper or in tempdb. If they are in the database proper that is not what I consider a good practice, you are just generating more data in the transaction log that later has to get backed up.. Tempdb is the place to be..
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply