Fetching speed aren't fast for unidrectional fetching for MySQL/MSSQL corelab DBExpress driver

[ccy]

Hi,

I am trying the following corelab drivers:

MSSQL dbx driver 2.50.6 04-Aug-05
Mysql dbx driver 2.60.7 16-Sep-05

both are demo version.

I have a 200K rows table on each MSSQL 2000 server, MySQL 4.1 Server and Firebird 1.51 server running on windows xp prof.

I try to traverse and fetch 1000 rows from the TSQLDataSet in unidirectional way:

SQL Query:
aSQL :=
'SELECT A.Code, A.Description, A.StockGroup, A.BalSQty, B.UOM, B.RefPrice
FROM ST_ITEM A LEFT OUTER JOIN
ST_ITEM_UOM B ON (A.Code=B.Code)'

var RS: TSQLDataset;
begin
i := 0;
RS.Commandtext := aSQL;
RS.Open;
while not RS.EOF do begin
inc(i);
for j := 0 to RS.Fields.Count - 1 do
RS.Fields[j].Value;
RS.Next;
break;
if i = 1000 then break;
end;
RS.Close;
end;

The above code fetch 1000 rows then stop.

Here is the result:

Borland Interbase DBX Driver: 0.016 seconds
CrLab MSSQL DBX Driver: 0.36 seconds
CrLab MYSQL DBX Driver: 0.047 seconds

It seems the result is quite different and Interbase perform very well here.

I am using TCRSQLconnection for both MsSQL/MYSQL testing with FetchAll set to False.

One problem for MySQL driver is the freeing of TSQLDataSet takes longer time as expected.

But If I fetch all rows, then the MSSQL has the best performance, then MySQL and finally Firebird.

Is there any setting for the CRLab driver that I need to set to improve the performance?

Best Regards,
Chee Yang

[Ikar]

> One problem for MySQL driver is the freeing of TSQLDataSet takes longer
> time as expected.
> But If I fetch all rows, then the MSSQL has the best performance, then
> MySQL and finally Firebird.

MySQL and MS SQL protocols don't allow to break fetching. As a result, on closing resultset client need to fetch all data from network buffer.

> Is there any setting for the CRLab driver that I need to set to improve
> the performance?

It's unlikely, but if you send us (dbxmda*crlab*com) complete small sample with script to create and fill tables then we'll be able to analyze this problem more detailed.

[ccy]

Hi

"MySQL and MS SQL protocols don't allow to break fetching. As a result, on closing resultset client need to fetch all data from network buffer. "

If the above statemen is true, so what is the function of FetchAll parameter in both MySQL/MSSQL crlab driver for?

[Ikar]

This parameter provides ability to delay this moment and decrease occupied RAM