VFPOLEDB is very slow with free tables???

[Lonvriz]

The problem is everytime I do a SELECT * FROM [Tablename] WHERE
[Criteria].
The query takes over 2 minutes to run. I don't think the connection is
using the Indexes specified on the table.

Does anyone know a way to force ADO connections to use a specific
index on a foxpro table? Or a way to speed up this process?

I have one free table SALES.DBF with SALES.CDX compound file.
In the sales.cdx there are a key = xxxx+yyyy

My select is :

SELECT * FROM SALES WHERE (XXXX=criteria)

If database open with this tag index is ordered by xxxx, why it works
so slow????

The command works, but very very slow. It returns over 45,000 regs.

Any information will be very appreciated.

Thank you for advance,

Nacho.
Huesca(Spain)

 

[Dan Normington]

I don't have any foxpro background, but it sounds to me
like you are just returning too many records. Try changing
your "Select *" to only select the columns that you need.
Also make sure you have indexes set on the appropriate
fields. If you are querying on a certain field a lot it
may be benneficial to add an index on the column you are
doing the "where" clause on. Also, for faster performance
you can use an OleDbDataReader class in conjuction with
the ExecuteReader command from the OleDbCommand object. It
is a forward only result set. Also, make sure you close
the connection after getting the data from the reader.

HTH

 

[Cindy Winegarden]

In Lonvriz <[email protected]> posted:

Make sure you have the latest VFP OLE DB data provider, available from
http://msdn.microsoft.com/vfoxpro/downloads/updates/default.aspx.

The problem is everytime I do a SELECT * FROM [Tablename] WHERE
[Criteria].
The query takes over 2 minutes to run. I don't think the connection is
using the Indexes specified on the table.
Does anyone know a way to force ADO connections to use a specific
index on a foxpro table? Or a way to speed up this process?

You need to be sure the expression in your WHERE clause exactly matches the
index expression. Also, retrieval (within FoxPro at least) is faster if the
table is used as-is and not set to a specific order. VFP's Rushmore
technology will kick in all by itself.

I have one free table SALES.DBF with SALES.CDX compound file.
In the sales.cdx there are a key = xxxx+yyyy

My select is :

SELECT * FROM SALES WHERE (XXXX=criteria)

If database open with this tag index is ordered by xxxx, why it works
so slow????

The command works, but very very slow. It returns over 45,000 regs.

Do you notice any difference in speed if the data is copied to your local
machine as compared to accessing it across a LAN? I agree with Dan - 45,000
records is a lot.

 

[Cindy Winegarden]

In Lonvriz <[email protected]> posted:

Make sure you have the latest VFP OLE DB data provider, available from
http://msdn.microsoft.com/vfoxpro/downloads/updates/default.aspx.

The problem is everytime I do a SELECT * FROM [Tablename] WHERE
[Criteria].
The query takes over 2 minutes to run. I don't think the connection is
using the Indexes specified on the table.
Does anyone know a way to force ADO connections to use a specific
index on a foxpro table? Or a way to speed up this process?

You need to be sure the expression in your WHERE clause exactly matches the
index expression. Can you add an index on XXXX by itself? (It shouldn't have
any affect on other applications. Just be sure it has a different name from
the XXXX+YYYY index expression.) Also, retrieval (within FoxPro at least) is
faster if the table is used as-is and not set to a specific order. VFP's
Rushmore technology will kick in all by itself.

I have one free table SALES.DBF with SALES.CDX compound file.
In the sales.cdx there are a key = xxxx+yyyy

My select is :

SELECT * FROM SALES WHERE (XXXX=criteria)

If database open with this tag index is ordered by xxxx, why it works
so slow????

The command works, but very very slow. It returns over 45,000 regs.

Do you notice any difference in speed if the data is copied to your local
machine as compared to accessing it across a LAN? I agree with Dan - 45,000
records is a lot.

 

[Lonvriz]

I have, thank you

In Lonvriz <[email protected]> posted:

Make sure you have the latest VFP OLE DB data provider, available from
http://msdn.microsoft.com/vfoxpro/downloads/updates/default.aspx.

The problem is everytime I do a SELECT * FROM [Tablename] WHERE
[Criteria].
The query takes over 2 minutes to run. I don't think the connection is
using the Indexes specified on the table.
Does anyone know a way to force ADO connections to use a specific
index on a foxpro table? Or a way to speed up this process?

You need to be sure the expression in your WHERE clause exactly matches the
index expression. Also, retrieval (within FoxPro at least) is faster if the
table is used as-is and not set to a specific order. VFP's Rushmore
technology will kick in all by itself.

I have one free table SALES.DBF with SALES.CDX compound file.
In the sales.cdx there are a key = xxxx+yyyy

My select is :

SELECT * FROM SALES WHERE (XXXX=criteria)

If database open with this tag index is ordered by xxxx, why it works
so slow????

The command works, but very very slow. It returns over 45,000 regs.

Do you notice any difference in speed if the data is copied to your local
machine as compared to accessing it across a LAN? I agree with Dan - 45,000
records is a lot.