Re: 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(dot)gauthier(at)intel(dot)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.