Re: Result Set over Network Question

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: "Ronald Hahn, DOCFOCUS INC(dot)" <rhahn(at)docfocus(dot)ca>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Result Set over Network Question
Date: 2012-05-03 15:00:59
Message-ID: CAHyXU0wdmbGNQgqwBVZR5UY6Nmb3-L6LSi+XA+gjMtqMFn-3Ww@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Mon, Apr 30, 2012 at 1:32 PM, Ronald Hahn, DOCFOCUS INC.
<rhahn(at)docfocus(dot)ca> wrote:
> Hi,
>     We have recently switch our product from MS SQL 2000 to Postgresql
> 9.0.7. We have tuned the searches and indexes so that they are very close
> (often better) to what sql2k was giving us.  We are noticing some
> differences now in the time it takes for the result set to make it back to
> the client and would like some help finding out why.
>
> What we see on the PG side is that if we run:
>     Select SomeInt32 from someTable where something Limit 1
> It consistently returns the results "instantaneously"  after the fetch
> time.  If we run the same select but ask for more data the fetch time stays
> the same but the row takes longer to come back.  Bringing back 400 bytes
> takes 1-2 s but bringing back 866 bytes takes 9 - 11 s.
>
> We went to the SQL2k server (On the same hardware) and ran the selects
> again. When bringing back on an int32 PG was faster with the fetch and the
> row coming back in 1-5 ms and SQL2k coming back in 500-700 ms. This tells me
> that the problem is not related to PG index or Disk.  When bringing back 400
> bytes PG fetch time would be 1-2 ms but the results would take 2-3 s but
> SQL2k would it bring back in 700-900 ms. Working with 866 bytes, PG fetch
> time is 1-3 ms with the results coming back in 9 - 11 s and SQL2k bringing
> the results back in 2-3 s.
>
> The head to head test was run in Aqua Data Studio 10.0.8 and ODBC driver
> 9.0.3.10. The same slow down happens when use PGadminIII. The differnces in
> time to not occure when running on the pg/sql server computer so I think
> there is a network component to this.

to rule out network just do:
create temp table scratch as select <your query>...

if it's a lot faster, then you have a probable network issue.

merlin

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Ronald Hahn, DOCFOCUS INC. 2012-05-03 15:28:44 Re: Result Set over Network Question
Previous Message Claudio Freire 2012-05-03 14:28:18 Re: Result Set over Network Question