Devart Forums

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

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

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?

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