SQL Queries and Local Tables with Indexes - Speed????

b...@droste1.com (Bill Artemik)

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

SQL Queries and Local Tables with Indexes - Speed????

I have several queries that need to be performed on local tables that
have indexes that I have to believe will help the speed of a query ...
however If I run the SAME query statement on a dBase III + table
WITHOUT an index and on a Fox .Dbf WITH a cdx index on all fields that
are being used there is LITTLE difference. WhY?

Using this fox .dbf with .CDX indexes scenario:
...is there a way to get the BDE to respect the fact that there is an
index on these tables and to use them?

Any insight is appreciated as I have a BUNCH of SQL code to write.

-----
Bill Artemik
Software Engineer
Droste Consultants, Inc.
b...@droste1.com

-------------------------

Attn: Spammers -> don't send to the following addresses:
first.l...@whitehouse.gov consumerl...@ftc.gov c...@ftc.gov fr...@uspis.gov u...@ftc.gov rh...@fcc.gov jque...@fcc.gov sn...@fcc.gov rch...@fcc.gov custo...@usps.gov
postmas...@agis.net ab...@agis.net r...@agis.net dns-ad...@agis.net n...@agis.net
hostmas...@agis.net p...@agis.net webmas...@agis.net docum...@agis.net ip-requ...@agis.net rout...@agis.net n...@agis.net account...@agis.net i...@agis.net a...@agis.net h...@agis.net
emai...@qlink2info.com sim...@answerme.com hostmas...@inreach.com a...@inreach.com
postmas...@emaildirect.net ab...@emaildirect.net r...@emaildirect.net
postmas...@e-bizness.com ab...@e-bizness.com r...@e-bizness.com com...@iaw.on.ca
maxem...@united-cbe.org dom...@emaildirect.net dom...@hosting.netcom.com
sa...@marketingmasters.com biz...@softcom.net getpa...@technosurf.com
dom...@hosting.netcom.com a...@additionalbenefits.com ls...@ne.infi.net
ltcli...@ne.infi.net ltper...@ne.infi.net ab...@alladvantage.com ab...@hotmail.com

Steve Koters

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Queries and Local Tables with Indexes - Speed????

On 13 Jan 2000 08:11:15 -0600, b...@droste1.com (Bill Artemik) wrote:

Quote

>I have several queries that need to be performed on local tables that
>have indexes that I have to believe will help the speed of a query ...
>however If I run the SAME query statement on a dBase III + table
>WITHOUT an index and on a Fox .Dbf WITH a cdx index on all fields that
>are being used there is LITTLE difference. WhY?

>Using this fox .dbf with .CDX indexes scenario:
>...is there a way to get the BDE to respect the fact that there is an
>index on these tables and to use them?

That the performance is the same when the SQL statement is executed on the
table with and without the index suggests that the index is simply not
being used. In local SQL, there is no way through SQL syntax to force an
index to be used. The BDE either finds an index it associates with the
situation and uses it, or it does not. (Compare this with the InterBase
PLAN keyword.)

I am not sure about whether or not FoxPro indexes are even considered for
local SQL, let alone whether their use follows the same rules as for dBASE
tables. But here are some rules for dBASE tables and I will let you apply
them to your specific situation. BDE use of indexes for local SQL
statements is *very* rudimentary. Only simple indexes can be used (i.e.,
single-field, non-expression indexes). I do not believe the name of the
index is pertinent, however, and the BDE bases the applicability of an
index on the index's column. On the SQL side, an index can only be used for
very simple situations. A WHERE clause with a single condition that is
based on a single field (the same field as the index) is one such
situation. A join on one field is another. But combine even these two
simple situations and the BDE might use no index at all.

What was your SQL statement? What kind of index were you wishing to be
used, simple or expression? Is the name of the index the same as the field
(in the case of a simple index)?

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski "Health nuts are going to feel stupid someday,
Felton, CA lying in hospitals dying of nothing."
-- Redd Foxx

Bill Artem

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Queries and Local Tables with Indexes - Speed????

Quote

>That the performance is the same when the SQL statement is executed on the
>table with and without the index suggests that the index is simply not
>being used. In local SQL, there is no way through SQL syntax to force an
>index to be used. The BDE either finds an index it associates with the
>situation and uses it, or it does not. (Compare this with the InterBase
>PLAN keyword.)

>I am not sure about whether or not FoxPro indexes are even considered for
>local SQL, let alone whether their use follows the same rules as for dBASE
>tables. But here are some rules for dBASE tables and I will let you apply
>them to your specific situation. BDE use of indexes for local SQL
>statements is *very* rudimentary. Only simple indexes can be used (i.e.,
>single-field, non-expression indexes). I do not believe the name of the
>index is pertinent, however, and the BDE bases the applicability of an
>index on the index's column. On the SQL side, an index can only be used for
>very simple situations. A WHERE clause with a single condition that is
>based on a single field (the same field as the index) is one such
>situation. A join on one field is another. But combine even these two
>simple situations and the BDE might use no index at all.

>What was your SQL statement? What kind of index were you wishing to be
>used, simple or expression? Is the name of the index the same as the field
>(in the case of a simple index)?

Well, that explains why I get little boost in performance. There are
two fields that make up the uniqueness constraint so that's out, the
other indexes in the .cdx file are simple field indexes. I have been
testing this (for some legacy Clipper code migration considerations)
in hopes that the .CDX files would improve my Delphi / SQL performance
and help us move off older Clipper / Fox .NTX files but with little
difference in performance I'll just have to live with using third
party products like Apollo until I can get us out of local tables
altogether and into a SQL back end.

Thanks for your help!

Bill

-----
Bill Artemik
Software Engineer
Droste Consultants, Inc.
b...@droste1.com

-------------------------

Attn: Spammers -> don't send to the following addresses:
first.l...@whitehouse.gov consumerl...@ftc.gov c...@ftc.gov fr...@uspis.gov u...@ftc.gov rh...@fcc.gov jque...@fcc.gov sn...@fcc.gov rch...@fcc.gov custo...@usps.gov
postmas...@agis.net ab...@agis.net r...@agis.net dns-ad...@agis.net n...@agis.net
hostmas...@agis.net p...@agis.net webmas...@agis.net docum...@agis.net ip-requ...@agis.net rout...@agis.net n...@agis.net account...@agis.net i...@agis.net a...@agis.net h...@agis.net
emai...@qlink2info.com sim...@answerme.com hostmas...@inreach.com a...@inreach.com
postmas...@emaildirect.net ab...@emaildirect.net r...@emaildirect.net
postmas...@e-bizness.com ab...@e-bizness.com r...@e-bizness.com com...@iaw.on.ca
maxem...@united-cbe.org dom...@emaildirect.net dom...@hosting.netcom.com
sa...@marketingmasters.com biz...@softcom.net getpa...@technosurf.com
dom...@hosting.netcom.com a...@additionalbenefits.com ls...@ne.infi.net
ltcli...@ne.infi.net ltper...@ne.infi.net ab...@alladvantage.com ab...@hotmail.com

Other Threads

1. Secondary indexes for LOCAL SQL queries...?

2. How to get local sql to use an index for join on DBASE tables

3. Local SQL / Indexes for dBase Tables

4. How to get local sql to use an index for join on DBASE tables

5. Help needed with SQL Query on a local table

6. Local sql, query on query problem

7. Query of a query (with Local SQL)...?

8. Using Index with SQL Query on Paradox Table

9. Local SQL, works in MS SQL, not in local SQL

10. MS SQL 6.5 vs Local SQL Query