Cache query results for paging; how? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Cache query results for paging; how?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-10 : 20:23:31

I've got a big query that's run frequently. Thing is, it's rarely the same; it's assembled as dynamic SQL and then executed. The query takes about 20 parameters, and depending on the presence/absence of them, the dynmaic SQL can vary greatly. I tried parameterizing it into a single SQL query once and it was a disaster.

So anyways, I've got this pretty expensive query, and among other things it returns paged results. Right now, it's using the table-variable-with-identity trick to do pagination (interim results are selected into the table variable, then the appropriate rows are selected out of the table variable).

If occurs to me that I could speed things up and lighten the load quite a bit by selecting into a temp table or plain old table instead, and returning a unique table ID to the calling application. From there on, for paging purposes, only the select against the temp table would need to be run, not the entire dynamic SQL which populates the table.

First, does that make any sense? Second, and I correct in thinking that I probably want to use a global table variable, so queries from the same user that come on on another SPID can get to it? Should I keep a directory using some kind of table-of-tables, so I know when each one was last accessed so I can delete them after inactivity? And, finally, am I correct in thinking that a global temp table is more appropriate for this than actually creating/dropping thousands of "real" tables every day?

Thanks in advance for any insight!
-b

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-04-10 : 20:30:25

IMHO creating temp tables for paging seems expensive in server resources.

My solution works very well for my situation, take it for what its worth:

I generate a dynamic where clause on the client side (I have close to 20 criteria as well). I then have a procedure do a count of records meeting that criteria. That info is passed to my paging function along with the current page number. I never retrieve anymore than what I will display on the client at that time. You can see my paging sproc below.


CREATE proc sprGetTheatreRange2 
  @CurrentPage int,
  @Pages int,
  @Remainder int,
  @Where nvarchar(2000)
as
set nocount on

declare @SQL nvarchar(4000)
select @SQL =
	case  
		when @CurrentPage = 1
			then 'select top 25 TheatreID, Company, TheatreName, 
				  PCity, PState, PZip, PriPhone from dba0082.TheatresAndContacts ' + @Where + '  and Active = 1 order by
				  TheatreID'			 
		when @CurrentPage > 1 and @CurrentPage <= @Pages 
			then 'select top 25 * from (select top 25 * from 
					(select top ' + cast((25 * @CurrentPage) as varchar(8)) + '
				  	 TheatreID, Company, TheatreName, PCity, PState, PZip, PriPhone 
				  	 from dba0082.TheatresAndContacts ' + @Where + '  and Active = 1 order by	TheatreID asc) t1
				  order by TheatreID desc) t2 order by TheatreID asc'				 
  		when @CurrentPage = @Pages + 1 and @Remainder > 0 
			then 'select top ' + cast(@Remainder as varchar(5)) + ' * from
				  (select top ' + cast(@Remainder as varchar(5)) + ' TheatreID, Company, TheatreName, 
				  PCity, PState, PZip, PriPhone from dba0082.TheatresAndContacts ' + @Where + ' and Active = 1 order by
				  TheatreID desc) t3 order by TheatreID asc'			 
	end

exec sp_executesql @SQL

hth,
Justin

Expect 0x80040106

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-10 : 20:42:38

Thanks for the note -- unfortunately, this wouldn't solve my underlying problem, which is that the actual query generating data is very expensive. It's a matchmaking type search that often needs to do order-by-distance, which is very resource intensive even with a precomputed distance-between-zip-codes table.

I hate temp tables myself, but if I want to not run the *big* query more than once per pageset, I think I've got to store the intermediate values somewhere. I suppose that could be application-side, too, but there can be as many as 1000 rows returned, and I'd hate to stuff them into a session variable.

I'm definitely open to other ideas, but I don't think this one will do the trick I need.

Cheers, and thanks; that is an educational example for other purposes, that's for sure.
-b

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-04-11 : 00:24:28

Hi

Maybe consider a fixed table. Give it some sort of identifer (GUID ?), a row number (so you can easily select rows 10 to 20) and a date column.

When they first execute the search, it creates a GUID and inserts the results into your fixed table.

For your paging, it becomes simple :

SELECT * FROM Results
WHERE
RowNum Between 11 and 20 AND
SearchID = 'E22768B6-CBA8-4236-9D56-C4D0B90E79BF'

Then run a job every half hour or something to kill old results.

EDIT : DUH... Re-read your post, that is exactly what you are thinking. Yes, give it a try

Damian

Edited by - merkin on 04/11/2003 00:26:10

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-11 : 01:18:31

Actually, that wasn't exactly what I was thinking; I was thinking of creating a seperate temp table for each query, with the table name being an identifier.

