Posted by shimmyshack on 02/14/07 21:16

On 14 Feb, 18:32, "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.

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

[Back to original message]