在Delphi中实现VFP6的查询速度

发表:不详阅读:311次关键字：不详字体:[大中小]

在Delphi中访问Foxpro的数据库效率非常低，一条简单的SQL都要检索半天。对于百万条记录
的表BDE根本拖不动。笔者因为经常要处理这种庞大的数据表，所以在这上面花足了心思，终于
探索出一种简单可靠的方法。
  首先，我们现用VFP6写一个类，这个类很简单，如下所示：
Define Class mFoxEngine As Session OlePublic
       mSQLString=''
       mRecCount=0
       mSpentCount=0

       Procedure InitEngine
         Set Collate to 'MACHINE'
Set Talk off
Set safe off
Set dele off
Set Excl off
set cent on
set date to ansi
Close Data
      EndProc

      Function RunSQL
         lStart=Seco()
         pSQLString=This.mSQLString
         &pSQLString
         This.mRecCount=_Tally
         Close Data
         This.mSpentCount=Seco()-lStart
         if _Tally=0
            Retu .f.
         else
            Retu .t.
         Endif
      EndFunc

EndDefine

把此类编译成支持多线程的COM内进程组件。（VFP6必须已经上了Pack4补丁）
注册该COM的DLL。
   在Delphi中可以使用该组件了。简单的用法为：
var myObject:Variant;
begin
     myObject:=CreateOleObject('FoxEngine.mFoxEngine');
     myObject.InitEngine;  //初使化引擎
     myObject.mSQLString:='Sele * from XXX into dbf XXX';  //随你怎么用，只要VFP支持的SQL和函数就可，也是我们想要的。
     if not VarIsEmpty(myObject) then myObject:=null; //释放。可选因为Delphi会自动释放
end;

上述做法可以很好的和VFP6的引擎结合，达到意想不到的效果。
笔者同时把此VFP6引擎做成了一个控件，源码如下：
unit FoxQuery;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db,ComObj;

type
  TFoxQuery = class(TDataSet)
  private
    { Private declarations }
    FSQLString:String;
    FActive:Boolean;
    FFoxObject:Variant;
    FSQL:TStrings;
    FRecCount:Integer;
    FVersion:String;
    FSpentCount:Integer;

    Procedure FoxSetActive(Value:Boolean);
    Procedure SetSQL(Value:TStrings);
    Procedure Execute;

  protected
    { Protected declarations }
  public
    { Public declarations }
    Constructor Create(AOwner: TComponent); override;
    Destructor Destroy; override;
    Procedure ClearSQL;

  published
    { Published declarations }
    Property Active:Boolean Read FActive Write FoxSetActive;
    Property SQL:TStrings Read FSQL Write SetSQL;
    Property RecCount:Integer Read FRecCount;
    Property SpentCount:Integer Read FSpentCount;
    Property SQLString:string Read FSQLString Write FSQLString;
    Property Version:String Read FVersion Write FVersion;
  end;

procedure Register;

implementation
{$R *.res}
Procedure TFoxQuery.FoxSetActive(Value:Boolean);
begin
    if Value then
    begin
       if FSQLString='' then
       begin
          MessageBox(0,'SQL Command is empty!','Error',mb_iconstop);
          FActive:=false;
       end else begin
          FActive:=true;
          Execute;
       end;
    end;
end;{}

Procedure TFoxQuery.ClearSQL;
begin
   FSQLString:='';
end;

Procedure TFoxQuery.SetSQL(Value:TStrings);
Var i:integer;
Begin
  if SQL.Text <> Value.Text then
  begin
    SQL.BeginUpdate;
    try
      SQL.Assign(Value);
    finally
      SQL.EndUpdate;
    end;
  end;
  FSQLString:='';
  if SQL.Count<>0 then
  begin
    for i:=0 to FSQL.Count-1 do
    begin
        FSQLString:=FSQLString+FSQL.Strings[i];
    end;
  end;
end;

Procedure TFoxQuery.Execute;
begin
    FFoxObject.mSQLString:=FSQLString;
    FFoxObject.RunSQL;
    FRecCount:=FFoxObject.mRecCount;
    FSpentCount:=FFoxObject.mSpentCount;
end;

Constructor TFoxQuery.Create(AOwner: TComponent);
begin
    inherited Create(AOwner);
    FFoxObject:=CreateOleObject('FoxEngine.mFoxEngine');
    FFoxObject.InitEngine;
    FSQL:=TStringList.Create;
    FVersion:='Version 1.0.0';
end;{}

Destructor TFoxQuery.Destroy;
begin
    inherited Destroy;
    if not VarIsEmpty(FFoxObject) then FFoxObject:=Null;
    FSQL.Free;
end;

procedure Register;
begin
  RegisterComponents('Data Access', [TFoxQuery]);
end;

end.

以上内容在Delphi5和VFP6(Pack4)中试用通过。
如想导论此技术请联系笔者：
Mail:  tom_yea@163.com
HomePage:  http://www.tomye.com

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。