However, I like your way. It may be a bit tough on transaction log backups, since it would mean backing up transitory data, but it has the advtantage of allowing indexing of the cache table ("sort this same search by a different field"), and it lessens the load on tempdb.

My only question is generating the RowNum field. Is this going to be one of those weird

declare @i int,@g uniqueidentifier

select @g=newID()

insert into cache (g,rownum,field1,field2)
  select @g,@i = @i = @i + 1,field1,field2
  from ...

...kinds of things? That is, doing the insert with an incrementing counter?

Thanks
-b

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-11 : 01:59:09

Ha! Not only were you right, Merkin, my way wasn't going to work at all. Global temporary tables are automatically deleted by SQL server when the session that created them ends, and all other sessions are done referencing them. In an ASP environment, with late bind and early destroy for the ADO objects and connection pooling, the global temp table is gone before it can be of any use.

So the GUID + rownum approach works... once I can figure out how to generate the rownum.

Cheers
-b

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-11 : 02:15:25

aiken,

Just add an Identity column and a StartingNo column and then a view...


CREATE TABLE Results (GUID uniqueidentifier, RowPos INT IDENTITY(1,1), Start# int)
GO
CREATE VIEW Results_Relative
AS
SELECT GUID, RowPos - Start# +1 as RowNUmber from Results
GO
declare @g uniqueidentifier
SET @g = NEWID()
Insert Results(GUID, Start#)
	SELECT @g, ISNULL((SELECT MAX(RowPos) from Results),0) +1 from Numbers
SET @g = NEWID()
Insert Results(GUID, Start#)
	SELECT @g, ISNULL((SELECT MAX(RowPos) from Results),0) +1 from Numbers

Select * from Results_Relative

HTH

DavidM

"SQL-3 is an abomination.."

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-11 : 02:40:13

byrmol, won't this fail if two different users run queries at the same time (their identity values will be interleaved)? Or maybe I'm just confused -- my head is spinning and it's about time to call it quits.

I *almost* have the Merkin approach working, but I don't have a good way to generate the rownum field during the insert into/select from phase. I desperately wish that

declare @i int,@g uniqueidentifier 

select @i=0,@g=newID()
insert into cache (g,rownum,field1)
select @g,@i = rownum = @i + 1, field1
from table

...worked. That's the cool way to get row numbers in updates, but it don't seem to work for insert's. I'm tempted to go back to generating everything into a table variable, then copying it from the table variable to the cache table, but it bugs my sense of elegance to do so.

(And then there's byrmol's method, which I'll admit to just flat out not understanding, though the view idea is tantalizing).

Cheers... and thanks, everyone. I do appreciate the help here!
-b

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-11 : 14:44:38

Oh, come on -- we're so close here. I just need a way to insert a running rownum counter as part of an insert/select from query, and then I'll be in business.

You good folks don't want to make me use an intermediate table variable just to get an identity value for each row, do you?

Cheers
-b

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-11 : 15:11:59

Here's a way to return rows @a through @b from a table or query -- but only if it is ordered by a unique field:

(assume we are ordering by the field "ID")

declare @startingID int;
declare @a int;
declare @b int;

set @a = 200
set @b = 250

-- @a = starting row #
-- @b = ending row #

set rowcount @a
select @startingID = ID from yourtable order by ID ASC

set @b = @b - @a

set rowcount @b
select * from yourtable where ID >= @startingID order by ID

set rowcount 0

---

Something to play around with, anyway ... should be fairly efficient though it has to make 2 passes through the data, and it doesn't use temp tables or updates or anything like that. Also should be more efficient than the SELECT TOP x FROM (Top y) techniques.

has anyone else used this techinque? any comments?

- Jeff

Bambola
Posting Yak Master

103 Posts

Posted - 2003-04-14 : 07:44:20

select (d0.dig + d1.dig + d2.dig + d3.dig) as dig 
    from 
        ( select 0 dig union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 
          union all select 6 union all select 7 union all select 8 union all select 9) d0,  
  
        ( select 0 dig union all select 10 union all select 20 union all select 30 union all select 40 union all 
          select 50 union all select 60 union all select 70 union all select 80 union all  select 90) d1, 
        
        ( select 0 dig union all select 100 union all select 200 union all select 300 union all select 400 
          union all select 500 union all select 600 union all select 700 union all select 800 union all 
          select 900) d2,

        ( select 0 dig union all select 1000 union all select 2000 union all select 3000 union all 
          select 4000 union all select 5000  union all select 6000 union all select 7000 union all select 8000 
          union all select 9000) d3
order by dig

Bambola.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources