Re: DBD::Pg timings
Hey All, I'd like to start off by saying that I've happily resolved my insert problems. The conclusion was extra baggage in un-needed constraints and triggers, especially triggers. So, thanks everyone (especially Tom Lane) for helping me figure this out. David Duff [EMAIL PROTECTED] writes: i timed inserts of 100k records using the following three techniques: 1. row-at-a-time insert using a prepared insert statement. I timed 250k records only using 1. results: 1. 1053 records per second - 647 records per second After I figured out that most of my problem was a trigger, and whittled it down to the bare essentials. general conclusion: Be careful of your triggers. I removed a foreign key constraint from my table (kept the column, removed the constraint as it was already satisfied by another trigger I didn't need two triggers for the same job). I also removed one of the two inserts from the trigger. details: this was postgres running on solaris. mine was a dual 1.8G Athlon box running debian linux. table had six fields - three varchar(32)'s, two integers, and a float. string values inserted were short - ~5-8 bytes. there was one unique index defined on the table. table had 21 fields but I was only inserting into the 5 not null fields: 2 varchar(128)'s, 2 name's, and a bigint. There are two unique indices on the table. autocommit was off. a single $dbh-commit was done in each test yup. Also, I had a little extra baggage: * All inserts were done in a loop reading values out of a perl 2D array. Before each insert the primary key value was selected out of a sequence in the DB. * Then there was the trigger on INSERT for each row that inserted a timestamp into an audit table. So given my extra unique index, the select on the sequence and the trigger doing an extra INSERT, I'm pretty happy that I'm seeing 60% of what you were getting. Conclusions: - watch your triggers. This now seems idiotically obvious now that I've gone through all this (and I feel dumb for potentially wasting a lot of people's time), but I was getting what I thought was reasonable performance for small inserts (1000 rows) but when I tried inserting something really big for a change everything seemed to fall apart. I'd just like to repeat: Thanks everyone for helping me figure this out. Cheers, jas.
Trying to Install DBI Bundle
Hi - I am trying to install the Bundle::DBI on my server using Telnet at the cpan prompt, and it is returning some errors. I have no idea how to proceed from here. I am simply connecting, typing in vcpan, then at the cpan prompt ''install Bundle::DBI'' and it runs for a while - I get messages like: Net:FTP: Bad hostname 'mirrors.phihost.com' at /usr/home/picks/usr/local/lib/site_perl/LWP/Protocol/fpp.pm line 83 Then it fails tests, like t/config FAILED tests 2-5 Failed 4-5 tests, 20% okay The remainder of the install session is below - any idea what I'm doing wrong? Is there another way/command to install the DBI module? Thanks - Monica Soberman === Running make test PERL_DL_NONLAZY=1 /usr/local/bin/perl5.00503 -Iblib/arch -Iblib/lib -I/usr/home/pi cks/usr/local/lib/perl5/5.00503/i386-freebsd -I/usr/home/picks/usr/local/lib/perl5 /5.00503 -e 'use Test::Harness qw(runtests $verbose); $verbose=0; runtests @ARGV; ' t/*.t t/base..ok t/configFAILED tests 2-5 Failed 4/5 tests, 20.00% okay t/fork..ok t/forkm.ok t/loop-childok t/loop..ok t/singleok t/threadskipped all skipped: no reason given t/threadm...skipped all skipped: no reason given t/unix..ok Failed Test Stat Wstat Total Fail Failed List of Failed --- t/config.t 54 80.00% 2-5 2 tests skipped. Failed 1/10 test scripts, 90.00% okay. 4/43 subtests failed, 90.70% okay. *** Error code 35 Stop in /usr/home/picks/.cpan/build/Net-Daemon-0.37. /usr/bin/make test -- NOT OK Running make install make test had returned bad status, won't install without force Running make for J/JW/JWIED/PlRPC-0.2016.tar.gz Unwrapped into directory /usr/home/picks/.cpan/build/PlRPC-0.2016 CPAN.pm: Going to build J/JW/JWIED/PlRPC-0.2016.tar.gz mkdir blib mkdir blib/lib mkdir blib/lib/RPC cp lib/RPC/PlClient.pm blib/lib/RPC/PlClient.pm cp lib/RPC/PlServer.pm blib/lib/RPC/PlServer.pm cp lib/RPC/PlServer/Comm.pm blib/lib/RPC/PlServer/Comm.pm cp lib/Bundle/PlRPC.pm blib/lib/Bundle/PlRPC.pm cp lib/RPC/PlServer/Test.pm blib/lib/RPC/PlServer/Test.pm mkdir blib/arch mkdir blib/arch/auto mkdir blib/arch/auto/RPC mkdir blib/arch/auto/RPC/PlServer mkdir blib/lib/auto/RPC mkdir blib/lib/auto/RPC/PlServer mkdir blib/man3 Manifying blib/man3/RPC::PlServer.3 Manifying blib/man3/RPC::PlClient.3 Manifying blib/man3/Bundle::PlRPC.3 /usr/bin/make -- OK Running make test PERL_DL_NONLAZY=1 /usr/local/bin/perl5.00503 -Iblib/arch -Iblib/lib -I/usr/home/pi cks/usr/local/lib/perl5/5.00503/i386-freebsd -I/usr/home/picks/usr/local/lib/perl5 /5.00503 -e 'use Test::Harness qw(runtests $verbose); $verbose=0; runtests @ARGV; ' t/*.t t/baseCan't locate Net/Daemon.pm in @INC (@INC contains: blib/arch blib/li b /usr/home/picks/usr/local/lib/perl5/5.00503/i386-freebsd /usr/home/picks/usr/loc al/lib/perl5/5.00503/i386-freebsd /usr/home/picks/usr/local/lib/perl5/5.00503/i386 -freebsd /usr/home/picks/usr/local/lib/perl5/5.00503 /usr/home/picks/usr/local/lib /perl5/5.00503/i386-freebsd /usr/home/picks/usr/local/lib/perl5/5.00503/i386-freeb sd /usr/home/picks/usr/local/lib/perl5/5.00503 /usr/home/picks/usr/local/lib/perl5 /5.00503/i386-freebsd /usr/home/picks/usr/local/lib/site_perl /usr/home/picks/usr/ local/lib/site_perl /usr/libdata/perl/5.00503/mach /usr/libdata/perl/5.00503 /usr/ local/lib/perl5/site_perl/5.005/i386-freebsd /usr/local/lib/perl5/site_perl/5.005/ i386-freebsd /usr/local/lib/perl5/site_perl/5.005 . /usr/libdata/perl/5.00503/mach /usr/libdata/perl/5.00503 /usr/local/lib/perl5/site_perl/5.005/i386-freebsd /usr/ local/lib/perl5/site_perl/5.005 .) at blib/lib/RPC/PlServer.pm line 25. BEGIN failed--compilation aborted at blib/lib/RPC/PlServer.pm line 25. t/basedubious Test returned status 2 (wstat 512, 0x200) DIED. FAILED tests 2-3 Failed 2/3 tests, 33.33% okay t/client..Can't locate Net/Daemon/Test.pm in @INC (@INC contains: blib/arch bl ib/lib /usr/home/picks/usr/local/lib/perl5/5.00503/i386-freebsd /usr/home/picks/us r/local/lib/perl5/5.00503/i386-freebsd /usr/home/picks/usr/local/lib/perl5/5.00503 /i386-freebsd /usr/home/picks/usr/local/lib/perl5/5.00503 /usr/home/picks/usr/loca l/lib/perl5/5.00503/i386-freebsd /usr/home/picks/usr/local/lib/perl5/5.00503/i386- freebsd /usr/home/picks/usr/local/lib/perl5/5.00503 /usr/home/picks/usr/local/lib/ perl5/5.00503/i386-freebsd /usr/home/picks/usr/local/lib/site_perl /usr/home/picks /usr/local/lib/site_perl /usr/libdata/perl/5.00503/mach /usr/libdata/perl/5.00503 /usr/local/lib/perl5/site_perl/5.005/i386-freebsd /usr/local/lib/perl5/site_perl/5 ..005/i386-freebsd /usr/local/lib/perl5/site_perl/5.005 . /usr/libdata/perl/5.00503 /mach /usr/libdata/perl/5.00503 /usr/local/lib/perl5/site_perl/5.005/i386-freebsd /usr/local/lib/perl5/site_perl/5.005 .) at
Re: execute and null values in quote
On Thu, 21 Nov 2002 15:12:47 -0800, Daniel Olson wrote:
$sth = $dbh-prepare(insert into ul_info
(ul_id,ul_len,ul_wid,ul_dep,ul_tun,ul_sin,ul_und,ul_und_fr,ul_und_to,ul_edge,ul_stat,ul_skin,ul_drain_typ,ul_drain_amt,ul_drain_col,ul_drain_od,ul_stage,timestamp,clinician)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,''));
$sth-execute($UlID,$formdata-param('Len'),$formdata-param('Wid'),$formdata-param('Dep'),$formdata-param('Tun'),$formdata-param('Sin'),$formdata-param('Und'),$formdata-param('UStart'),$formdata-param('UEnd'),$formdata-param('DEdge'),$formdata-param('DEval'),$formdata-param('DSkin'),$formdata-param('DType'),$formdata-param('DAmt'),$formdata-param('DColor'),$formdata-param('DOdor'),$newstage)
|| die Can't perform execute: $DBI::errstr;
If I run this from the command line and only define three variables, it fails
with the error:
DBD::Oracle::st execute failed: called with 5 bind variables when 17 are needed at
script.cgi line 201.
You shouldn't just drop undefined values. Pass undef instead. DBI will
insert NULL for those values.
--
Bart.
Re: DBD::Oracle on OS X Success!
On Friday, November 22, 2002, at 06:06 PM, Tom Mornini wrote: Thanks to Andy Lester [EMAIL PROTECTED] and Steve Sapovits [EMAIL PROTECTED], I've figured out a way to get DBD::Oracle to compile and pass all tests on OS X 10.2.2 Jaguar against Oracle 9iR2 DP. FYI, ORA's MacDevCenter.com has published the first of three articles discussing Oracle on Mac OS X. http://www.macdevcenter.com/pub/a/mac/2002/11/12/oracle_part1.html Regards, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: execute and null values in quote
Perhaps the param() method is returing an empty list for
parameter names that don't exist.
If so, try wrapping each with scalar(...).
Tim.
On Sat, Nov 23, 2002 at 10:04:43PM +0100, Bart Lateur wrote:
On Thu, 21 Nov 2002 15:12:47 -0800, Daniel Olson wrote:
$sth = $dbh-prepare(insert into ul_info
(ul_id,ul_len,ul_wid,ul_dep,ul_tun,ul_sin,ul_und,ul_und_fr,ul_und_to,ul_edge,ul_stat,ul_skin,ul_drain_typ,ul_drain_amt,ul_drain_col,ul_drain_od,ul_stage,timestamp,clinician)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,''));
$sth-execute($UlID,$formdata-param('Len'),$formdata-param('Wid'),$formdata-param('Dep'),$formdata-param('Tun'),$formdata-param('Sin'),$formdata-param('Und'),$formdata-param('UStart'),$formdata-param('UEnd'),$formdata-param('DEdge'),$formdata-param('DEval'),$formdata-param('DSkin'),$formdata-param('DType'),$formdata-param('DAmt'),$formdata-param('DColor'),$formdata-param('DOdor'),$newstage)
|| die Can't perform execute: $DBI::errstr;
If I run this from the command line and only define three variables, it fails
with the error:
DBD::Oracle::st execute failed: called with 5 bind variables when 17 are needed at
script.cgi line 201.
You shouldn't just drop undefined values. Pass undef instead. DBI will
insert NULL for those values.
--
Bart.
