How repeatedly to obtain the data?

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

How repeatedly to obtain the data?

Post by jan.javurek@floresps.cz » Mon 10 Oct 2011 21:49

How quickly can I get data from a query that has repeatedly many times I cry.

Example: 1000x call query
SELECT ID, Name, ...
FROM StoreCards
WHERE ID =: objid

The duration of the execution of queries on the SQL server (as measured using SQL Server Profiler)
SUM (Duration) = 91.595 ms (1000x query)
AVG (Duration) = 91us (1 query)

The duration of the execution of queries using the TMSQuery
3.02 seconds

The slowdown is caused by using DataSet in TMSQuery. Initialization takes a very long field in the dataset.
Is there a way to quickly get the data as there is a solution without using dataset TMSQuery?
TMSQuery only used for the sequential reading of rows of the outcome.
The data obtained in a real application, only TMSQuery overload in their own data structures.

Thank you for your advice.
Jan Javůrek.

The code is just a demonstration of need repeatedly to read data from a query prepare.

procedure TForm1.Button1Click(Sender: TObject);
var
i: Integer;
t: Cardinal;
MSConnection1: TMSConnection;
MSQuery1: TMSQuery;
begin
MSConnection1 := TMSConnection.Create(nil);
MSConnection1.Database := 'FLORES';
MSConnection1.Username := 'sa';
MSConnection1.Password := 'xxx';
MSConnection1.Options.ApplicationName := 'test';
MSConnection1.ConvertEOL := False;
MSConnection1.Options.DefaultLockTimeout := 432000;
MSConnection1.Options.Provider := prNativeClient;
MSConnection1.Connect;
MSQuery1 := TMSQuery.Create(nil);
MSQuery1.ReadOnly := True;
MSQuery1.Connection := MSConnection1;
Memo1.Clear;
try
t := GetTickCount;
MSQuery1.SQL.Text := 'SELECT ID, Name, * FROM StoreCards WHERE ID = :OBJID';
MSQuery1.Params[0].AsString := '0LW0000101';
MSQuery1.Prepare;
Memo1.Lines.BeginUpdate;
for i := 0 to 999 do
begin
if i mod 2 = 0 then
MSQuery1.Params[0].AsString := '0LW0000101'
else
MSQuery1.Params[0].AsString := '0M70000101';
MSQuery1.Open;
if not MSQuery1.Eof then
Memo1.Lines.Add(MSQuery1.Fields[0].AsString + ' = ' + MSQuery1.Fields[1].AsString);
MSQuery1.Close;
end;
t := GetTickCount - t;
MSQuery1.Execute;
Memo1.Lines.Add(IntToStr(t));
Memo1.Lines.EndUpdate;
finally
MSConnection1.Disconnect;
end;
end;


Profiling code from AQTime
Routine Name Time with Children(secound) Hit Count
TForm1::Button1Click 3,02 1
TCustomMSDataSet::SetActive 2,76 2082
TCustomDADataSet::SetActive 2,75 2002
TCustomMSDataSet::OpenCursor 2,71 1001
TCustomDADataSet::OpenCursor 2,71 1001
TMemDataSet::OpenCursor 2,7 1001
TCustomMSDataSet::InternalOpen 2,7 1001
TCustomDADataSet::InternalOpen 2,7 1001
TMemDataSet::InternalOpen 2,66 1001
TOLEDBRecordSet::Open 0,55 1002
TCRRecordSet::Open 0,55 1002
TMemData::Open 0,55 1002
TData::Open 0,55 1002
TOLEDBRecordSet::InternalOpen 0,55 1002
TCRRecordSet::InternalOpen 0,55 1002
TCRRecordSet::ExecFetch 0,55 1002
TOLEDBRecordSet::Fetch 0,32 2081
TOLEDBRecordSet::FetchAll 0,32 1001
TOLEDBRecordSet::DoFetchAll 0,32 1001
TOLEDBCommand::Execute 0,23 1002
TOLEDBRecordSet::ExecCommand 0,23 1002
TCRRecordSet::ExecCommand 0,22 1001
DoExecute 0,22 1002
OpenOrExec 0,21 1002
InitBlock 0,12 1079
TCustomDAConnection::SetConnected 0,1 2009
TCustomDAConnection::Connect 0,1 2005
TCustomDAConnection::PerformConnect 0,1 1
TCustomMSConnection::DoConnect 0,1 1
TCustomDAConnection::DoConnect 0,1 1
TOLEDBConnection::Connect 0,1 1
FirstFetch 0,09 1002
TOLEDBRecordSet::AllocFetchBlock 0,09 1002
TFieldDescs::GetItems 0,07 2811983
TCustomDADataSet::Prepare 0,06 1
TMemDataSet::Prepare 0,06 1
TCRRecordSet::Prepare 0,06 1

