| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2005-12-28 : 19:18:16
|
I've got an app that does a lot of searching; many searches turn up 1000's of records. Right now, those queries are using a table variable with an identity column for pagination. Each time a user goes to a new page, the same query runs again, and just a different range of rows are selected from the table variable. There are some optimizations (expensive computations that don't affect ordering are only done for the rows we're returning, etc).I'd like to move to a model where the searches insert rows into a search cache table, and then rows are returned from there, along with a search ID that could be used to get subsequent pages without rerunning the entire query.The difficulty I'm having is figuring out how to manage pagination. My plan is to have one table that keeps track of what queries are cached, and a second one that has the actual cache data. The first one would be:create table matchCache (i int identity primary key, i_users int NOT NULL default(0), [dateitme] smalldatetime NOT NULL default(getdate()), resultCount int NOT NULL default(0)) ...and the second one would be something like:create table matchCacheItems (i_matchCache int NOT NULL,i_result int) Of course, that doesn't preserve ordering. I'm hoping to insert directly into the cache table rather than using a table variable as a halfway point; otherwise I could just use the identity from the table variable to indicate ordering.Is there any clever way to have an "order" column in the matchCacheItems table that will indicate what order the row was inserted in? Or does someone see a better approach that preserves ordering and pagination?Thanks-b |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-12-29 : 00:35:38
|
| Thanks for the links, but they don't really help. I'm already pretty good with pagination, and have some very fast SP's for it. My problem is storing the entire resultset into a cache table for future lookups, while preserving the ability to do pagination. Specifically, preserving the order-of-insertion for medium sized lists all being inserted into the same table.I'm starting to think I need an identity column on the matchCacheItems table, and then use SCOPE_IDENTITY() and @@ROWCOUNT to capture the first and last inserted identy value, and then offset pagination by that. Seems kinda hoaky, though. Google turns up "sequence," an Oracle tech, that seems perfect for this. But that's not going to help me.Cheers-b |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-12-29 : 08:55:47
|
well i'd say easiest is to have an id for each query that you associate with the query results.i int primary, queryid int, result columns...Go with the flow & have fun! Else fight the flow  |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-12-29 : 15:31:18
|
| Well, yeah, I got that part :) The issue is keeping the result columns in the right order :)It does seem like just an identity on the result cache table coupled with storing the first/last identity values, and then computing pagination from there.Cheers-b |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-12-30 : 07:02:18
|
| well identity takes care of order, no?queryid takes care of telling you which resluts come from which query.Go with the flow & have fun! Else fight the flow |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-30 : 07:33:15
|
| interesting issue, aiken. Do your users have the option to see results in varying orders? (sort by different columns, and either asc or desc) If so, wouldn't you need to cache a heck of a lot of data?Be One with the OptimizerTG |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-12-31 : 21:34:02
|
| Well, yeah, identity takes care of order, but the issue is having a lot of cached queries share the same table. So query 1 will be identity values (for instance) 1-10000. Then query 2 will be identity values 10001 - 20000. It would sure be easier if there was a way to have an identity type column but which was only unique to the queryID (which I believe oracle's "sequence" allows).TG, we do let users sort by different columns, including asc/desc. And yeah, this will be caching a lot of data. On the bright side, if you want to re-sort a given resultset by a different column, that query can use the cache table as input rather than having to run the expensive stuff that computes some of the sort-by data (distance, for example).My theory is that it will be less work for the DB to do the inserts into the cache table than to totally re-run the query for each subsequent set of page numbers.Cheers-b |
|
|
|
|
Local identities? Or something like that?