Temp Tables + sp

[vlitim]

I have a table with 15,000 records in it that I am then joiing to several other tables, inserting the results in a temp table. I am then using asp and the sp to page throught the records. The sp only passes the correct records to the page, nut the problem is that it is a very complicated query that takes 30 secs +, and that means everytime you press next you have to wait 30 secs again, whereas if you could store the results of the first search which could be looked at everytime the person clicked next it would greatly reduce the time taken! Now I expect this is just not possible as it would have to be some sort of temp table that wasn't removed when the sp stopped!

any ideas??

Cheers
Tim

 

[rajeevnandanmishra]

Hi Tim,

Try using global temporary tables (## tables) or permanent temporary tables (created in tempdb).


---
Raj

 

[philhege]

As a quick-and-dirty, Tim, I'd create a global temp table with a GUID as its name, and refer to that for your paging. The only problem in your disconnected environment is how to delete it if the user has left the site. Maybe use some scheduled job that would clean up global temps related to your page every x (insert time interval here).

Perhaps you should investigate ASP.NET, which can send the whole recordset to the client, or keep it in session space.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK
<a href=&quot;

http://www.iimak.com&quot;>http://www.iimak.com</a>

-----------
Boy howdy, my Liberal Studies degree really prepared me for all of this....