DBD versus dblist
I'm a programmer at the US Courts, working on the case management software that is used by most of the federal criminal, civil, and bankruptcy courts. We use a heavily modified version of dblist, originally written by David Snyder, to extract data from our Informix databases. It is a compiled c program using esql libraries. Yesterday, I downloaded DBD::Informix module and compiled it without a hitch, and it works quite well at getting data from the database. However, it takes about twice as long to run as dblist does. When I do my timing test, it is while I am running Perl, so my script has already compiled. I have tried running for one row of data, for 60,000 rows of data, for one field, for a 60 fields, and DBI is always slower. I find this quite surprising. My Informix DBA, who is quite sharp, also finds this surprising. Do you? Do you have any suggestions for us? Thanks, Jacob Davenport
Jacob Davenport wrote: > I'm a programmer at the US Courts, working on the case management > software that is used by most of the federal criminal, civil, and > bankruptcy courts. > > We use a heavily modified version of dblist, originally written by David > Snyder, to extract data from our Informix databases. It is a compiled c > program using esql libraries. OK - but the version in the IIUG Software archive is a C-ISAM program appropriate for use with SE. Have you converted it into an ESQL/C program? If you're comparing a C-ISAM program with Perl + DBI + DBD::Informix, then I am not surprised that dblist can outrun Perl. There are a number of big customers who still use C-ISAM for its out and out speed advantage over any SQL-based database. > Yesterday, I downloaded DBD::Informix > module and compiled it without a hitch, and it works quite well at > getting data from the database. However, it takes about twice as long > to run as dblist does. When I do my timing test, it is while I am > running Perl, so my script has already compiled. I have tried running > for one row of data, for 60,000 rows of data, for one field, for a 60 > fields, and DBI is always slower. > > I find this quite surprising. My Informix DBA, who is quite sharp, also > finds this surprising. Do you? Do you have any suggestions for us? Well, can you identify your hardware and your Informix database - product and version. If dblist is indeed an ESQL/C program (I've no reason to think otherwise given your say so), which version of ESQL/C are you using. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Jonathan Leffler wrote: > Jacob Davenport wrote: > >> I'm a programmer at the US Courts, working on the case management >> software that is used by most of the federal criminal, civil, and >> bankruptcy courts. >> >> We use a heavily modified version of dblist, originally written by David >> Snyder, to extract data from our Informix databases. It is a compiled c >> program using esql libraries. > > > OK - but the version in the IIUG Software archive is a C-ISAM program > appropriate for use with SE. Have you converted it into an ESQL/C > program? If you're comparing a C-ISAM program with Perl + DBI + > DBD::Informix, then I am not surprised that dblist can outrun Perl. OK - I love my browser! It showed up dblist_c instead of dblist when I asked it to search for dblist. Hmmmph! The program dblist (as distinct from dblist_c) is an ESQL/C program. Mea culpa. > There are a number of big customers who still use C-ISAM for its out and > out speed advantage over any SQL-based database. > >> Yesterday, I downloaded DBD::Informix >> module and compiled it without a hitch, and it works quite well at >> getting data from the database. However, it takes about twice as long >> to run as dblist does. When I do my timing test, it is while I am >> running Perl, so my script has already compiled. I have tried running >> for one row of data, for 60,000 rows of data, for one field, for a 60 >> fields, and DBI is always slower. >> >> I find this quite surprising. My Informix DBA, who is quite sharp, also >> finds this surprising. Do you? Do you have any suggestions for us? > > > Well, can you identify your hardware and your Informix database - > product and version. If dblist is indeed an ESQL/C program (I've no > reason to think otherwise given your say so), which version of ESQL/C > are you using. The real dblist program is a simple, compact ESQL/C program. DBD::Informix has much the same core code, but also has to deal with some idiosyncracies of the DBI spec, and then gets a layer of Perl wrapped around the top. No, I'm not very surprised that a custom program like dblist can outperform DBD::Informix. The code looks slim enough that dblist probably outperforms SQLCMD on raw speed - but SQLCMD does a whole heap of things that DBLIST cannot do. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/