Posted by Racter on 02/14/07 23:35

On Feb 14, 10:32 am, "Steve" <no....@example.com> wrote:
> "Racter" <bkna...@gmail.com> wrote in message
>
> news:1171476183.885962.29770@v45g2000cwv.googlegroups.com...
> |
> | So I have an application that does massive result sets, typically 3 to
> | 20k rows... someone starts a search, it might take 30-45 seconds
> | before the database comes back, then awhile for it to actually pump
> | the data across...
> |
> | Now while someone is doing that, they open up another window to do
> | another search at the same time, or even see the main index page,
> | which is data driven, and they are blocked... by the pending query.
> |
> | I'm assuming there is a way to get around this? I tried the
> | PGSQL_CONNECT_FORCE_NEW thing, but that dosnt seem to solve the
> | blocking problem at all.
> |
> | Ideas?
>
> it's to do with table locking. you need to specify that a select should not
> lock the tables being queries. in addition, your other competing query can
> perform a 'dirty read' on the table as well.
>
> btw, if you are returning MINISCULE result sets between 3 and 20K and it's
> taking that long for the db to return data, i'd seriously consider rewriting
> your query, optimizing your tables, and analyze it's performance...that, or
> getting a better db.

I refer to it as huge, because 20,000 records of anything being
displayed on a single web page tends to be a large number. The query
is slow, but thats a legacy schema problem that I cant solve at the
moment. The problem I need to solve is how to get it to treat each
page the way it currently treats each session... if I have IE and
Firefox both open, I can do queries on both... if I have two windows
of firefox open (one session) then I can only do a single query at a
time, it blocks...

[Back to original message]