How repeatedly to obtain the data?

[[email protected]]

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]

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''';

[[email protected]]

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.

[[email protected]]

please respond. thank you

[Dimon]

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.

[[email protected]]

Hello,

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

Thank you John Javůrek.

[AndreyZ]

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

[[email protected]]

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]

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

[JGordon]

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

[AndreyZ]

This fix is included in the latest SDAC version 6.1.3.

[[email protected]]

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]

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.