Retrieving data from FoxPro free table (large table) with vb.net

dyaker
dyaker used Ask the Experts™
on
I apologize for the lengthy explanation and question.
Using the OleDB provider (v 8.0) and VB.Net, we are retrieving data from a FoxPro 6.0 free table by using
the following code (some variable instantiations might be missing from the snipet of code):

            SqlStr.Append(" SELECT * FROM rackdtl ")
            SqlStr.Append(" WHERE rk_pgm = '" & p_rk_pgm & "'")
            SqlStr.Append(" AND ch_id = " & ChainId)
            SqlStr.Append(" ORDER BY ch_id")
            Dim OledbCmd As New OleDbCommand
            Dim LocalConnection As OleDbConnection = new OleDbConnection(connection_string)

            OledbCmd.Connection = LocalConnection
            OledbCmd.CommandType = CommandType.Text
            OledbCmd.CommandText = SqlStr.ToString
            Dim dbAdapter As New OleDbDataAdapter(OledbCmd)
            Dim dsRackDetail as DataSet
            dbAdapter.Fill(dsRackDetail, "rackdtl")

This process works, but our problem is that the FILL method takes extremely long because of the size of the table (approx. 1million rows, some deleted).

Is there a way that we can make this process faster?  Is there a way to utilize indexes stored in the .cdx file to make it faster?
Maybe specify something in the SELECT command?

I appreciate the input very much.


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
1) They still make FoxPro?!
2) Why in the world would you ever want to return 1 million records?!

You can give the Fill command two optional parameters for first record and number of records.

    dbAdapter.Fill( dsRackDetail, 0, 1000, "rackdtl " );

Then put a query in front of this window to force the user to actually get the information that they want.  If they don't enter anything, then you return the first 1000 instead.  If they want more, then provide a 'Next' button that increases the first number by 1000 and return the next 1000 records.

Author

Commented:
Thanks for the response.

1) Hard to believe but yes, still FoxPro, and we have version 6.0 so we can't use the enhancements that were added in 7.0  
    and 8.0

2) The table has a total of apporx. 1 million rows, but probably half of them are deleted so we don't care about those

3) I failed to explain that the process of getting the data from this table is only a small part of a bigger picture that includes
    reading data from different FoxPro tables into different datasets.  The user does not see any of this processing.  It all  
    happens in our middle tier.  The only thing they will see at the end of the processing is an invoice.
Commented:
2) Deleting half a million rows will increase your speed most dramatically.  I used to make my Clipper users clear out their databases and did Reindex every weekend because of a deleted records performance hit.
3) You're retrieving half a million rows just for one invoice?  

The indexes are automatically used by SQL, there is no need to try to access them directly.

Make sure your indexes are valid, too many indexes will affect your performance, typically for updates/deletes though.  For example, never have a index on a field that has only 2-4 values like Sex or Eye color.  Indexes like that are worthless.

I've gotta scratch my head and wonder why you're pulling so much data into a DataSet?  Could you use join queries and only get the data you need?  Remember, you have functionality in SQL like Sum and Avg to keep the amount of data you return to a minimum.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Commented:
I don't know your exact situation, but these suggestions might help.

Do you have an index on rk_pgm? ... on ch_id?  Do you have a composite index of rk_pgm and ch_id?
To optimize a particular query, you geerally want an index for fields in the WHERE and ORDER BY clauses.


psdavis> The indexes are automatically used by SQL, there is no need to try to access them directly.
I wonder if there's some diagnostics to know  whether this is actually happening at run-time.  psdavis, do you know?


This two-step strategy might help (assuming the problem is actually slow access to the Foxpro database file).

(1) Before invoice processing (perhaps daily?):
  IMPORT:  (assumes records have some kind of timestamp, which is an indexed field)
    Record the "import time".
    Import all rackdtl records since the last time you imported into a table "ImportedRackdtl" in SQL Server (or MSDE, or even maybe MS Access, or even MySQL, or whatever).
    // Make sure ImportedRackdtl has appropriate indexes.

(2)Invoice processing:
  For each invoice:
    Select records from ImportedRackdtl instead of rackdtl.

