[asterisk-dev] Postgres Realtime driver

[Glenn B. Lawler]

>> We use PostgreSQL with an ODBC driver for commercial apps (non- *) and
>> have found the performance to be fine. I don't know about PostgreSQL
>> specifically, but don't assume that ODBC means "extra layer". A
>> great many
>> of the ODBC drivers out there are single layer and therefore have
>> no more
>> overhead than the "native" method of access. I know MS SQL Server
>> is that
>> way. MS says that ODBC access to SQL Server is "at least" as fast as
>> DBLib, and our benchmarks bear that out. ODBC is, in fact, faster than
>> DBLib for some operations. The big advantage to ODBC is the
>> software you
>> write can be made to work with any RDBMS backend that has an ODBC
>> driver
>> (which is about all of them) without ANY change to the software.
>> Also, a
>> lot of people think of ODBC as an MS thing when in fact it is just an
>> implementation of the X/Open CLI (Call Level Interface) specification
>> which is determined by an independent standards body.
>
>Well, that's the marketing glorified message. Our experience with the
>FreeTDS problems show that regardless of ODBC, we have to adopt
>to the underlying layer anyway and have db-specific code in there.
>
>/O

There's really not much in the way of marketing behind _that_ message. The 
people who sell the DBMS would love for you to use their proprietary static 
or dynamically linked way of communicating with their database. They know 
the only way you will move to one of their competitors is if you are 
willing to do major code revisions (and testing).

The real winners when you use ODBC are the customers and the developers. 
The only reason the DBMS vendors offer good ODBC drivers is competition. 
Very widely used products, like Crystal Reports and others, connect to 
databases via ODBC, so if you have poor ODBC driver, customers won't want 
your DBMS because the reports will run too slow.

>we have to adopt
>to the underlying layer anyway and have db-specific code in there.

I am guessing you are talking about the SQL code that you are embedding in 
your application. Think a minute. SQL is just text. Is there any reason it 
must be embedded in the application? With just a little skill you can come 
up with a way for your program to load the SQL code that it uses from some 
kind of a configuration file, or from the database itself. Many databases 
that provide stored procedure do, in fact, store the source for the stored 
procedure in the database itself. If you do not want to others to see your 
SQL code, encryption (if you use a file) or database access rights (if you 
store in the database) will handle that.

The other thing you may not be aware of is ODBC escapes. These allow you to 
use escape sequences in your SQL code that will automatically be translated 
by the driver into the syntax needed for the backend. Things like dates and 
times can be escaped so they will work on any backend. ANSI joins can be 
coded with ODBC escapes so that backends that only support some proprietary 
syntax for outer joins can be supported (fortunately there aren't many 
remaining databases that don't support ANSI joins). For everything else, 
you can write different versions of the SQL code for different versions of 
the DBMS and have different versions of the configuration files containing 
the SQL.

Glenn Lawler

-----Original Message-----
From:	Olle E Johansson [SMTP:oej at edvina.net]
Sent:	Tuesday, April 04, 2006 3:41 PM
To:	Asterisk Developers Mailing List
Subject:	Re: [asterisk-dev] Postgres Realtime driver


4 apr 2006 kl. 21.00 skrev gblawler at incodesystems.com:

>> What's the performance impact of ODBC vs native?  (Is that extra
>> layer
>> negligible for most common tasks?)  Has anyone ever noticed a
>> difference
>> or ran any benchmarks?
>
> We use PostgreSQL with an ODBC driver for commercial apps (non- *) and
> have found the performance to be fine. I don't know about PostgreSQL
> specifically, but don't assume that ODBC means "extra layer". A
> great many
> of the ODBC drivers out there are single layer and therefore have
> no more
> overhead than the "native" method of access. I know MS SQL Server
> is that
> way. MS says that ODBC access to SQL Server is "at least" as fast as
> DBLib, and our benchmarks bear that out. ODBC is, in fact, faster than
> DBLib for some operations. The big advantage to ODBC is the
> software you
> write can be made to work with any RDBMS backend that has an ODBC
> driver
> (which is about all of them) without ANY change to the software.
> Also, a
> lot of people think of ODBC as an MS thing when in fact it is just an
> implementation of the X/Open CLI (Call Level Interface) specification
> which is determined by an independent standards body.

Well, that's the marketing glorified message. Our experience with the
FreeTDS problems show that regardless of ODBC, we have to adopt
to the underlying layer anyway and have db-specific code in there.

/O
_______________________________________________
--Bandwidth and Colocation provided by Easynews.com --

asterisk-dev mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-dev