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
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.