SQL Server profiler log:
StartTime TextData Duration in ms
2011-10-10 23:12:14.193 declare @p1 int set @p1=1 exec sp_prepare @p1 output,N'@P1 varchar(10) OUTPUT',N'SELECT ID, Name, * FROM StoreCards WHERE ID = @P1',1 select @p1 0.156000
2011-10-10 23:12:14.193 exec sp_execute 1,'0LW0000101' 0.097000
2011-10-10 23:12:14.210 exec sp_execute 1,'0M70000101' 0.087000
2011-10-10 23:12:14.210 exec sp_execute 1,'0LW0000101' 0.178000
2011-10-10 23:12:14.210 exec sp_execute 1,'0M70000101' 0.128000
2011-10-10 23:12:14.227 exec sp_execute 1,'0LW0000101' 0.174000
...
=> 3ms delay between the client queries

AndreyZ

Post by AndreyZ » Tue 11 Oct 2011 13:30

You can speed up data obtaining by fetching more data at once. For example, you can use the following query:

Code: Select all

SELECT ID, Name FROM StoreCards WHERE ID BETWEEN 1 AND 999
Also you can try to avoid using parameters. SQL Server executes queries that don't contain parameters faster. For example:

Code: Select all

MSQuery1.SQL.Text := 'SELECT ID, Name, * FROM StoreCards WHERE ID = ''0LW0000101''';

jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

RE:

Post by jan.javurek@floresps.cz » Fri 14 Oct 2011 17:24

Hello,

Your answer does not correspond at all to my question.

I need to speed up repeated calls same SQL queries as shown in the demo program.
So to avoid slow re-initialize the dataset.

It is clear to me that I can read the records at once, but it does not solve the recall, which is very slow due to the initialization dataset.

How can I speed up repeated calls to the same SQL query?

As shown in the profile obtained using AqTime profiler.
Time to get data from TMSQuery is 2.76 seconds TMemDataSet: InternalOpen 2.66 seconds
Time in OLE DB is just TOLEDBRecordSet: InternalOpen 0.55 seconds.

How to eliminate slow dataset with repeated calls to the same query?

Thank you for your advice.
Jan Javůrek.

jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

please respond. thank you

Post by jan.javurek@floresps.cz » Tue 18 Oct 2011 23:26

please respond. thank you

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 20 Oct 2011 13:07

Thank you for information. We have reproduced this problem. This behaviour is connected with creating the TField and TFieldDef object on every dataset opening. We have fixed this problem for prepared datasets. This fix will be included in the next SDAC build.

jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

Thank

Post by jan.javurek@floresps.cz » Wed 26 Oct 2011 12:18

Hello,

Please write me the version number, in which improvements will be done.

Thank you John Javůrek.

AndreyZ

Post by AndreyZ » Wed 26 Oct 2011 15:00

This fix will be available in the next SDAC version 6.1.1.

jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

Post by jan.javurek@floresps.cz » Fri 04 Nov 2011 10:07

Hello,

When will those versions to download?

I do not know if your repair will solve my problem with the speed dataset used in TMSQuery.
There is some low-level access without using a dataset in TMSQuery?

Thank you for your answer.

AndreyZ

Post by AndreyZ » Fri 04 Nov 2011 12:50

We plan to release SDAC approximately in a month.
There is no low-level access to data in SDAC.

JGordon
Posts: 10
Joined: Tue 27 Sep 2011 16:43

Is this fixed in the update just released

Post by JGordon » Mon 28 Nov 2011 13:59

Does the 6.1 update contain this update from last week. You mentioned it being fixed in 6.1.1.

AndreyZ

Post by AndreyZ » Mon 28 Nov 2011 16:10

This fix is included in the latest SDAC version 6.1.3.

jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

Re: How repeatedly to obtain the data?

Post by jan.javurek@floresps.cz » Thu 20 Sep 2012 20:42

Hello

the problem is not fixed even in version 6.5.9 for Delphi 7

Starting already prepared query is still as slow.

Please repair and testing.

Thank Ing. Jan Javůrek.

AndreyZ

Re: How repeatedly to obtain the data?

Post by AndreyZ » Fri 21 Sep 2012 10:16

I cannot reproduce this problem. Please try creating a small program that demonstrates this performance problem and send it to andreyz*devart*com . Also, please specify the following:
- the script to create your table;
- the exact version of SQL Server you are using. You can learn it from the Info sheet of TMSConnection Editor.

Post Reply