paradox sql select w/index

[Jason]

I have a composite index on 3 columns (Classification, ClientID, StartDate)
the index name is ByClientId.

I have an odbc connection to the paradox 5 db via a DSN. When I run the following
query, it takes about 90 secs to return. There are 60K records in the table

select *
from table
where classification = 1
and clientid = 110
and startdate = 39951

It appears that my query is not using the index. Is there a trick or something
I am missing?

[Liz McGuire]

Jason,

First, please be aware that most people here use Paradox the application
to access Paradox the table format and so may not know the answer to your
question. You might have better luck on forums for the application you're
using.

That said, I don't know whether there's any BDE command or option that forces
use of an index, but how long does it take to run a "select * from table"
query?

If you're running over a connection that's less than 100Mb, that could explain
the speed issue.

Am I correct that startdate is not a date type field in Paradox (or a datetime
type field)? Cuz if it is, I can't believe it's going to like your date
criteria, in which case, it would return no rows and that's the slowest kind
of query.

FWIW,

Liz

Post by Jason
I have a composite index on 3 columns (Classification, ClientID, StartDate)
the index name is ByClientId.
I have an odbc connection to the paradox 5 db via a DSN. When I run the following
query, it takes about 90 secs to return. There are 60K records in the table
select *
from table
where classification = 1
and clientid = 110
and startdate = 39951
It appears that my query is not using the index. Is there a trick or something
I am missing?

[Jason]

select * from table returns in 0.05 seconds, so it is very fast.

All 3 columns are longs and data does return.

If I create specific indexes on each column the query returns fast.

Is there a problem with composite indexes?

Post by Liz McGuire
Jason,
First, please be aware that most people here use Paradox the application
to access Paradox the table format and so may not know the answer to your
question. You might have better luck on forums for the application you're
using.
That said, I don't know whether there's any BDE command or option that forces
use of an index, but how long does it take to run a "select * from table"
query?
If you're running over a connection that's less than 100Mb, that could explain
the speed issue.
Am I correct that startdate is not a date type field in Paradox (or a datetime
type field)? Cuz if it is, I can't believe it's going to like your date
criteria, in which case, it would return no rows and that's the slowest kind
of query.
FWIW,
Liz

Post by Jason
I have a composite index on 3 columns (Classification, ClientID, StartDate)
the index name is ByClientId.
I have an odbc connection to the paradox 5 db via a DSN. When I run the

following

Post by Jason
query, it takes about 90 secs to return. There are 60K records in the table
select *
from table
where classification = 1
and clientid = 110
and startdate = 39951
It appears that my query is not using the index. Is there a trick or something
I am missing?

[Liz McGuire]

I can only refer to Larry's suggestions. But if having three indexes,
one per column, does the trick, I'd say go that way. Sounds like the
ODBC driver won't use the composite...

Liz

Post by Jason
select * from table returns in 0.05 seconds, so it is very fast.
All 3 columns are longs and data does return.
If I create specific indexes on each column the query returns fast.
Is there a problem with composite indexes?

[Larry DiGiovanni]

Post by Jason
It appears that my query is not using the index. Is there a trick or
something I am missing?

I believe the query optimization is up to the ODBC driver. Only suggestion
I can offer are to rearrange the where clause to order the criteria
differently. One other possibility is that your ODBC driver was not
designed to use Paradox indexes in queries. ISTR this was a failing of some
ODBC drivers.

You can also try rebuilding the index - it may be out of date, crosslinked,
etc, which would result in a performance problem.

--
Larry DiGiovanni