Dynamic Tables Names and Temporary Tables Options

Firstly I consider myself quite an experienced SQL Server user, and
am
now using SQL Server 2005 Express for the main backend of my
software.

My problem is thus: The boss needs to run reports; I have designed
these reports as SQL procedures, to be executed through an ASP
application. Basic, and even medium sized (10,000+ records) reporting
run at an acceptable speed, but for anything larger, IIS timeouts and
query timeouts often cause problems.


I subsequently came up with the idea that I could reduce processing
times by up to two-thirds by writing information from each
calculation
stage to a number of tables as the reporting procedure runs..
ie. stage 1, write to table xxx1,
stage 2 reads table xxx1 and writes to table xxx2,
stage 3 reads table xxx2 and writes to table xxx3,
etc, etc, etc
procedure read final table, and outputs information.


This works wonderfully, EXCEPT that two people can't run the same
report at the same time, because as one procedure creates and writes
to table xxx2, the other procedure tries to drop the table, or read a
table that has already been dropped....


Does anyone have any suggestions about how to get around this
problem?
I have thought about generating the table names dynamically using
'sp_execute', but the statement I need to run is far too long
(apparently there is a maximum length you can pass to it), and even
breaking it down into sub-procedures is soooooooooooooooo time
consuming and inefficient having to format statements as strings
(replacing quotes and so on)


How can I use multiple tables, or indeed process HUGE procedures,
with
dynamic table names, or temporary tables?


All answers/suggestions/questions gratefully received.


Thanks

Re: Dynamic Tables Names and Temporary Tables Options

brstowe wrote:

> How can I use multiple tables, or indeed process HUGE procedures,
> with
> dynamic table names, or temporary tables?

Isn't this your answer right here? Just CREATE TABLE #foo instead
of CREATE TABLE foo, etc. and each process will get their own local
version of the #foo table.

Re: Dynamic Tables Names and Temporary Tables Options

On Oct 5, 7:07 pm, Ed Murphy wrote:
> brstowe wrote:
> > How can I use multiple tables, or indeed process HUGE procedures,
> > with
> > dynamic table names, or temporary tables?
>
> Isn't this your answer right here? Just CREATE TABLE #foo instead
> of CREATE TABLE foo, etc. and each process will get their own local
> version of the #foo table.

please be honest and tell me if I have completely misunderstood...?
the temporary tables are session/process/user specific...

two users could essentially create/drop tables, and populate/delete
information from tables with exactly the name e.g. #temptable1 at
exactly the same time?

thanks

Re: Dynamic Tables Names and Temporary Tables Options

On Mon, 08 Oct 2007 02:01:58 -0700, brstowe wrote:

>On Oct 5, 7:07 pm, Ed Murphy wrote:
>> brstowe wrote:
>> > How can I use multiple tables, or indeed process HUGE procedures,
>> > with
>> > dynamic table names, or temporary tables?
>>
>> Isn't this your answer right here? Just CREATE TABLE #foo instead
>> of CREATE TABLE foo, etc. and each process will get their own local
>> version of the #foo table.
>
>please be honest and tell me if I have completely misunderstood...?
>the temporary tables are session/process/user specific...
>
>two users could essentially create/drop tables, and populate/delete
>information from tables with exactly the name e.g. #temptable1 at
>exactly the same time?

Hi brstowe,

Indeed. In fact, a single user can even execute two copies of the
procedure in parallel, over different connections, and use a temp table
with the same name.

Here's a quick demo. Open two windows in SQL Server Management Studio or
Query Analyzer. Copy, paste, and execute the first code in the first
window, then (while the code is still running) copy, paste and execute
the second code in the second window.

==> Below is the code for the first query window:

-- Create temp table
CREATE TABLE #MyTempTable
(KeyColumn int IDENTITY NOT NULL PRIMARY KEY,
DataColumn varchar(30));
-- Insert some data
INSERT INTO #MyTempTable (DataColumn)
VALUES ('Data for temp table 1');
-- Show result
SELECT * FROM #MyTempTable;
-- Delay a few seconds, to allow other connnection to run
WAITFOR DELAY '0:0:10';
-- Show that temp table is unchanged
SELECT * FROM #MyTempTable;
-- Clean up
DROP TABLE #MyTempTable;
go

==> Below is the code for the second query window:

-- Create temp table - different layout than the one in the other
connection, but same name
CREATE TABLE #MyTempTable
(TheKey int IDENTITY(15,5) NOT NULL PRIMARY KEY,
TheDate datetime DEFAULT (CURRENT_TIMESTAMP),
TheText varchar(50) NOT NULL);
-- Insert some data
INSERT INTO #MyTempTable (TheText)
VALUES ('Data for temp table #2');
-- Show result
SELECT * FROM #MyTempTable;
-- Clean up
DROP TABLE #MyTempTable;
go


--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis