Devart Forums

Hi,

I am here requesting any ideas on what I am missing in settings..

The story is, I have a Delphi 2007 project that I have lifted to Delphi Tokyo.
In the D2007 project I have UniDAC 6.2.7 installed, in Delphi Tokyo it's 7.0.2

I have in the code some requests to the database performed with SQL like
aQry : TUniQuery;

aQry.SQL.add(' Select * from MyTable where MyIdxField = :value');
aQry.Prepare;
aQry.ParambyName('value').asString := '1223AA';

aQry.open;

By using the db-monitor, I can see that this takes approximately 0 milliseconds (of course it takes some time, but apparently less than 0.001 milliseconds).
Calling this code in a loop with 50,000 iterations is not a problem.

Now, in my Delphi Tokyo version of the application, the same code runs in something between 0.015 and 0.050 milliseconds, and now it becomes a problem.

So, I have of course tried to isolate this into an application that just proves the issue. I cannot as it seems.
So if I build myself an application that contains absolutely only this code, to connect to a database and run through the SQL 50.000 times. It makes no difference if I use my Delphi 2007 with my 6.2.7 UniDac or if I use Delphi Tokyo with 7.0.2 UniDAC

So, if anyone could give me any ideas on what the h.... could be going on, I'd be very greatfull.

It seems to me, that there could a setting somewhere on UniDac components, that for some reason I haven't gotten into my Delphi Tokyo version of the main project.

Any suggestions are welcome

Please specify the DBMS (and its version) you are working with.

Sorry, I forgot that bit. Sure:

I created and ran the explained project on Microsoft SQL Server Express 2012 edition.
I have also run the application on MS SQL Server full version 2016, with same results.

By further investigation, it seems that it could have something to do with Prepare and Autoprepare, but I cannot seem to figure out a reproducable pattern.

Jens

Update:

By putting the code in a loop, I actually do not call aQry.Open, but aQry.Refresh

if aQry.active then
begin
aQry.ParamByName('value').asString := newValue;
aQry.Refresh;
end
else
begin
aQry.ParamByName('value').asString := newValue;
aQry.Open;
end;

It does seem, that the problem I am seeing that the query is returning metadata every time, notmatter if I call Refresh or Close/open;

Hope this helps in your investigation

I am of course pursuing this...
Is there any way I can verify whether or not my call to Refresh actually retrieves metadata or not?

According to the documentation, calling Refresh on a parameterized query should not retrieve metadata. But as I am experiencing weird execution times, I have a feeling it might actually be doing something wrong. Not necessarily the component, it might easily by my usage of said component.

Kind regards
Jens Fudge

We've compared the performance of UniDAC 6.2.7 and UniDAC 7.5.13 and haven't noticed any major difference in the query execution time. The query execution speed is very high on the server , and most of that time is spent on sending the data over the network. In general, the execution time for a single query is so little that it's hard to evaluate the performance.

If you have an example of a query that is executed much faster with the older UniDAC version, please compose a small sample demonstrating the issue and send it us, along with scripts for creating database objects, so that we could investigate the described behavior of UniDAC.
You can send us the sample via the contact form on our website: devart.com/company/contactform.html

Stellar,

Thanks for replying. As i stated in my original question/post, I did actually try to reproduce this in a small isolated program.
Unfortunately with no luck.

I managed to create an application where I saw, that if I set autoprepare to true, AND called prepare, it was around 16 ms per execution in both D2007 and D Tokyo.
I also managed to prove that the execution was under 1 ms (db-monitor) when I just had either autoprepare or Prepare, also on both versions.

I am pretty sure that you are absolutely correct, that time is spent in sending data.
It does however seem, that in my real project, the components could be sending meta-data on every call. I am relatively convinced that the problem is on my side, but I cannot be 100% sure. I am here trying to get input on how to be able to fix this.
The db-monitor is helping me a lot, as I can see which SQL statements are sent to the server. Is there a setting or possibility to also see if the SQL server still regards the query as open/valid so that it shouldn't send back metadata?

Thanks for helping

I might be getting closer to this.
I have as explained created an isolated application to figure out what is going on.
I created an application in Delphi 2007 with UniDAC 6.2.7, and lifted this project to Delphi Tokyo with UniDAC 7.0.2.

By using the db-monitor, I noticed that this application that I created as a test had the varchar fields, or at least the one with the parameter, set to String[14]

But in my real project the parameter is set to WideString[14] on Delphi Tokyo, but String[14] on D2007

On the database the fields are created as Varchar fields (not NVarchar), and they are going to stay that way :-)

I am guessing there is somewhere this can be set?
Can it be set on a field basis, query basis or connection basis?

Thanks

I think I have it fixed now.
For some reason beyond me, setting the SQL to
SELECT field1, field2... fieldN from table1 where fieldPK = :FieldValue
and
Qry.ParamByName('FieldValue').asString := 'blabla';

where the field on the underlying database (MS SQL Server) is defined as Varchar, the type of the parameter is interpreted and thus set to be a ftWideString. This happens sometimes, and I havent figured out the conditions for this.

By inserting
Qry.ParamByName('FieldValue').dataType := ftString;
after assigning the value, I now get the expected results (Or, at least as fast as on D2007)

When specifying the value of a string parameter using the AsString property, UniDAC sets the data type for the parameter to be ftWideString by default. In the table, the "field1" field is of the Varchar type, in this case, because the sql data type does not match, the expression runs longer. To solve the problem, you can explicitly set the parameter type ftString, or set the parameter value using the AsAnsiString property.

Stellar,

That may be what "should" happen, but it's definitely not what actually happens.
In the test project I made, trying to figure this out I tried setting the parametervalue with both .AsString and with .Value
In both cases the datatype was set by UniDAC to ftString, which is the reason why my test project ran fast on both Delphi 2007 and Delphi Tokyo. And yes the value was set to ftString by UniDAC in both cases. So if ftWideString is the default, it's not working.

In my real project however, for reasons unknown to me, UniDAC does set the datatype of the parameter to ftWideString, and it does this whether or not I set the value using .AsString or .Value.

Having said this, I do appreciate your attempts to help. And I hope that you are able to help others with these findings in the future. I have earlier had a similar problem, also asked about here in this group, and I am going to see if the same issue applies.

DataType will have a value of ftWideString when you set the value of the string parameter with the help of the AsString property in Delphi 10.2 Tokyo. Only the value of the ParamStringAsAnsiString global variable can affect this behavior. By default, the value of the ParamStringAsAnsiString variable is set to False.
In order for all string parameters set using the AsString method to have the ftString type, you can set the global variable ParamStringAsAnsiString to True when initializing your application, for example:
Note that the ParamStringAsAnsiString variable is located in unit DBAccess.