Re: Code useless, forget it... this is more usefull (o;

OK, I see nothing obviously wrong in this snippet, but...
(Apologies if this is teaching you to suck eggs)

1. If your main concern is overall speed, I suggest some detailed profiling. Find where the code is spending most of its time. See Benchmark. Use the Benchmark->new and timediff() methods to look at checkpoints. Since you are doing lots of logging anyway, have your loggit() call put a timestamp in the LOG.

Prepare_cached is only going to save you time if it takes a long time to do the actual prepare and you call it lots of times. It could be that if the datasets are large the time is taken in the execute(), and not the prepare().
Depending on your DB config, load on the server, and systems could be a factor.
You seem to be collecting all the values and then only using the first 15 or 10 lines. How about using a LIMIT command to reduce the lines computed/returned by the SQL.
There may be other SQL/DB efficiency ideas, but I am not the person to ask about that. I expect that a good time profile will help you narrow down the issues better.

2. Have you turned on the error checking in the DB ?

{RaiseError=>1}
[download]

in the connect(). It could be that there is a problem in the prepare() line.

3. Is it only failing if you use prepare_cached ? I.E. does it work without the _cached ?

4. Are you using the $sth->finish() when no longer needed ? This can generate errors if there is an open execute and _cached is called again.

That's all I can think of right now, good luck.
--
Brovnik

Comment on Re: Code useless, forget it... this is more usefull (o; Download Code

Replies are listed 'Best First'.
Re: Re: Code useless, forget it... this is more usefull (o; by Eric.D (Initiate) on Jun 22, 2001 at 17:34 UTC
How about using a LIMIT command ... unfortunately, LIMIT is not valid in ORACLE )o; and I call both of the one I detail about 80 000 times... I tried to bind the values.. same problem... `$sth_imei = $dbh->prepare_cached(<<__EOSQL__) or die "Couldn't parse q +uery:".$sth_imei->errstr; SELECT imei FROM imei_period WHERE (mobile_number = :mob) AND (account_no = :acc) __EOSQL__ $sth_imei->bind_param( ':mob', $rec[1] ); $sth_imei->bind_param( ':acc', $rec[0] ); $sth_imei->execute() or die "Couldn't execute query:" . $sth_imei- +>errstr; @imei = $sth_imei->fetchrow_array; print "IMEI : @imei\n";` [download] When executed, the $sth_imei->errstr does not return anything... I check, the SQL works and return the requested infor if executed in SQLPlus... This is very frustrating, especially since the $sth_acc one is working fine... for $sth->finish() I thought I should not use it as it was cached ?? But yes, I do not use it much... Ihad the PrintError => 1, set, but not the RaiseError one. If I use RaiseError => 1 it doesnot change a thing )o; still no error reported... I will go for a perl -w I think... (o;	[reply] [d/l]
Re: Re: Re: Code useless, forget it... this is more usefull (o; by Arguile (Hermit) on Jun 22, 2001 at 17:46 UTC
The LIMIT clause isn't in Oracle but you can use the internal rownum index to grab the selected records. See Re: Querying Select Number of Rows for more info.	[reply]
Re: Re: Re: Code useless, forget it... this is more usefull (o; by Brovnik (Hermit) on Jun 22, 2001 at 18:39 UTC
A couple more ideas (sparked by the 80,000) : Try binding the return values. e.g. `my $imei; $rc = $sth_imei->bind_col(0,\$imei); $sth_imei->execute(); $sth_imei->fetch(); print "IMEI : $imei\n";` [download] If you don't want to do that, at least use ->fetchrow_arrayref(). Otherwise, each execute call has to create a new list for you. The _arrayref gives you a ref. to a static array, so less copying. bind_param should take a colum number shouldn't it, not a name, e.g. `$sth_imei->bind_param(0,$rec[1])` Either of the first 2 should save time, given 80,000 iterations. Finally, note that execute() can return '0E0' meaning 0 but true, meaning no rows (and different to undef for errors). May be worth checking for that explicitly. I think I'm out of ideas now... -- Brovnik	[reply] [d/l] [select]
Re: Re: Code useless, forget it... this is more usefull (o; by Eric.D (Initiate) on Jun 22, 2001 at 18:32 UTC
3. Without the "_cached", it does not seems to change anything..... I used DBI->trace as Arguile wisely suggested, and it looks like all is fine... strange... The Statement is prepared, the parameter binded, and the all thing is executed successfully. BUT it returns nothing ([0] items). if I cut and paste the SQL from the Perl, and replace the :mob and :acc by the binded values in the log file, it returns the value fine (the entrie does exist in the table...). Maybe I look for an error in the Perl when I should go and seek the wisdom of the DBA Administrator as he might have error log of my activities....	[reply]