PERL Miscellaneous - using Win32::ODBC

Author

using Win32::ODBC - what's fast?

Fred

2004-10-29, 3:57 am

I have these subs... init_sku_lookup() takes 20 seconds to run on a
pentium IV... when done it has 70k sku numbers in it. It's coded the way
it is so that it only gets unique values. The other sub check_for_sku()
runs like instantly. And I call it before I build an add record for an
item. (If the sku is on file we don't build the add record.) So I guess I
was wondering if there was any way to speed up init_sku_lookup(). Critism
is welcome. I'm processing 70k items and a call to init_sku_lookup() for
each one would be insane. So I build the hash and depend on perl's
'exists' to do the fast work. But I wonder..... I don't tknow the
internals of Win32::ODBC but I wish I could just slurp the whole record
set..... did I mention I ask myself about every 30 minutes... "Do you
really know what your doing here?"

TIA
Fred

sub init_sku_lookup() {
%skuhash;
$db = new Win32::ODBC("sg") || die "Error: " . Win32::ODBC::Error();
if ( !$db->Sql("SELECT DISTINCT SKU_tbl.sku from SKU_tbl") ) {
while ( $db->FetchRow() ) # iterate over the data set {
$val = $db->Data();

# load file values into hash
$skuhash{$val} = $val;
}
}
$db->Close();
}

sub check_for_sku() {

if ( exists $skuhash{ $Fld[11] } ) {
return 0; # it's true! it did exist
}
else {
return 1; # it was not there so build the add record
}

}

Matt Garrish

2004-10-29, 3:57 am

"Fred" <noemail@#$&&!.net> wrote in message
news:pan.2004.10.29.00.17.40.139861@%23$&&!.net...
>I have these subs... init_sku_lookup() takes 20 seconds to run on a
> pentium IV... when done it has 70k sku numbers in it.
>

Are all your tables indexed on the select column to maximize for speed?

> It's coded the way
> it is so that it only gets unique values. The other sub check_for_sku()
> runs like instantly. And I call it before I build an add record for an
> item. (If the sku is on file we don't build the add record.) So I guess I
> was wondering if there was any way to speed up init_sku_lookup().

Why are you reading all 70,000 unique ids into memory? It would be faster
just to execute a select statement each time you need to check if an sku
already exists. I've never used the Win32::ODBC module, but if you were
using the DBD driver you could just check whether the unit id exists like
so: (untested)

sub check_exists {

my ($tid, $unitid) = @_;

my $sel_sth = $dbh->prepare("SELECT sku from $tid WHERE sku = ?") or die
$dbh->errstr();

$sel_sth->execute($unitid) or die $dbh->errstr();

if ($sel_sth->fetchrow_array) {
$sel_sth->finish();
return 1;
}

return 0;

}

Remember that most of the overhead involved in database access is in setting
up the connection. Once you have that connection, querying the database
should be very fast (assuming your data is well structured and indexed).

Matt

Fred

2004-10-29, 8:56 am

On Thu, 28 Oct 2004 22:30:14 -0400, Matt Garrish wrote:

> Are all your tables indexed on the select column to maximize for speed?

They are indexed on that column, but good point.

> Why are you reading all 70,000 unique ids into memory?

I thought it would be faster than doing it each time. Even with the index,
it just *seemed* exspensive, but I see what you mean about the connection.
( and the where clause!!)

>It would be faster
> just to execute a select statement each time you need to check if an sku
> already exists. I've never used the Win32::ODBC module, but if you were
> using the DBD driver you could just check whether the unit id exists
> like so: (untested)
>
> sub check_exists {
>
> my ($tid, $unitid) = @_;
>
> my $sel_sth = $dbh->prepare("SELECT sku from $tid WHERE sku = ?") or
> die
> $dbh->errstr();
>
> $sel_sth->execute($unitid) or die $dbh->errstr();
>
> if ($sel_sth->fetchrow_array) {
> $sel_sth->finish();
> return 1;
> }
>
> return 0;
>
> }
>
> Remember that most of the overhead involved in database access is in
> setting up the connection. Once you have that connection, querying the
> database should be very fast (assuming your data is well structured and
> indexed).
>
> Matt

I see now that including the where cluase leverages the power of the
index.. and individual selects start looking a lot better. Can't wait to
to test and thanks very much for your insight! Plus I'll try out the
driver you mention and test the diff. In the beginning when I was learning
perl, (circa 6 months ago) Win32::ODBC was the first thing I found tor
acessing MS DB's... so I learned it and never looked back. Bad practice in
'theory' but in real life when people want things done yesterday.... Thank
you again!

Fred

Sponsored Links