Page 1 of 1
Slow closing
Posted: Fri 11 Mar 2011 08:09
by 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
Posted: Fri 11 Mar 2011 14:05
by 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.
Posted: Fri 11 Mar 2011 15:23
by 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
Posted: Mon 14 Mar 2011 12:33
by 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.
Times
Posted: Thu 24 Mar 2011 08:16
by 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
Posted: Thu 24 Mar 2011 15:45
by 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.