Slow closing

[imagina]

Using SDAC 4.80.0.60, Delphi 2009
Microsoft SQL Server: 09.00.4053
Microsoft OLE DB Provider for SQL Server: 06.01.7600

When FetchAll of a TMSQuery is False, open is fast, but closing it take long time. Example: table with 100000 records, with FetchRecords to 100, opened in 0.02 seconds; closed in 9 seconds!
In the sample i go to last record, it take 9 seconds but then, closing is immediate. Seems that closing time is equal to fetching all records time.
If FetchAll property is True, closing is inmediate.

Thx

[AndreyZ]

Hello,

I cannot reproduce the problem. Please specify the table structure (script) you are using for testing. Also specify all non-default options you've set in the TMSConnection and TMSQuery components.

[imagina]

Hello

No there are non-default options in query, and the slowness is produced only when the client connect a remote server that is out the local lan, is connected via DSL in a remote town. I am aware of the short bandwidth of a DSL and the transfer time of retrieving data (that is reasonable).
To reproduce, try to connect a remote server (not in your local lan) and probably notice the lag in any query of a large table.
Note: I'm using native client with MARS active.

Thank you, anyway

[AndreyZ]

Please try using the following code:

Code: Select all

procedure TMainForm.BitBtnClick(Sender: TObject);
var
  con: TMSConnection;
  q: TMSQuery;
  tc: Cardinal;
begin
  con := TMSConnection.Create(nil);
  con.Server := 'server';
  con.Database := 'database';
  con.Username := 'username';
  con.Password := 'password';
  con.LoginPrompt := False;
  con.Options.Provider := prNativeClient;
  con.Options.MultipleActiveResultSets := True;
  con.Open;
  q := TMSQuery.Create(nil);
  q.Connection := con;
  q.FetchAll := False;
  q.FetchRows := 100;
  q.SQL.Text := 'select * from big_table'; // in my case big_table has 500000 records.
  tc := GetTickCount;
  q.Open;
  ShowMessage('Open: ' + IntToStr(GetTickCount - tc));
  tc := GetTickCount;
  q.Close;
  ShowMessage('Close: ' + IntToStr(GetTickCount - tc));
  q.Free;
  con.Free;
end;

Using this code query opening takes 0.1-0.25 seconds, and query closing doesn't take any time. Please check if you get the same time values when using this code.

[imagina]

Table with 150000 records:

Local lan: Open and close: <0.1sec

WAN (remote SQL Server): Open: <0.3sec Close: 6 seconds!
If insert a "q.Last" before "q.Close", closing is fast (<0.1sec), but going to latest record takes 5sec.

But don't worry, we are passing all code to UniDAC and converting databases to MySQL (our perfomance tests on a remote server demonstrates that is faster than SQL Server).

Thanks

[AndreyZ]

Unfortunately, we cannot reproduce the problem. We will be able to investigate this problem if you provide us access to your SQL Server database. If you want to help investigate this problem, please contact us by andreyz*devart*com e-mail.