Global Temporary Table and SQL Server 2000

[davism]

I have a question with regard to temporary tables and a usp_Test1 stored procedure (SP). This is related to a SQL Server 2000. (Yes, I know it's older technology but it is what it is.)

I'm looking a situation:
• On a website, when a User or whomever requests a “Click Here”
• The above SP is executed (i.e. usp_Test1)
• This SP will return let’s say (for example) 50,000 members.
• The screen will only display 100 so the user will click the “Next” button.

 I want to have the SP called again to get the next set using the cursor alternative for the identity column in the table and only return 100.

Now this is where the question comes in… I DO NOT want it to query 50000 members again to get to use the cursor alternative for the next 100. We can’t use a permanent temporary (stage type) type table (basically a table with a persistent structure and non-persisent data) because more than 1 user could select something different at the same time.

So, we’re left with temporary tables. But I also know that local temporary tables (i.e. those prefaced with a “#”) will lose the temporary table when the SP is done and since we may be calling the SP again from the website to get the next 100 and so it we need the temporary table to stay around to use the identity column.

Now, this is where the dilemma is coming in. How can we keep that around because we do not want to have to query 50000 again to get the next 100 and then again for the next 100, etc, etc? Would the use of a global temporary table (i.e. those prefaced with a “##”) work. ## tables are around for the duration of a process but under what condition is the process considered ended or is there a way we can force it and tell it to end?

Would the use of a global temporary table work when there are multiple users that could execute the search simulatenously? I know global temporary tables are around for the duration of the process but what consistitutes the "process". The calling of the SP would be from an IIS application.

Please let me know on this; any information is greatly appreciated.

Thanks

 

[gmmastros]

Global temp tables will not solve your problem. If anything, it will make your problem worse.

The "lifetime" of a global temp table is the same as a regular temp table. The only difference is that multiple users/processes/connections have access to the same global temp table.

To illustrate the point, open Query Analyzer and create a new query window. Execute this code:

Create Table #Temp(EyeColor VarChar(20))
Create Table ##Temp(EyeColor VarChar(20))

Insert Into #Temp Values('Blue')
Insert Into ##Temp Values('Brown')

Now, leave that window and create a new query window. Execute this code:

select * from #Temp

Since this is not the global temp table, you will get [red][tt]Invalid object name '#Temp'.[/tt][/red]

Now execute this code:

select * from ##Temp

This time, the global temp table exists and you get the 1 row, with Brown for the output.

Now... here's the significant part. If you close the Query Analyzer window that you used to create the temp (and global temp) table, and then go back to the other query window, you'll get a message about Invalid object name ##Temp.

The point is, when the session closes, so does temp tables and global temp tables. When someone makes a web request and you dump data in to a temp table, as soon as the page is done loading, IIS will disconnect it's session from the database, and the global temp table will no longer exist.

There are ways to get the Persistent Table method to work. For example, you could assign each web session a unique value (like a GUID) and then store the GUID as another column in the persisted table. This will work well for each web session, but the problem is determining when the session has ended so that you can remove those rows from the persisted table.

Whenever I need to do something like this, I usually pass the starting row number and the number of rows to fetch (in cases where the user only wants to see 59 results instead of 100). With this method, IIS will only see the number of rows that you care about and (if done properly in the stored procedure) the performance should also be good.

You should not need to use any cursors for this. For example, in your current SP, you are creating a temp table with an identity column. As the final select in your stored procedure, you could do something like...

Create Procedure TestPagingResults 
  -- Your existing parameters,
  @StartingRow int,
  @NumberOfRows int
AS
-- Your existing code here that fills a temp table with an identity col.

Select *
From   #YourTempTable
Where  IdentityColumn between @StartingRow And @StartingRow + @NumberOfRows -1

Does this make sense?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Thanks George on the explanation for the Global Temporary table and all.

One of the thoughts I had on the global temptable was to create with the SPID in the name.

BUT you sorta got the whole idea shot down when you mentioned that even if I used global temp tables that it would then still not work because the IIS web site would in essence end the process and therefore stop or delete the global temp table too.

So, in essence, I have to go back to the drawing board.

There permanent table with the colum for a guid or something like that might work. But that guid and the row counts might need to be part of the key due to the mere volume that might happen. I could arguable have a SQL job delete those after like 2 or 3 hours.

But I'm a little confused with the code you provided using a temp table because there may be multiple users and if a user gets a result of 50000 row for instance, the web screen will only even show up to 100 or less if there is nothing else.

So, if I have a temp table that displays initiall displays the first 100 rows (of the 5000), if the user hits the next button, I will have to load the temp table again with the 50000 row and then go a between at you mentioned of the 101-200. Now if I keep doing that it would mean that the 50000 loaded into a temp table how ever many times. Because the temp table would be gone but the end of the execution from the web page.

If I use a permanent table with a guid or something like it may help but I'm not sure I can have an identity column start over based on the new guid. Or could I?

 

[gmmastros]

So, if I have a temp table that displays initiall displays the first 100 rows (of the 5000), if the user hits the next button, I will have to load the temp table again with the 50000 row and then go a between at you mentioned of the 101-200. Now if I keep doing that it would mean that the 50000 loaded into a temp table how ever many times. Because the temp table would be gone but the end of the execution from the web page.

I'm afraid so. But this is how I would do it.

Actually, there's a part to this that I didn't mention, and it will make a huge difference in performance. I have done this before, and I know from experience that this process works, and works quickly.

To explain this, let's talk about the data a little. Suppose we had a table with every man, woman, and child in all of the US. This would be approximately 300 million rows. Now, suppose you all your users to select certain this to filter the data on, like last name. When you return the data to the screen, you would probably want to show first name, last name, date of birth, address, etc....

When you put the data in to the temp table, you should ONLY put the unique identifier for the person in the temp table, NOT all of the data you want to return. Once you filter this down to just the 100 rows you care about, then join back to the "People" table to get the rest of the data.

Example:

Create Procedure GetPeoplePaged
  @LastName VarChar(100),
  @FirstRow Int,
  @NumberOfRows Int
As
SET NOCOUNT ON

Create Table #Temp(RowId Int Identity(1,1) Primary Key, PersonId int)

Insert 
Into   #Temp(PersonId)
Select PersonId 
From   People 
Where  LastName = @LastName
Order By FirstName

Select People.*
From   #Temp
       Inner Join People
         On #Temp.PersonId = People.PersonId
Where  #Temp.RowId Between @FirstRow And @FirstRow + @NumberOfRows -1
Order By People.LastName

Notice how I load the #Temp table with just an identity column and the primary key. Also notice how I create a primary key on the RowId column. If you picked a common last name (like Jones), there may be many thousands of rows that match the selection. But there wouldn't likely be more that 100,000 rows. The temp table would store 2 integers, one for the identity column and another for the primary key value. That's 200,000 integers. each integer takes 4 bytes, so that's 800,000 bytes to store this data. There's likely to be some overhead and such, but we're still talking about less than 1 megabyte of storage (which is ridiculously small). The primary key will allow us to quickly narrow down the rows, using an index seek, to just the 100 you want. The join to the super big people table will only be on 100 rows, so this part will also be fast.

All in all, if you do this carefully, it should all be fast.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

I was hoping to not kill the DMBS with all the queries that would provide repeated data.

However, you have some very good informaton here and it coincides with the performance with the use of the keys and such.

One quick question though, for the optimizer to know of the existence of the key on temp on the temp table wouldn't the:

Select People.*
From #Temp
Inner Join People
On #Temp.PersonId = People.PersonId
Where #Temp.RowId Between @FirstRow And @FirstRow + @NumberOfRows -1
Order By People.LastName

need to be in a child-proc called from the main proc?

It was always my understanding that it needed to and when I looked at execution plans it seemed so as well but do you have any information on that?

Thanks!

 

[gmmastros]

No. This would not need to be a child-proc is order for the engine optimizer to use the index. I will admit that different indexes may give different results, but you can index a temp table any way you want without needing to resort to child-procs.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Ok, let me take a stab and this. I will probably go off a child proc just based on previous findings from execution plans.

But let me take a stab at this and will let you know ASAP.

Thanks!

 

[gmmastros]

I'm curious to know if you've made any progress on this.

If your query is running slow, it's probably the initial load in to the temp table. If that's the case, then I encourage you to show the query so we can help to improve the performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom