Considering delete a temporary table after use it

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)

Considering delete a temporary table after use it

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-03-04 : 04:57:36

Hi,

I wrote a Stored Procedure so that in my ASP.NET application the user can navigate through a results page (a data grid) of a database query with two buttons: forward and backward. To do so, this SP uses two variables passed in by the app, @CurrentPage and @PageSize, the first one is the number of the current page the user is seeing the results, and the second one is the number of items (rows) are displayed in every page. These variables are used to return, every time the SP is called, only the number of rows allowed per page, avoiding to return in every page 5000 records only to display 10 of them.

Here is the SP:


USE market
GO
ALTER PROCEDURE offers_list
@CurrentPage int, @PageSize int, @TotalRecords int output
As

CREATE TABLE #tempTable
(
 Id int IDENTITY PRIMARY KEY,
 Offer_id bigint,
 Date datetime,
 Offer_Title varchar(100),
 Company_name varchar(100),
 City_name varchar(100)   
)

INSERT INTO #tempTable
(
Offer_id,
 Date,
 Offer_Title,
 Company_name,
 City_name 
)

Select Offer_id, Date, Offer_Title, user.Company_name, city.City_name
From Offers As offe

JOIN Users As user
On offe.User_num = user.User_id
JOIN Cities As city
On offe.city_num = city.City_id

Order by Date DESC


Declare @FirstReg int, @LastReg int
Select @FirstReg = (@CurrentPage - 1) * @PageSize  
Select @LastReg = (@CurrentPage * @PageSize) + 1


SELECT Offer_id, Date, Offer_Title, Company_name, City_name  
FROM #tempTable
WHERE
Id > @FirstReg AND Id < @LastReg

SELECT @TotalRecords = COUNT(*) FROM #tempTable    
GO

This SP works, and also the app paging through the results. It is better to drop (delete) the 'tempTable' at the end of the SP? If so, how can I do it? Is there something else to improve this SP?

Thank you,
Cesar

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-03-04 : 06:49:05

Are you using SQL server 2000? In that case I would seriously consider using a table variable instead. Does all its work in memory and is automatically deleted once the procedure terminates.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

cesark
Posting Yak Master

215 Posts

Posted - 2005-03-04 : 07:05:36

Hi,
Yes I have SQL Server 2000, but I don' t know how to use the 'table variable' you mentioned, I have searched by these words but I didn' t find it. Could you give me a little example please? Or some reference?

Thank you

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-04 : 07:19:52

Temp tables are also dropped at the end of the batch.
It's not true that table variables are always held in memory.
You should drop the temp table just for completeness but it's not necessary.
drop table #tempTable after the select.

To use a table variable (may be slower if it's large)

declare @tempTable TABLE
(
Id int IDENTITY PRIMARY KEY,
Offer_id bigint,
Date datetime,
Offer_Title varchar(100),
Company_name varchar(100),
City_name varchar(100)
)

INSERT INTO @tempTable
(
Offer_id,
Date,
Offer_Title,
Company_name,
City_name
)

Select Offer_id, Date, Offer_Title, user.Company_name, city.City_name
From Offers As offe

JOIN Users As user
On offe.User_num = user.User_id
JOIN Cities As city
On offe.city_num = city.City_id

Order by Date DESC

Declare @FirstReg int, @LastReg int
Select @FirstReg = (@CurrentPage - 1) * @PageSize
Select @LastReg = (@CurrentPage * @PageSize) + 1

SELECT Offer_id, Date, Offer_Title, Company_name, City_name
FROM @tempTable
WHERE
Id > @FirstReg AND Id < @LastReg

SELECT @TotalRecords = COUNT(*) FROM @tempTable

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-03-04 : 07:31:44

Yup, there it is...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-04 : 10:19:13

>>Is there something else to improve this SP?

How is the performance ? It is relatively expensive to continually re-query & repopulate a table and then only return a small percentage of records from it.

If this is a result of a user search, you could give the search results a unique identifier (GUID), place the results in a permanent table with the GUID as part of the key, and then each call from the client app for the next/previous page sends in the GUID of the search results and you pull from an already populated table.

The only drawback to this is the requirement to purge old search resultsets from the table, which you can do by capturing a timestamp on when the search is performed and purge based on staleness.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-04 : 10:26:23

Suprised Jeff hasn't posted here...

This is the best paging article I've read

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

Brett

8-)

cesark
Posting Yak Master

215 Posts

Posted - 2005-03-04 : 10:40:06

Ok nr, I see, I read in BOL that a temporary local table created into a SP is automatically deleted once the SP finishes, so my original code is good. Isn’ t it?

PW, perhaps the SP performance now is reasonably good. Isn’ t it?

Brett, I am going to read that article.

Thank you

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-04 : 12:08:04

You should always clean up after yourself....

Save you trouble in the long run...

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-04 : 12:16:36

quote:
Originally posted by X002548

You should always clean up after yourself....

Save you trouble in the long run...

Brett

8-)

That's what my mom always says!

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources