Hi all,
I'm having a heck of a time querying a large table. What I'm trying to do
is:
#!/usr/bin/perl -w
use DBI;
use strict;
$|++;
my $dbh = DBI->connect("dbi:ADO:Provider=SQLOLEDB;Integrated Security=SSPI;Data
Source=(local);Initial Catalog=db;CommandTimeout=900");
die "Connect failed: " . $DBI::errstr if !defined($dbh);
my $sql = <<EOS;
select top 30000 c.col1, c.col2, c.col3, d.col4
from tabl1 c, tabl2 d
where c.col4 = d.col4
and c.col5 = 'N'
and c.col6 = '0'
EOS
my $sh = $dbh->prepare($sql);
$sh->{ado_conn}->SetProperty("CommandTimeout",900);
$sh->execute; # HERE
$sh->finish
$dbh->disconnect;
The line marked HERE always errors out with a "Timeout expired" error
between 30 and 40 seconds after HERE is begun. I've tried setting the
CommandTimeout values to 0, not doing the one on the line before HERE,
moving it out of the connect string and setting it separately, e.g.
DBI->connect("dbi:ADO:...",,{ CommandTimeout => 900 });
Nothing seems to work. I'm using perl 5.8.2.808 (activestate) on a Win2003
server with SQL Server 2000 (same machine), DBI 1.43, DBD::ADO 2.91. I'm
not sure which MDAC is installed. The archives were thin on this. I've
reviewed the ADO.pm and docs for more info, but I'm stumped.
The table I'm querying has 61M rows. I'm trying to review them in
batches. I don't expect the query will ever take less than 30s. That's
not the issue. I'd just like to be able to get any amount of rows back at
this point. Even TOP 500 fails.
TIA,
Dave
