delphi - Why is TFDTable.FieldDefs.Update so slow? - Stack Overflow

admin管理员组

文章数量:1392002

I want to know the field definitions (field names, types, and sizes) for a group of PostgreSQL (it seems the database type does not really matter, it could be Oracle or SQL Server) tables using TFDTable. The table has about 20 columns and 5 million records. But this line of code needs 20 seconds:

MyFDTable.FieldDefs.Update;

(If I change the TableName property without calling this method, I get the same field definitions for each table).

When I use old BDE TTable (through ODBC) the same method executes in 1 second.

Everything is getting much worse when the table has a BLOB column with large data size. When the total table sizes 20 GiB this method executes 20 minutes for TFDTable, and still one second for TTable.

I suppose, FireDac does implicit Open and FetchAll during the FieldDefs.Update, but why?

Here is an example of how I connect the PostgreSQL database:

• FireDac:

MyFDCon.Params.Add('DriverID=PG');
MyFDCon.Params.Add('Server=MyPgServerIP');
MyFDCon.Params.Add('Port=5432');        
MyFDCon.Params.Add('Database=db1');  
MyFDCon.Params.Add('CharacterSet=utf8');

MyFDCon.Connected := True;  

MyFDTable.Connection := MyFDCon;
MyFDTable.TableName := MyTableName;

MyFDTable.FieldDefs.Update; //too slow for large tables

//populating field definitions:
for i := 0 to MyFDTable.FieldDefs.Count - 1 do
begin
  ...
end;

• BDE:

MyBdeTable.DatabaseName := MyODBCDSN;
MyBdeTable.TableName := MyTableName;

MyBdeTable.FieldDefs.Update; //very fast even for large tables

//populating field definitions:
for i := 0 to MyBdeTable.FieldDefs.Count - 1 do
begin
  ...
end;

So, my questions:

1. Why does FireDac refresh the field definitions so slowly, comparing to BDE?
2. Is there a way to speed up this call? Maybe some table options exist to control this?
3. Is there another (faster) way to get the field definitions for the table without needing to query system tables?

P.S.:

• I cannot use BDE, because the solution must work for 64-bit too.

本文标签： delphiWhy is TFDTableFieldDefsUpdate so slowStack Overflow