Re: looking for speed

"Jerry Preston" <g-preston1_at_ti.com> wrote in message news:<ckgb02$iu6$1_at_home.itg.ti.com>...
> Hi!
>
> I have a simple table with 25k plus entries. Currently I am using the
> following and it is very slow:
>
> my $sql="select Name, Address, ID
> from data_status
> where TRASHED = 'NOT'
> and NOT = '$not'
> and TP = '$area'";
> print "$sql<BR>" if $DEBUG;
> my $sth=$dbh->prepare($sql) || die "$DBI::errstr\n";
> $sth->execute() || die "Cannot execute Statement - build sort
> tables!!<br>$DBI::errstr";
>
> while( my ( $Name, $Address, $ID ) = $sth->fetchrow() ) {
> .
> .
> }
>
> Is there any way to speed this up?
>
> Thanks,
>
> Jerry

Jerry, I tried posting earlier but my posts have never shown up so I will try again via google:

Does an index exist on any of the referenced where clause columns: TRASHED, NOT, TP? Are the statistics current?

Depending on the total number of rows in the table and how distinctive the columns are will determine if the CBO can (should) use an index to solve the query. But the CBO cannot use what does not exist. So if one or more of the columns data distribution is such that it will identify a small percentage of the table then add a single index on one, two, or all three of these columns.

Which columns to include should be determined based on all the queries issued by your application against this table.

HTH -- Mark D Powell -- Received on Tue Oct 12 2004 - 20:08:19 CDT