Creating temp table in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating temp table in Stored Procedure

Author

Topic

unicorn21
Starting Member

10 Posts

Posted - 2004-09-03 : 00:10:28

Hi!
I currently have a stored procedure with 10 seperate select statements that all return different tables of information to the Dataset.
Each select statement queries a number of the same tables from each query.

I would like to from within the stored procedure create a temporary table with info that i can use on all the seperate queries.

Something like this does work:
create table #tempstores(UID int, PackageID int)
insert #tempstores
select UID, PackageID
from scssSubscription
where ISPID = 193

but because its using the insert it takes too long.
I would just like to run a select statement and use the results in all other tables.

The View State would work great exept the stored procedure won't allow parameters to be declared first (view state has to be the first thing)

So now I am looking at using Cursors to somehow point to the result table.
Im wondering if im on the right track here or is there another way to do this (if this all makes sense!)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-03 : 00:32:50

How big is your scssSubscription table?
Perhaps it is a large table and is not indexed properly - if this is the case then you need to look at indexing the table and you could probably get your existing code to execute a whole lot quicker.

Edit:
The other thing is - I hope that the temp table you are creating is not too large, this could also cause lots of performance problems.

Duane.

unicorn21
Starting Member

10 Posts

Posted - 2004-09-03 : 00:48:48

Yes, indexing is on.
The subscription table has millions of records...
because there are 10 sepeate queries all querying the subscription table...
Rather than each query searchng through the whole subscription database I would prefer to search through a temp table with only records relating to a certain client.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-03 : 01:21:00

Consider posting the entire procedure

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

unicorn21
Starting Member

10 Posts

Posted - 2004-09-03 : 01:31:25

Pity it wont let me create a view and declarations in a stored procedure at the same time :( as this would have worked great
create view tempTable
as
select *
from scssSubscription sub
where sub.ISPID = 193
GO

select *
from tempTable
But anyway, back to the problem...

Ok, I shorened it a lot :)
See, I am using the Subscription table everywhere and would rather replace it with a temp table with only subscriptions relating to the customer.
STORED PROCEDURE.....

declare
@CUSTOMER int, @StartDate datetime, @EndDate datetime
set @CUSTOMER = 193
set @StartDate = '6-1-2004'
set @EndDate = '9-1-2004'

-- 1
SELECT COUNT(iuser.ISPUserID) AS SubCount
FROM ISPUser iuser
WHERE iuser.CUSTOMER=@CUSTOMER
AND
(
iuser.Status=1
)
AND EXISTS
(
SELECT sub.UID
FROM Subscription sub
INNER JOIN Package pack ON (pack.UID=sub.PackageID AND (pack.Status=1 OR pack.ModifyDate>@EndDate))
)
-- 2
SELECT COUNT(iuser.ISPUserID AS SubCount
FROM ISPUser iuser
WHERE iuser.CUSTOMER=@CUSTOMER
AND
(
iuser.Status=1
)
AND EXISTS
(
SELECT sub.UID
FROM Subscription sub
INNER JOIN Seat seat ON (seat.SubscriptionID=sub.UID AND seat.CreateDate<@EndDate)
)
-- 3 etc etc

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-03 : 01:36:49

How many records will each client have? If it's not more than 10k, then put into a table variable. If it is, you will need to use the temp table. You also might want to look into using indexed views and partitioning your table. What indexes do you have on iuser and subscription?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-03 : 01:37:56

A cursory look seems like you can join all of these queries with UNION and you can also use dynamic sql to create a view in a sp.

Really, post the entire thing so we can look at it.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

unicorn21
Starting Member

10 Posts

Posted - 2004-09-03 : 02:25:35

using the table is too slow as you have to do inserts after the select.

I think a cursor pointing to a table is the way to go but not sure how to implement it. At the moment and can only get rows out using a While loop.
Im gonna keep trying...

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-03 : 03:53:48

Avoid the cursor route.

Have you tried SELECT .. INTO if appropriate? It will be faster than create.. insert assuming that you can do the inserting in one batch.

-------
Moo. :)

unicorn21
Starting Member

10 Posts

Posted - 2004-09-03 : 19:47:39

hi, yeah, i just tried a select into but it actually creates a table in the database. If only i could save it to memory rather than the table. I even tried putting a # in front of the table name. It didnt actually create a table but said it still existed. hmmm
still stuck :(

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 20:27:40

#temp tables are stored in tempdb so why wouldn't that work for you?
with select ... into #temp i mean.
they exist only for one batch.... and if nothing else you can stil do drop table #temp

Go with the flow & have fun! Else fight the flow :)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-03 : 22:42:14

unicorn. Use a table variable and just see how it works.

DECLARE @table TABLE(column1 VARCHAR(55), etc INT, etc1 INT)

INSERT @table(column1, etc, etc1)
SELECT column1, etc, etc1 FROM whatever

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

unicorn21
Starting Member

10 Posts

Posted - 2004-09-06 : 02:04:34

OK, I have decided to try using a tem,p trable for now as it doesn't take long to create. I fun a command that displayed useful info 'SET STATISTICS IO ON' so i can check what was going on. I used this...

CREATE TABLE #tempSub(UID int, UserID varchar(255), PackID int, SiteID int)

INSERT INTO #tempSub(UID, UserID, PackID, SiteID)
SELECT UID, UserID, PackID, SiteID
--INTO #tempSub
FROM subscription
WHERE MainID = @ID
AND CreateDate >= @StartDate
AND CreateDate <= @EndDate

then I can use this instead of doing the same checking in each query but...

I noticed that my scan counts jumped from 1 in each query to 300+!
I'm guessing this has something to do with indexing.
I tried something like this:
CREATE UNIQUE CLUSTERED INDEX Indx1 ON #tempSub(UID, PackID)
but it made no difference.
I must admit i dont really understand how the indexes work between related tables but Im wondering if Im on the right track or if the #tempSub table cannot be indexed the way the normal subscription table is.
thanks for all the help!
I have learnt a lot about Sql Server in 2 1/2 days!!

unicorn21
Starting Member

10 Posts

Posted - 2004-09-06 : 02:19:55

Actual Results
BEFORE:

Table 'User'. Scan count 52, logical reads 184, physical reads 0, read-ahead reads 0.
Table 'Subscription'. Scan count 1, logical reads 1628, physical reads 0, read-ahead reads 0.
Table 'Package'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'Site'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0.

AFTER:

Table '#tempUser __ Scan count 52, logical reads 52, physical reads 0, read-ahead reads 0.
Table 'Package'. Scan count 359, logical reads 1077, physical reads 0, read-ahead reads 0.
Table 'Site'. Scan count 370, logical reads 1480, physical reads 0, read-ahead reads 0.
Table '#tempSub __ Scan count 11, logical reads 11, physical reads 0, read-ahead reads 0.

I
t Seems to Make can counts a lot Worse. hmmm, be good if i could get the scan counts to 1 :)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-06 : 22:15:31

Post the before and after SQL Statements you used.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

unicorn21
Starting Member

10 Posts

Posted - 2004-09-07 : 02:26:25

Thanks for all the help.
Let me apologise for the terrible sentences written by me that don't make sense (i was in a big rush yesterday)
But by using temp tables with about a 10 second overhead creating them i halved the amount of time it takes to generate my reports.
For some reason though on one database the scans are terrible and it takes longer (as posted above)
I'm gussing this database might be really fragged!
Does a fragged database create more scans?!?
Anyway, thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources