On Jan 26, 2005, at 5:36 AM, Leeuw van der, Tim wrote:
What you could do is create a table containing all the fields from your SELECT, plus a per-session unique ID. Then you can store the query results in there, and use SELECT with OFFSET / LIMIT on that table. The WHERE clause for this temp-results table only needs to contain the per-session unique id.
This is what I do, but I use two columns for indexing the original query, a user_id (not session-id) and an index to the "query_id" that is unique within user. This "query_id" is a foreign key to another table that describes the query (often just a name). I allow the user only a fixed number of "stored" queries and recycle after hitting the maximum. You can timestamp your queries so that when you recycle you drop the oldest one first. If you don't need multiple stored query results, then using the user_id is probably adequate (assuming the user is not logged on in several locations simultaneously).
This of course gives you a new problem: cleaning stale data out of the temp-results table. And another new problem is that users will not see new data appear on their screen until somehow the query is re-run (... but that might even be desirable, actually, depending on how your users do their work and what their work is).
See above. The query refresh issue remains.
And of course better performance cannot be guaranteed until you try it.
For the standard operating procedure of perform query===>view results, I have found this to be a nice system. The user is accustomed to queries taking a bit of time to perform, but then wants to be able to manipulate and view data rather quickly; this paradigm is pretty well served by making a separate table of results, particularly if the original query is costly.
Would such a scheme give you any hope of improved performance, or would it be too much of a nightmare?
This question still applies....
From: [EMAIL PROTECTED] on behalf of Andrei Bintintan
Sent: Wed 1/26/2005 11:11 AM
To: [EMAIL PROTECTED]; Greg Stark
Cc: Richard Huxton; email@example.com; firstname.lastname@example.org
Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance???
The problems still stays open.
The thing is that I have about 20 - 30 clients that are using that SQL query
where the offset and limit are involved. So, I cannot create a temp table,
because that means that I'll have to make a temp table for each session...
which is a very bad ideea. Cursors somehow the same. In my application the
Where conditions can be very different for each user(session) apart.
The only solution that I see in the moment is to work at the query, or to
write a more complex where function to limit the results output. So no
replace for Offset/Limit.
Best regards, Andy.
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])