Devart Forums

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.

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.

Hello,

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

Thank you John Javůrek.

please respond. thank you

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.

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

Hello,
Please read the previous posts do not answer my question.

In your last response before it is written that you can not get the data types of parameters, because the function can be Overloaded.
===============================
Dimon
Devart Team
Joined: 05 Mar 2007
Posts: 2527
PostPosted: Tue 07 Dec 2010 13:53 Post subject: Reply with quote
This problem is connected with the specificity of the SQL Server work. Is that the point When you use functions with parameters in a SQL query, then this query Preparing before you should set information about data type of this parameter, because the function Can Be Overloaded and use prarameter Different types.
In your case you should set the property to DescribeParams False and set the data types of paramters Preparing before.
===============================

The example I have demonstrated that the function of the MSSQL can not override.

Let's go back to my problem.

************************************************** *********************
I need to validate the SQL query that contains parameters.
I need to test whether the query syntax correct.
************************************************** *********************

Unfortunately, with MSSQL or not if your components are the parameters placed in the call to the function or sub in the sql query (described in my first post).

Another example:
Query.Options.DescribeParams: = True;

Update Test
Set a = 0
Where b in
(Select id
From Test
WHERE id =: objid
)
Parameter Information can not Be derived from SQL statements with sub-select queries. Set parameter information before Preparing command.

Thank you for your answer.

I need to test the question whether it is correct without knowing the parameters.

Mssql to not overload the user function.
CREATE FUNCTION test_over(@a int)
RETURNS INTEGER
AS
begin
return 1
end

CREATE FUNCTION test_over(@a varchar(100))
RETURNS INTEGER
AS
begin
return 1
end

There is already an object named 'test_over' in the database.

How can I test a query that contains parameters whether it is valid?

Thank you for your reply.

Hello,

I have a problem with certain types of PREPARE SQL SELECT queries.

Parameters to the SQL query I can not set using Query.Params [0]. ASSTRING ... because the parameters do not know. This is a test application of SQL queries. QUERY.Prepare is used to determine the syntactic correct SQL query.


==========================================
Variant A - Query.Options.DescribeParams := True;

var
Query: TMSQuery;
...
begin
...
Query.Options.DescribeParams := True;
Query.SQL.Text := INSERT SQL CODE;
Query.Prepare;


1.
SELECT Name FROM sys.tables WHERE UPPER(Name) = UPPER(:TableName)
or
SELECT Code FROM StoreCards WHERE UPPER(Name) = UPPER(:Name)

Prepare Error
---------------------------
Incorrect syntax near ')'.
---------------------------

SELECT Name FROM sys.tables WHERE UPPER(Name) = UPPER('test')
is OK
SELECT Code FROM StoreCards WHERE UPPER(Name) = UPPER('test')
is OK

2.
SELECT ID FROM PaymentsForDocument(:APDocumentType, :APDocument_ID, :AEndDate)

Prepare Error
---------------------------
Syntax error, permission violation, or other nonspecific error.
---------------------------


SELECT ID FROM PaymentsForDocument('', '', 0)
is OK

PaymentsForDocument is table function:
ALTER FUNCTION [dbo].[PaymentsForDocument]
(
@APDocumentType CHAR(2),
@APDocument_ID CHAR(10),
@AEndDate FLOAT
)
RETURNS @PaymentsForDocument_TABLE TABLE
(
ID CHAR(10) COLLATE Czech_CS_AS,
DocumentType CHAR(2) COLLATE Czech_CS_AS,
DocDate$DATE FLOAT,
Amount NUMERIC(15, 3),
LocalAmount NUMERIC(15, 3),
PAmount NUMERIC(15, 3),
Currency_ID VARCHAR(10) COLLATE Czech_CS_AS,
CashDesk_ID VARCHAR(10) COLLATE Czech_CS_AS,
BankAccount_ID VARCHAR(10) COLLATE Czech_CS_AS,
OrdNumber INTEGER,
DocQueue_ID CHAR(10) COLLATE Czech_CS_AS,
Period_ID CHAR(10) COLLATE Czech_CS_AS,
LocalPAmount NUMERIC(15, 3)
) ...

3.
SELECT ID, (SELECT ID FROM StoreSubCards WHERE StoreCards.ID = :ID AND StoreSubCards.ID = :ID2) FROM StoreCards

Prepare Error
---------------------------
Syntax error, permission violation, or other nonspecific error.

SELECT ID, (SELECT ID FROM StoreSubCards WHERE StoreCards.ID = '' AND StoreSubCards.ID = '') FROM StoreCards
is OK


==========================================

Variant B - Query.Options.DescribeParams := False;

1.
SELECT ID FROM PaymentsForDocument(:APDocumentType, :APDocument_ID, :AEndDate)
Prepare Error
---------------------------
Project Project1.exe raised exception class EMSError with message 'Statement(s) could not be prepared.
Implicit conversion from data type sql_variant to float is not allowed. Use the CONVERT function to run this query.
Implicit conversion from data type sql_variant to char is not allowed. Use the CONVERT function to run this query.
Implicit conversion from data type sql_variant to char is not allowed. Use the CONVERT function to run this query.'. Process stopped. Use Step or Run to continue.
---------------------------

Thanks for the reply.
Ing. Jan Javurek
Floresps s.r.o.
Praha
Ceska Republika

