TVirtualQuery speed

[JensFudge]

Hi,
Trying to do some performance optimization here.

I have a TUniQuery connecting to a MS SQL Server 2016.
I have the SQL something like
'SELECT * FROM MYTABLE WHERE ID = 'XYZ''
The key field is a stringfield, and I am NOT using parameters at this point (I know I could, but I am not)

substituting the 'XYZ' with something else for each run, I run this in a loop of 100,000 iterations, and this takes something like 100 seconds, so roughly 1000 records per second.

Now trying to do this faster, I have added another TUniQuery (tmpQry) and a TVirtualQuery.
The TUniQuery has SQL 'SELECT * FROM MYTABLE'
so its selecting everything, which is exactly what I want (there are 100,000 records so its nothing really)
and my TVirtualQuery has the sourcedatasets added with the tmpQry.

In my TVirtualQuery I have now 'SELECT * FROM MYTABLE WHERE ID = 'XYZ''
so basically now my TVirtualQuery is doing what I started with, only selecting from an in-memory table in stead of running over the network, on a server.

Why does this take close to 200 seconds? I have an increase in time of factor 2, where I was expecting some performance boost.

I did try to go via a TVirtualTable, but that made no difference.
I have also tried setting the IndexFieldNames property on the tmpQry, also no difference

Can you hint me what I am doing wrong?

Thanks in advance

Kind Regards

[MaximG]

Unfortunately, we could not reproduce the issue according to your description. For further issue investigation, please compose and send us a full sample, the execution of which causes an issue. In addition, we need a DDL script to create a table, which is accessed in this sample and a sample of its filling with test data.
This is convenient to do this via the e-support form (https://www.devart.com/company/contactform.html)