Обсуждение: DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows)

On 06/24/2015 06:44 AM, Gauthier, Dave wrote:
> Hi:  I'm trying to get a 10,000 ft understanding of the difference in DB
> access speeds for two different scenarios...
>
> Scenario 1:     Apps are on linux.  PG DB is on linux (different server
> than apps)
>
> Scenario 2:     Apps are on linux.  MSSql DB is on Windows (obviously a
> different server)
>
> The apps are typically perl scripts using Perl DBI.
>
> I'm thinking that since perl has a DBD driver for PG, that would be
> faster than going through ODBC to get to the MsSQL DB, but I'm not sure
> about that.
>
> Any insights/guesses ?

Not without more information.

1) The same schema, data on both DB servers?

2) Are they the same apps?

3) What are the apps doing?

>
> Thanks in Advance.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 6/24/2015 8:44 AM, Gauthier, Dave wrote:
> Hi:  I'm trying to get a 10,000 ft understanding of the difference in DB
> access speeds for two different scenarios...
>
> Scenario 1:     Apps are on linux.  PG DB is on linux (different server
> than apps)
>
> Scenario 2:     Apps are on linux.  MSSql DB is on Windows (obviously a
> different server)
>
> The apps are typically perl scripts using Perl DBI.
>
> I'm thinking that since perl has a DBD driver for PG, that would be
> faster than going through ODBC to get to the MsSQL DB, but I'm not sure
> about that.
>
> Any insights/guesses ?
>
> Thanks in Advance.
>

I think it'll depend on your usage.

If you fire off one really big analytic sql that chews on a huge table
and does stats and counts, then odbc vs native will make no difference.

If you fire off lots and lots of small querys that hit an index and
return very fast, then native is the way to go.

-Andy

>>1) The same schema, data on both DB servers?
Yes, identical
>>2) Are they the same apps?
Yes, the same.  The only diff is the db connect.
>>3) What are the apps doing?
The vast, vast majority are small queries, often run inside large programming loops.

>>If you fire off lots and lots of small querys that hit an index and return very fast, then native is the way to go.
By this are you implying that there is more overhead using ODBC vs Perl DBI-DBD ?   If so, any guess, percentage wise,
ofhow much slower it would be? 50% slower?  2x?  Other? 

Some (who really don't understand DB mechanics, or even know what odbc is) are telling me that there is no difference.
Isay there is, but this is an intuitive response.  I'm trying to tap more experienced people for something more solid. 




-----Original Message-----
From: Andy Colson [mailto:andy@squeakycode.net]
Sent: Wednesday, June 24, 2015 10:10 AM
To: Gauthier, Dave; Postgres General
Subject: Re: [GENERAL] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows)

On 6/24/2015 8:44 AM, Gauthier, Dave wrote:
> Hi:  I'm trying to get a 10,000 ft understanding of the difference in
> DB access speeds for two different scenarios...
>
> Scenario 1:     Apps are on linux.  PG DB is on linux (different server
> than apps)
>
> Scenario 2:     Apps are on linux.  MSSql DB is on Windows (obviously a
> different server)
>
> The apps are typically perl scripts using Perl DBI.
>
> I'm thinking that since perl has a DBD driver for PG, that would be
> faster than going through ODBC to get to the MsSQL DB, but I'm not
> sure about that.
>
> Any insights/guesses ?
>
> Thanks in Advance.
>

I think it'll depend on your usage.

If you fire off one really big analytic sql that chews on a huge table and does stats and counts, then odbc vs native
willmake no difference. 

If you fire off lots and lots of small querys that hit an index and return very fast, then native is the way to go.

-Andy

> -----Original Message-----
> From: Andy Colson [mailto:andy@squeakycode.net]
> Sent: Wednesday, June 24, 2015 10:10 AM
> To: Gauthier, Dave; Postgres General
> Subject: Re: [GENERAL] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows)
>
> On 6/24/2015 8:44 AM, Gauthier, Dave wrote:
>> Hi:  I'm trying to get a 10,000 ft understanding of the difference in
>> DB access speeds for two different scenarios...
>>
>> Scenario 1:     Apps are on linux.  PG DB is on linux (different server
>> than apps)
>>
>> Scenario 2:     Apps are on linux.  MSSql DB is on Windows (obviously a
>> different server)
>>
>> The apps are typically perl scripts using Perl DBI.
>>
>> I'm thinking that since perl has a DBD driver for PG, that would be
>> faster than going through ODBC to get to the MsSQL DB, but I'm not
>> sure about that.
>>
>> Any insights/guesses ?
>>
>> Thanks in Advance.
>>
>
> I think it'll depend on your usage.
>
> If you fire off one really big analytic sql that chews on a huge table and does stats and counts, then odbc vs native
willmake no difference. 
>
> If you fire off lots and lots of small querys that hit an index and return very fast, then native is the way to go.
>
> -Andy
>
>
>
On 6/24/2015 10:13 AM, Gauthier, Dave wrote:>
 >>> 1) The same schema, data on both DB servers?
 > Yes, identical
 >>> 2) Are they the same apps?
 > Yes, the same.  The only diff is the db connect.
 >>> 3) What are the apps doing?
 > The vast, vast majority are small queries, often run inside large
programming loops.
 >
 >>> If you fire off lots and lots of small querys that hit an index and
return very fast, then native is the way to go.
 > By this are you implying that there is more overhead using ODBC vs
