 |
|
| Computers Forum Index » Computer - Databases - Paradox » paradox sql select w/index... |
|
Page 1 of 1 |
|
| Author |
Message |
| Jason... |
 Posted: Fri Jun 19, 2009 5:14 pm |
|
|
|
Guest
|
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? |
|
|
| Back to top |
|
|
|
| Liz McGuire... |
| Posted: Fri Jun 19, 2009 7:03 pm |
|
|
|
Guest
|
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
"Jason" <jason at (no spam) sipstorm.com> wrote:
Quote:
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?
|
|
|
| Back to top |
|
|
|
| Larry DiGiovanni... |
| Posted: Fri Jun 19, 2009 7:10 pm |
|
|
|
Guest
|
Jason wrote:
Quote: 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 |
|
|
| Back to top |
|
|
|
| Jason... |
| Posted: Fri Jun 19, 2009 9:47 pm |
|
|
|
Guest
|
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?
"Liz McGuire" <liz at (no spam) paradoxcommunity.com> wrote:
Quote:
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
"Jason" <jason at (no spam) sipstorm.com> wrote:
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?
|
|
|
| Back to top |
|
|
|
| Liz McGuire... |
| Posted: Sat Jun 20, 2009 12:34 am |
|
|
|
Guest
|
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
Jason wrote:
Quote: 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?
|
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Wed Nov 25, 2009 2:37 am
|
|