This strategy limits the processing of the original rackdtl file to only the new records, and _potentially_ moves the FoxPro access so that it only happens once and so it's at a time (after midnight?) when the user doesn't need to wait for it.  If you have a number of tables from different sources that you do this with, it has an additional advantage, in that you can then do joins on any of the imported tables, leading to more effective queries.

Author

Commented:
Again, thank you for all the input.

All the ideas submitted sound pretty good, but they don't apply to what we need to accomplish.
For reasons too long to explain in one comment, we need to run a select statement through ODBC (the developer doing this found that the OleDB provider produces an exception error that may/may not appear randomly; not stable), against our
detail table AS IS.  We cannot move data, deleted records or subselect anything from the table (other than the data that we want to get with the sql statement).  Our only solution to make it faster is
finding a way to indicate in the sql statement, which of the indexes contained in the .cdx file we want to use.

Does anyone know of a way specify (force)  the index name (which could be one of several in the .cdx) in the sql statement?

Thank You

Author

Commented:
We found it!!!!

The only thing we had to do was create a simple index (one field only) in the .cdx file.
The sql query apparently knows what index to use.  We cut the time from 4 minutes to 17 seconds.

Thank you all.

Commented:
A number of avenues to explore:


I don't know anything about this library, but it might be worth a try, since your hands are tied and you have few options.  ODBC is probably the slowest thing you could use.  Are there any other wokarounds for the OleDB provider random unstable exception?

xBase Engine for C# and VB.NET
http://www.c-sharpcorner.com/Code/2002/Feb/xBaseEngineRFV.asp

---

> We cannot move data, deleted records or subselect anything from the table (other than the data that we want to get with the sql statement).  Our only solution to make it faster is finding a way to indicate in the sql statement, which of the indexes contained in the .cdx file we want to use.

Hmmmm.  I'm not sure I really believe this (as stated).  I don't think trying to mislead, but people often overlook some unexpected options.  Your comments seem to state a technical requirement.  Technical requirements are generally derived from business requirements.  It would be interesting and useful to know what the actual business requirements are.

---
Diagnostics:
Perhaps you could determine if an index is being used, and if so, which one.
You could do so by adding some code to time the Fill operation.
Run this diagnostics test against a test-copy of the FoxPro file.
Time it as-is.  That's your baseline.
Delete all indexes.
Time it.  If it's about the same speed, then it's not (effectively) using indexes.
             If it's slower, it means the original baseline is at least using some index which helps somewhat.
Experiment with just deleting certain indexes to isolate exactly which index(es) are being used for the query.

---

If this is going over a network, it might be faster to copy the whole FoxPro file to the local machine, then use the local copy (though this may not fit your constraints).

---

I found a page with this:

  "In order for the FoxPro indexes to "kick in" when you run your query the
  expression you use in your WHERE clause must exactly match the index
  expression. Do you know what the index expression is? Can you post the exact
  query you are using?

  [USE INDEXSEEK([index name]) is not a valid FoxPro construct which is why
  you're getting errors.

  Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
  cindy.winegarden@mvps.org
  http://cindywinegarden.adsl.duke.edu
  http://mdsn.microsoft.com/vfoxpro
  http://foxcentral.net"

The implication of this is that you want to use the exact index expression used for an index in your WHERE clause.  It might even be faster to do that and select more data than you actually need, then filter it more after you get it.  To do that, you might omit the Fill statement, use some firehose Reader object to read the data, and insert only the appropriate records in the data table in the data set.

What are the indexes (and the exact index expressions for them) currently on the existing FoxPro table?

Commented:
That last post was in response to your 11:23AM PDT message.  I started before your next post was posted.

What field did you add an index for?

Don't forget to assign points to close your question.

Author

Commented:
We created a simple index (in the .cdx) with tag CH_ID with a simple expression: CH_ID.  Somehow the sql statement went down, like I said before, to 17 seconds.
We are very happy that we found the solution to this.  Now we can move on with the project.
Most Valuable Expert 2012
Top Expert 2008

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: psdavis {http:#9243738} & farsight {http:#9247975}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

TheLearnedOne
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial