sp with temporary tables runs forever ?

Hi all,Have a stored proc that creates three tables and uses these tables in a select within the same sp.
If I create these tables in my db the sp runs ~ 20 sec.
If I use temporary tables instead, the sp runs forever.
Does any1 have a logical explanation for this behavior ??

TIA
acki

@Frank
Sorry no, does not work.
Is there a limit with the size of a post in this forum ???

If you run the version that creates the tables, then how large do the tables end up? Check against file size and free space in tempdb's file(s), adjust file size(s) and growth factor(s) accordingly.

@Adriaan
sizes for the temporary tables are:
9 rows, 0.008 MB
2860 rows, 0.345 MB
10100 rows, 0.133 MB

OK, that's clearly peanuts - assuming this is against production data.
Are you by any chance using INTO like this:
SELECT columns
INTO #tmp_table
FROM permanent_table
This can cause locking issues. Always best to use a CREATE TABLE statement followed by an INSERT INTO query.

@Adriaan
I use:
DECLARE @Table0 TABLE ( Field1 ... )
INSERT INTO @Table0
SELECT FROM Permanent_Table0...
DECLARE @Table1 TABLE( Field1 ...)

INSERT INTO @Table1
SELECT
...
FROM
Permanent_Table1
WHERE
@Table0.Field1 = Permanent_Table1.Field1
checking for locking issues...

Try adding WITH (NOLOCK) after each table in your FROM clauses.

You are using table variable here but not temp table as described. No full script posted here to be sure. But in general, for big results sets in @table0 or @table1, you might be better off to use #temp table style to gain the statistics/execution plan or to create extra index on temp table to help the join.
Adding WITH (NOLOCK) could help too if it is locking issue.
Eugene

For larger data like above 10K rows the table variables are not good performers. Change the table variable to #temp table and add an additional filter to your query to get just 100 rows or so and see how it goes if that succeeds then remove the filter and run.
Good luck...

In addition to what everyone else has said with using an actual Temp table instead of a table variable, also remember you can create an index on a temp table.

@Frank
lines where temporary tables have been replaced with 'real' tables are ---