Jeremiah Jahn wrote:

On Thu, 2005-03-03 at 11:46 -0600, John A Meinel wrote:


Not really, about 2% of the returned rows are thrown away for security
reasons based on the current user, security groups they belong to and
different flags in the data itself. So the count for this is generated
on the fly needed for pagination in the app which expresses the total
number of finds, but only displays 40 of them. If any one knows a way to
determine the total number of matches without needing to iterate through
them using jdbc, I'm all ears as this would save me huge amounts of time
and limit/offset would become an option.

Well, what is wrong with "select count(*) from <the query I would have
Are you saying 2% are thrown away, or only 2% are kept?
Is this being done at the client side? Is there a way to incorporate the
security info into the database, so that the query actually only returns
the rows you care about? That seems like it would be a decent way to
speed it up, if you can restrict the number of rows that it needs to
look at.

There are other alternatives, such as materialized views, or temp
tables, where you select into the temp table the rows that the user
would request, and then you generate limit/offset from that. The first
query would be a little slow, since it would get all the rows, but all
subsequent accesses for that user could be really fast.

The other possibility is to do "limit 200", and then in your list of
pages, you could have:
1, 2, 3, 4, 5, ...
This means that you don't have to worry about getting 10,000 entries,
which probably isn't really useful for the user anyway, and you can
still break things into 40 entry pages, just 200 entries at a time.


Attachment: signature.asc
Description: OpenPGP digital signature

Reply via email to