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?

Post removed (X-No-Archive: yes)

"Racter" <bknauss [at] gmail.com> wrote in message
news:1171476183.885962.29770 [at] 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.

On 14 Feb, 18:32, "Steve" <no.... [at] example.com> wrote:
> "Racter" <bkna... [at] gmail.com> wrote in message
>
> news:1171476183.885962.29770 [at] 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.

yeah something's up, that's not much data. do have a spare few MB -
maybe you could copy the table to memory, which would speed things up
a bit, depending on a few factors. I have a postcode table with a
million of them in there - mysql takes 0.005 secs to crunch through
searching for
field LIKE '%crab street%'
- and return 2000 rows.
It could be the way you are returning the data that takes time as
well, not just the lookup - you arent asking the database to do work
your application layer could be doing are you, to format the results,
or perform maths on the rows, things like that can cause the data
table to be read multiple times - to order, etc.. the resulting set.
php offloads its maths onto c libraries so its practically as fast as
native c

On Feb 14, 10:32 am, "Steve" <no.... [at] example.com> wrote:
> "Racter" <bkna... [at] gmail.com> wrote in message
>
> news:1171476183.885962.29770 [at] 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...

On Feb 14, 11:35 pm, "Racter" <bkna... [at] gmail.com> wrote:
> On Feb 14, 10:32 am, "Steve" <no.... [at] example.com> wrote:
>
>
>
> > "Racter" <bkna... [at] gmail.com> wrote in message
>
> >news:1171476183.885962.29770 [at] 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...

hmm that is indeed a lot of rendering to do,
is it coming straight back as html, is the page XHTML sent as XHTML or
tag soup (xhtml sent as html) Firefox can be quite memory hungry - you
are sure its not just an issue with low memory on your PC after the
first query which prevents the rendering of the 2nd.
It doesn't seem like to be a table locking problem, or a slow query or
memory problem... although it /could/ be a session problem, that's not
usual with a php app (assuming it /is/ php)

so when you say it locks do you mean you are looking at the db server
and you can see only one query is going ahead, that the connection is
not being acknowledged, or are you referring to the browser waiting
period.
Personally I think unless you try on two separate machines there are
too many factors here, but the fact that IE and firefox play nice /
could/ suggest a session issue, but my money's still on a CPU/mem
issue with firefox on a single machine - although of course its just a
guess at the mo.