Nexus vx UniDac: different result set and slower SQL

[Yves]

I've made a test tool and database to demonstrate my questions. The zip is about 20MB and can be downloaded from Wetransfer. http://we.tl/VMa6zn0BBx Available till March 13

Q1.
We use a Nexus database. When I execute a SQL over UniDac it results in different result set then Nexus. One result set is directly executed by a Nexus components. The other result set is executed by UniDac components. It looks like the handling of parameters in the SQL is handled in a different way. Especially GUID with value {00000000-0000-0000-0000-000000000000}. See files in attachment.

Please note {00000000-0000-0000-0000-000000000000} is not the same as NULL
In a demo table Ann & Adele have UserID NULL and Celine, Marc & Peter have UserID {00000000-0000-0000-0000-000000000000}

The Nexus components show the correct records, but unfortunately not by UniDac. Do I miss something or is it a bug?

Q2
When executing the following SQL in the test application. The execution time by Nexus is < 16ms but via UniDac very long > 1000ms

Code: Select all

SELECT ItemID, KnownLength FROM PlaylistItem
WHERE ItemID in(
  GUID '{F3246F94-6A8F-413E-8283-1D1F5FC9FDEB}',
  GUID '{49F705C0-4CA3-48B9-BF35-9E8577A9C24B}'
)

In the Nexus DB Enterprise Manager you notice the same very fast execution time and in Devart DB Monitor a simillar long time. This to exclude my test application.
Because the same SQL is executed by the Nexus database server, I don't understand why it takes that long time by UniDac components.
That text SQL statement is generated runtime, especially the where clause.
How to speed up UniDac?

Installed Devart components: unidac62d21pro.exe for Delphi XE7

Thanks you

[ViktorV]

1. Currently, when setting the TUniParam.DataType property, the parameter value is cleared. Therefore, to make your sample work correctly, you should replace the following lines:

Code: Select all

    UniQuery.Params[0].AsString := EditGUID.Text;
    UniQuery.Params[0].DataType := ftGuid;

with

Code: Select all

    UniQuery.Params[0].DataType := ftGuid;
    UniQuery.Params[0].Value := EditGUID.Text;

2. We are investigating this behavior of UniDAC and will inform you about the results.

[Yves]

1.
Thx, but when I try to apply your solution an exception is raised on UniQuery.Open

NexusDB: <nil pointer>: Query execution failed:
Type mismatch (nxtGUID <> nxtWideString) when comparing at line 2, pos 14 [ UserID ='{00000000-0000-0000-0000-000000000000}']

[ViktorV]

You may get thiss error in case if you use the TUniParam.AsString property instead of the TUniParam.Value property to set the parameter value. Since the TUniParam.DataType property will be automatically set to ftWideString on using the TUniParam.AsString property.

[Yves]

Yes indeed. I swapped the lines and did not noticed the TUniParam.AsString was changed to TUniParam.Value. Sorry.

About 2, consering Slower SQL by Unidac vs Nexus
I've found a workaround but I don't understand the executions times.
2.a Add

Code: Select all

ORDER BY KnownLength

The execution time is 0..16ms, the same fast time when using only Nexus components instead of UniDac.
2.b FYI, when adding

Code: Select all

ORDER BY ItemID

The execution time is about 2000ms against 1200 ms without "Order By". So, still slower.

Strange because ItemID is the default primary key index.
There is no index on KnownLength but it results in the fastest execution times.
Actually I don't need an order in the result set.