Slow closing

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
imagina
Posts: 3
Joined: Thu 10 Mar 2011 12:16

Slow closing

Post by imagina » Fri 11 Mar 2011 08:09

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

Post by AndreyZ » Fri 11 Mar 2011 14:05

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
Posts: 3
Joined: Thu 10 Mar 2011 12:16

Post by imagina » Fri 11 Mar 2011 15:23

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

Post by AndreyZ » Mon 14 Mar 2011 12:33

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
Posts: 3
Joined: Thu 10 Mar 2011 12:16

Times

Post by imagina » Thu 24 Mar 2011 08:16

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

Post by AndreyZ » Thu 24 Mar 2011 15:45

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.

Post Reply