Result Set over Network Question

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.

I know that as you bring back more data it takes longer but why is there
such a difference in the time it takes PG to send the data compared to
the time it takes sql2k to send it?

Any thoughts and suggestions are very much appreciated
Thanks
Ron
--

*Ronald Hahn* , CCP, CIPS Member
*DOCFOCUS INC.*
Suite 103, 17505 - 107 Avenue,
Edmonton, Alberta T5S 1E5
Phone: 780.444.5407
Toll Free: 800.661.2727 (ext 6)
Fax: 780.444.5409
Email: rhahn(at)docfocus(dot)ca
Support:dfisupport(at)docfocus(dot)ca <mailto:dfisupport(at)docfocus(dot)ca>
DOCFOCUS.ca <http://docfocus.ca/>

There are 2 kinds of people in the world.
Those who can extrapolate from incomplete data

On Mon, Apr 30, 2012 at 3:32 PM, Ronald Hahn, DOCFOCUS INC.
<rhahn(at)docfocus(dot)ca> wrote:
> 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.

I think the opposite. I'm thinking it's quite probable that it's disk
access the one killing you. Remember, two different database systems
means two different access patterns.

To figure it out, you have to provide a lot more information on your
system and your query. Check out how to post "Slow Query Questions"
[0]. Only after getting all that information the people of the list
will be able to have a clue as to what your problem is.

[0] http://wiki.postgresql.org/wiki/SlowQueryQuestions

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

After some testing using wiershark (poor mans profiler) to see what was
going on with the network I found that it was the tools I've been using.
Both Aqua and PGadminIII have a large overhead per column to get the
meta data. MSSQL sends that data upfront so the impact isn't as bad. I'm
not sure if it's a limitation of the pgsql protocol vs tds or a
limitation of Aqua or a combination of both. At any rate it turns out
not to be part of the problem I'm having with my software stalling out
so I'm back to Square one with my problem.

Thanks,
Ron

On Thu, May 3, 2012 at 10:28 AM, Ronald Hahn, DOCFOCUS INC.
<rhahn(at)docfocus(dot)ca> wrote:
> After some testing using wiershark (poor mans profiler) to see what was
> going on with the network I found that it was the tools I've been using.
> Both Aqua and PGadminIII have a large overhead per column to get the meta
> data. MSSQL sends that data upfront so the impact isn't as bad. I'm not sure
> if it's a limitation of the pgsql protocol vs tds or a limitation of Aqua or
> a combination of both. At any rate it turns out not to be part of the
> problem I'm having with my software stalling out so I'm back to Square one
> with my problem.

ok, let's figure out what the issue is then. first, let's make sure
it isn't the server that's stalling: configure
log_min_duration_statement with an appropriate value so you start
catching queries that are taking longer then you think the should be.
also some client side logging directly wrapping the SQL invocation
couldn't hurt. is your application jdbc?

merlin

On Thu, May 3, 2012 at 5:40 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Thu, May 3, 2012 at 10:28 AM, Ronald Hahn, DOCFOCUS INC.
> <rhahn(at)docfocus(dot)ca> wrote:
>> After some testing using wiershark (poor mans profiler) to see what was
>> going on with the network I found that it was the tools I've been using.
>> Both Aqua and PGadminIII have a large overhead per column to get the meta
>> data. MSSQL sends that data upfront so the impact isn't as bad. I'm not sure
>> if it's a limitation of the pgsql protocol vs tds or a limitation of Aqua or
>> a combination of both. At any rate it turns out not to be part of the
>> problem I'm having with my software stalling out so I'm back to Square one
>> with my problem.

So, Ronald, are you saying the different approach to meta data
transfer is _not_ the issue?

> ok, let's figure out what the issue is then.  first, let's make sure
> it isn't the server that's stalling: configure
> log_min_duration_statement with an appropriate value so you start
> catching queries that are taking longer then you think the should be.
>  also some client side logging directly wrapping the SQL invocation
> couldn't hurt.   is your application jdbc?

Ronald said ODBC in his first posting. But since ADS seems to support
JDBC as well trying that might be a good test to get another data
point. Alternative tools for JDBC tests:

http://squirrel-sql.sourceforge.net/
http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

Using the PG client remotely with "\timing on" might be an even better idea.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Robert Klemme, 07.05.2012 14:03:
> Alternative tools for JDBC tests:
>
> http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

SQL Developer does not support PostgreSQL

This page:

http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

also lists several JDBC based tools.

Thomas

On Mon, May 7, 2012 at 2:11 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> Robert Klemme, 07.05.2012 14:03:
>>
>> Alternative tools for JDBC tests:
>>
>> http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
>
> SQL Developer does not support PostgreSQL

Last time I checked (quite a while ago) you could use arbitrary JDBC
drivers. There's also
http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/intro.htm#sthref306

And this seems to indicate that it's still the case: "[...] or another
third-party driver. [...]
JDBC URL (Other Third Party Driver): URL for connecting directly from
Java to the database; overrides any other connection type
specification."
http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/dialogs.htm#BACDGCIA

I assume Oracle is not interested in aggressively advertizing this
feature though.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Robert Klemme, 07.05.2012 15:44:
>>> http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
>>
>> SQL Developer does not support PostgreSQL
>
> Last time I checked (quite a while ago) you could use arbitrary JDBC
> drivers. There's also
> http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/intro.htm#sthref306
>
> And this seems to indicate that it's still the case: "[...] or another
> third-party driver. [...]
> JDBC URL (Other Third Party Driver): URL for connecting directly from
> Java to the database; overrides any other connection type
> specification."
> http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/dialogs.htm#BACDGCIA
>
> I assume Oracle is not interested in aggressively advertizing this
> feature though.

That seems to be a documentation bug.
I tried it, and it definitely does not work (or I am missing something).

Their release notes at: http://www.oracle.com/technetwork/developer-tools/sql-developer/sqldev31-ea-relnotes-487612.html

state:

Third Party Databases

SQL Developer supports IBM DB2 UDB LUW , Microsoft SQL Server and Microsoft Access, MySQL, Sybase Adaptive Server and Teradata.
See Supported Platforms for details on all third party database releases supported.

Regards
Thomas

On Mon, May 7, 2012 at 4:25 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> That seems to be a documentation bug.
> I tried it, and it definitely does not work (or I am missing something).

Apparently I am the one who is missing something. :-)

> Their release notes at:
> http://www.oracle.com/technetwork/developer-tools/sql-developer/sqldev31-ea-relnotes-487612.html
>
> state:
>
> Third Party Databases
>
>    SQL Developer supports IBM DB2 UDB LUW , Microsoft SQL Server and
> Microsoft Access, MySQL, Sybase Adaptive Server and Teradata.
>    See Supported Platforms for details on all third party database releases
> supported.

Right you are, Thomas! Thank you! Sorry for the confusion.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/