Devart Forums

Howdy All!

I am finding on memory tables using TkbmMemTable that

1) TVirtualQuery is significantly FASTER than TxQuery on SELECT statements against a SINGLE table.

2) On multiple tables with JOINS, TxQuery is significantly FASTER! I In my final test, TxQuery did a a multiple JOIN in ~3 seconds. In TVirtualQuery, it took 17 minutes and 42 seconds.

I am finding TVirtualQuery is unusable with multi-table SELECT statements.

Please fix like all previous issues!

All the best,

Aggie85

Thank you for the information. We constantly test the performance of our products and make the necessary changes to the source code in case
of detecting the behavior that negatively affects the work speed. To investigate the mentioned behavior, please describe the problem in more detail.
Do you use the FetchAll property (https://www.devart.com/virtualdac/docs/ ... tchall.htm) when executing a query using VirtualQuery?
Which exact query is executed? How many records are in the database tables used in the query? Perhaps you can send us DDL scripts to create these tables, and also
provide a sample of filling them with data.

Howdy Max!

I just emailed you a zip file with two VTD files so you can see what I am talking about.

Yes I use FetchAll because I am copying the results into another memory table for reporting.

TxQuery using two TkbmMemTables executes and returns the ~26K records I emailed you in < 2 seconds.

TVirtualQuery against same TkbmMemTables takes over 17 minutes and 30 seconds to return the same ~26K records.

TVirtualQuery against the TkbmMemTables assigned to two TVirtualTables takes ~3 minutes and 42 seconds.

Since all data sources are in local memory, why is TVirtualQuery so SLOW fetching results?

Please fix or tell me what options I need to use. If I can get by this, I will finally have a replacement for TxQuery that I have been looking for the last 8+ years.

All the best,

Aggie85

Thank you for the information. We reproduced the problem. Currently, we are engaged in this problem solution. The necessary changes will be in the next VirtualDAC build

Howdy Max!

That is GREAT news! I really appreciate the great product and support y'all are providing!

I am half-way through my application replacing and testing TVirutalQuery. Once the JOINED performance is like the non-joined queries, my users are going to love my next upgrade!

Please let me know if you need anyone to test.

Have a great weekend.

Aggie85

Howdy Maxim!

I finally got some time to continue my replacement of TxQuery with TVirtualQuery with the latest version of VirtualDac.

While y'all did speed it up, TVirtualQuery is still incredibly SLOW on SELECT statements with JOINS.

A query with TxQuery against two TkbmMemTables that returns ~26k rows returns in < 2 seconds. The same query using TVirtualQuery takes over 115 seconds. If I copy the two tables to a TVirtualTable, the TVirtualQuery returns in ~32 seconds.

Why is TVirtualQuery so SLOW with TkbmMemTable and SELECT JOINS?

Thanks,

Aggie85

Maxim,

Just a follow up from playing with the options... the query is executed quickly the slowness is in the retrieval.

Thanks,

Aggie85

Please after testing inform us about the results

I wasn't clear on my last post. After testing many results, the SLOWNESS is still there (much improved over initial version, but still very SLOW).

What I noticed is that if I don't do a FetchAll but only fetch 25 records @ a time, the SQL statement executes quickly. The delay is when all the records are being fetched. This leads me to believe that the slowness is in the TVirtualQuery's data retrieval method(s).

The two tables I sent you to test should show this.

Have a great weekend,

Aggie85

Good day Maxim!

While the changes already made have sped up retrieval of data from TVirtualQuery much faster than before, it is still 50+ times slower than TxQuery which is not optimized in any fashion.

Any chance of narrowing the speed margin? Queries with SINGLE tables are much faster than TxQuery!

Thanks,

Aggie85

We continue to work on improving the performance of VirtualDAC and by now have achieved certain results. We tested the performance of VirtualDAC using your sample and made sure that the query used in the sample is executed much faster. In the very near future we will send you VirtualDAC night build so that you can check its work in your environment

Thanks.

Please send ASAP!

All the best,

Aggie85

The links to download UniDAC night build with the made fixes were sent to your email

Downloading now!

I will report back.

Much appreciated.

Aggie1985

Howdy Maxim!

With the latest version if I use TVirtualTables to hold the data, it is as fast as TxQuery using TkbmMemTable.

If I use TkbmMemTable to hold the data with TVirtualQuery, it is 10 to 20 times SLOWER. Any reason for the slowness when using TkbmMemTable? I

I can use TVirtualTable for reports but I am still curious on TkbmMemTable's performance issues with TVirtualQuery.

Thanks,

Aggie85