links to my previous questions about the PREPARE:
http://www.devart.com/forums/viewtopic. ... highlight=
http://www.devart.com/forums/viewtopic. ... highlight=
http://www.devart.com/forums/viewtopic. ... ede05ee76c

thank you

ENGLISH:
Hello,

Query.SQL.Text: = 'SELECT * FROM STORECARS.Code = :Code'
Query.Prepare;

Evaluate Query.FieldDefs [0]. DataType = ftUnknown

How do I keep TMSQuery returned the correct data type parameter?

The solution is somehow characteristic DescribeParams how to turn it on and use?
Thank you for your reply.

CZECH:
Dobrý den,

Query.SQL.Text := 'SELECT * FROM STORECARS.Code = :Code'
Query.Prepare;

Query.FieldDefs[0].DataType = ftUnknown

Jak zajistím, aby TMSQuery vrátilo správný datový typ parametru?

Řeší to nějak vlastnost DescribeParams, jak jí zapnout a používat?
Děkuji za odpoveď.

CZ:
Jak mám nastavit profiler, aby se v něm zobrazovali události StartTransaction, Commit a Rollback volané z SDAC TMSConnection?

Příkazy ne jsou standardně vidět jako TSQL.

Děkuji za odpověď.

EN:
How do I set the MSSQL profiler to it depicted events StartTransaction, Commit and Rollback called from SDAC TMSConnection?

Commands are not normally seen as TSQL.

Thank you for your reply.

Log profiler:

Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Project1.exe jan.javurek QGIRIT\jan.javurek 6400 56 2010-04-23 17:45:34.020
SQL:BatchStarting SET LOCK_TIMEOUT 2000 Project1.exe jan.javurek QGIRIT\jan.javurek 6400 56 2010-04-23 17:45:34.020
SQL:BatchCompleted SET LOCK_TIMEOUT 2000 Project1.exe jan.javurek QGIRIT\jan.javurek 0 0 0 0 6400 56 2010-04-23 17:45:34.020 2010-04-23 17:45:34.020
SQL:BatchStarting SET NO_BROWSETABLE ON Project1.exe jan.javurek QGIRIT\jan.javurek 6400 56 2010-04-23 17:45:34.023
SQL:BatchCompleted SET NO_BROWSETABLE ON Project1.exe jan.javurek QGIRIT\jan.javurek 0 0 0 0 6400 56 2010-04-23 17:45:34.023 2010-04-23 17:45:34.023
SQL:BatchStarting SELECT * FROM StoreCards Project1.exe jan.javurek QGIRIT\jan.javurek 6400 56 2010-04-23 17:45:34.023
SQL:BatchCompleted SELECT * FROM StoreCards Project1.exe jan.javurek QGIRIT\jan.javurek 0 7 0 9 6400 56 2010-04-23 17:45:34.023 2010-04-23 17:45:34.030
SQL:BatchStarting SELECT * FROM Firms Project1.exe jan.javurek QGIRIT\jan.javurek 6400 56 2010-04-23 17:45:34.037
SQL:BatchCompleted SELECT * FROM Firms Project1.exe jan.javurek QGIRIT\jan.javurek 0 28 0 26 6400 56 2010-04-23 17:45:34.037 2010-04-23 17:45:34.063
Audit Logout Project1.exe jan.javurek QGIRIT\jan.javurek 0 35 0 50 6400 56 2010-04-23 17:45:34.020 2010-04-23 17:45:34.070
Trace Stop 2010-04-23 17:45:41.957

Delphi code:

var
MSConnection: TMSConnection;
Query: TMSQuery;
begin
MSConnection:= TMSConnection.Create(nil);
Query := TMSQuery.Create(nil);
try
MSConnection.Database := EdDatabase.Text;
MSConnection.Authentication := auWindows;
MSConnection.Server := EdServer.Text;
MSConnection.Connect;

Query.Connection := MSConnection;
Query.SQL.Text := 'SELECT * FROM StoreCards';
Query.Execute;

MSConnection.StartTransaction;
try
Query.SQL.Text := 'SELECT * FROM Firms';
Query.Execute;
MSConnection.Commit;
except
MSConnection.Rollback;
end;
finally
Query.Close;
Query.Free;
MSConnection.Disconnect;
MSConnection.Free;
end;
end;

or TMSQuery?

Hello
how set TrimFixedChar := False on TMSConnection?

Thanks.

column type IMAGE insert size = 0 varchar(1) '' OK

How insert data size = 0 to column type VARBINARY(MAX) not null.

column DriverParams
Query.Params.CreateParam(ftString, 'DriverParams', ptInput).AsString := '';

exec sp_executesql N'INSERT INTO DefinedB2BImports(ID,DriverName,OBJVERSION,Description,DriverGUID,CLSID,DriverParams,CLSIDName) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8)',N'@P1
varchar(10),@P2 varchar(34),@P3 int,@P4 varchar(40),@P5 varchar(26),@P6 varchar(26),@P7 varchar(1),@P8 varchar(15)','1000000000','Import dokladů do faktur přijatých',1,'Import faktury vydané
do faktury přijaté','2TIXBDUFGIEOJIPYQUM4JJBBPC','42HE04FZGJD13ACM03KIU0CLP4','','Faktura přijatá'

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.