当我读取一个巨大的 FoxPro .DBF 时，如何使用 VFPOLEDB 提供程序来利用 .NDX/.CDX 索引? [英] How do I use the VFPOLEDB Provider to make use of the .NDX/.CDX indexing when I read a huge FoxPro .DBF?

问题描述

我正在用 C# 编写一个 .NET 应用程序，它使用 Visual FoxPro OLE DB 提供程序 (VFPOLEDB.1) 读取 FoxPro .DBF.

I am writing a .NET application in C# that reads a FoxPro .DBF using the Visual FoxPro OLE DB provider (VFPOLEDB.1).

问题是 .DBF 是 900MB，所以与 FoxPro(我们使用 DOS FoxPro)的瞬时读取相比，一个简单的读取花费的时间太长了.提供程序和本地 FoxPro 搜索之间的区别在于，我假设 FoxPro 使用索引文件(我们使用 .CDX 和 .NDX)，并且提供程序使用给定的 SQL 语句进行一种线性搜索.

The problem is the .DBF is 900MB, so a simple read takes way too long, compared to the instantaneous reading from FoxPro (we use the DOS FoxPro). The difference between the provider and and a native FoxPro search is that I assume FoxPro makes use of the indexing files (we use .CDX and .NDX) and the provider does a kind of linear search with the given SQL statement.

public DataTable getData(string refNum = "TESTREFNUM")
{

    DataTable result = new DataTable();

    OleDbConnection connection = new OleDbConnection(
            "Provider=VFPOLEDB.1;Data Source=C:\\PATH\\TO\\DBFFILES\\;Exclusive=No");

        connection.Open();

        if (connection.State == ConnectionState.Open)
        {
            string mySQL = "SELECT DP_PRO, DP_FILE, DP_NUM"
                + "FROM DISPATCH"                           // 900MB dispatch.dbf
                + "WHERE DP_PRO LIKE '%" + refNum + "%'";

            OleDbCommand MyQuery = new OleDbCommand(mySQL, connection);
            OleDbDataAdapter DA = new OleDbDataAdapter(MyQuery);

            DA.Fill(result);

            connection.Close();
        }
    return result;
}

因此，根据我的研究，我看到对 .CDX 文件的支持比对 .NDX 文件的支持更多，这很好.但是除了默认的慢速搜索文件之外，如何设置 OleDBConnection 以使用索引?

So from my research, I've seen more support for .CDX files than .NDX files, which is fine. But how do I set the OleDBConnection to make use of the indexing, besides the default slow search through the file?

我必须对 SQL 语句进行任何更改吗?还是供应商?或更改 DBF 文件的设置?通过研究，我还发现 .CDX 引用位于 .DBF 文件的标头中，因此提供程序知道关联哪些 .CDX 文件应该不是问题.

Are there any changes I have to make to the SQL statement? Or the provider? Or settings to change on the DBF file? From research, I've also found out that .CDX references are in the headers of the .DBF file, so it shouldn't be a problem for the provider to know which .CDX files are associated.

我们将非常感谢任何帮助.我是应届毕业生，没有 FoxPro 经验.:p

Any help would be greatly appreciated. I'm a recent grad with no FoxPro experience. :p

推荐答案

就复合索引 (CDX) 而言，您希望确保您的查询是 Rushmore 优化的.这就是您需要做的所有事情 - 如果有问题的表存在 CDX(如 DBF 的标题中所示)，那么它将被自动使用.查看上面的示例，因此假设字段 DP_PRO 上没有索引标记.

In terms of the compound index (the CDX) you want to make sure your queries are Rushmore-optimised. That's all you need to do - if a CDX is present for the table in question (as indicated in the header of the DBF) then it will be automatically used. Looking at the example above, the assumption therefore is that there is no index tag on the field DP_PRO.

这篇关于当我读取一个巨大的 FoxPro .DBF 时，如何使用 VFPOLEDB 提供程序来利用 .NDX/.CDX 索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！