Perl DBI-DBD ?   If so, any guess, percentage wise, of how much slower
it would be? 50% slower?  2x?  Other?
 >
 > Some (who really don't understand DB mechanics, or even know what
odbc is) are telling me that there is no difference.  I say there is,
but this is an intuitive response.  I'm trying to tap more experienced
people for something more solid.
 >
 >
 >
 >


I am totally guessing!  I have never benchmarked odbc vs native.

But you have bigger problems.  You have win vs linux, pg vs mssql, odbc
vs native to contend with.

Benchmarking a bunch of queries to one vs the other wont tell you which
(os, db, method) is the slow part.

To only measure odbc vs native you'd need everything else to be the same.

I'd recommend benchmarking you app to each db and just see which is
faster.  You wont know why its faster but do you really care?

-Andy

Hi Dave:

On Wed, Jun 24, 2015 at 3:44 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> Hi:  I'm trying to get a 10,000 ft understanding of the difference in DB
> access speeds for two different scenarios...
> Scenario 1:     Apps are on linux.  PG DB is on linux (different server than
> apps)
>
> Scenario 2:     Apps are on linux.  MSSql DB is on Windows (obviously a
> different server)
>
> The apps are typically perl scripts using Perl DBI.
> I'm thinking that since perl has a DBD driver for PG, that would be faster
> than going through ODBC to get to the MsSQL DB, but I'm not sure about that.
> Any insights/guesses ?

From 10k ft, same speed ( variations due to lack of precision in the
definition of the problem are going to dwarf everything else ).

I assume apps are the same on both, and same network distance. If you
use DBI for both you can discount it. If you use DBI you must use DBD.
DBD::Pg is quite efficient in my measurements, but for the windows
part I do not know what you are using. I'm using Mssql from linux
using freetds ( DBD::Sybase, built with freetds, which is equivalent
to DBD::Pg built with libpq ) and it goes at the appropiate speed ( I
mean, nothing big is going to be gained, optimization time will be
bigger than savings ). You say you use ODBC, but where exactly? You
use ODBC on linux going to mssql using ¿which library? and DBD::ODBC,
or you use remote odbc and an ODBC driver on windows?

In any way, speed differences in your queries may be much bigger than
requester speeds, unless you have a very complex path ( dbd::proxy
going to a windows perl dbiproxy going with DBD::odbc to local mssql ?
). You should time a couple of your queries in real world condition,
and test some loops of null queries ( like select 1 and the mssql
equivalent in autocommit and no autocommit mode for both ), either of
them can win.

Also, someone has already told you that for really fast queries native
may make a difference. It may, but you have to define 'non native'
better. In very fast queries requester differences may be dwarfed by
network roundtrips, and if this is a problem to you, you should look
for optimizing the common path firsts, things like how many roundtrips
each PROTOCOL needs for the small query and other similar. You should
measure before. Requester is not normally going to be your big
problem.

Francisco Olarte.

OK, so there is a DBD for sybase that you are leveraging to interface with the MSSql DB.  And that would be roughly
equivilentto the DBD for PG in terms of performance.  I mentioned odbc because I thought that was the common thread to
useto bridge perl to the MSSql DB.  ANd I saw that has introducing more translation steps (slower).
 

Yes, everything else is a constant between the two (network distance, server size/mem, etc...)

I think this answers my question.  Thanks everyone. 

-----Original Message-----
From: Francisco Olarte [mailto:folarte@peoplecall.com] 
Sent: Wednesday, June 24, 2015 12:50 PM
To: Gauthier, Dave
Cc: Postgres General
Subject: Re: [GENERAL] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows)

Hi Dave:

On Wed, Jun 24, 2015 at 3:44 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> Hi:  I'm trying to get a 10,000 ft understanding of the difference in 
> DB access speeds for two different scenarios...
> Scenario 1:     Apps are on linux.  PG DB is on linux (different server than
> apps)
>
> Scenario 2:     Apps are on linux.  MSSql DB is on Windows (obviously a
> different server)
>
> The apps are typically perl scripts using Perl DBI.
> I'm thinking that since perl has a DBD driver for PG, that would be 
> faster than going through ODBC to get to the MsSQL DB, but I'm not sure about that.
> Any insights/guesses ?

From 10k ft, same speed ( variations due to lack of precision in the definition of the problem are going to dwarf
everythingelse ).
 

I assume apps are the same on both, and same network distance. If you use DBI for both you can discount it. If you use
DBIyou must use DBD.
 
DBD::Pg is quite efficient in my measurements, but for the windows part I do not know what you are using. I'm using
Mssqlfrom linux using freetds ( DBD::Sybase, built with freetds, which is equivalent to DBD::Pg built with libpq ) and
itgoes at the appropiate speed ( I mean, nothing big is going to be gained, optimization time will be bigger than
savings). You say you use ODBC, but where exactly? You use ODBC on linux going to mssql using ¿which library? and
DBD::ODBC,or you use remote odbc and an ODBC driver on windows?
 

In any way, speed differences in your queries may be much bigger than requester speeds, unless you have a very complex
path( dbd::proxy going to a windows perl dbiproxy going with DBD::odbc to local mssql ?
 
). You should time a couple of your queries in real world condition, and test some loops of null queries ( like select
1and the mssql equivalent in autocommit and no autocommit mode for both ), either of them can win.
 

Also, someone has already told you that for really fast queries native may make a difference. It may, but you have to
define'non native'
 
better. In very fast queries requester differences may be dwarfed by network roundtrips, and if this is a problem to
you,you should look for optimizing the common path firsts, things like how many roundtrips each PROTOCOL needs for the
smallquery and other similar. You should measure before. Requester is not normally going to be your big problem.
 

Francisco Olarte.