Performance Issues

Discussion of open issues, suggestions and bugs regarding Virtual Data Access Components for Delphi, C++Builder, Lazarus (and FPC)
Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Performance Issues

Post by Aggie85 » Fri 09 Jun 2017 23:26

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

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Performance Issues

Post by MaximG » Mon 12 Jun 2017 10:46

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.

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Performance Issues

Post by Aggie85 » Tue 13 Jun 2017 17:21

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

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Performance Issues

Post by MaximG » Fri 16 Jun 2017 06:18

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

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Performance Issues

Post by Aggie85 » Fri 16 Jun 2017 19:05

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

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Performance Issues

Post by Aggie85 » Thu 06 Jul 2017 19:06

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

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Performance Issues

Post by Aggie85 » Thu 06 Jul 2017 19:50

Maxim,

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

Thanks,

Aggie85

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Performance Issues

Post by MaximG » Fri 07 Jul 2017 14:26

Please after testing inform us about the results

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Performance Issues

Post by Aggie85 » Fri 07 Jul 2017 15:33

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

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Performance Issues

Post by Aggie85 » Tue 11 Jul 2017 14:35

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

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Performance Issues

Post by MaximG » Wed 12 Jul 2017 10:07

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

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Performance Issues

Post by Aggie85 » Wed 12 Jul 2017 12:02

Thanks.

Please send ASAP!

All the best,

Aggie85

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Performance Issues

Post by MaximG » Wed 12 Jul 2017 15:37

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

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Performance Issues

Post by Aggie85 » Wed 12 Jul 2017 15:40

Downloading now!

I will report back.

Much appreciated.

Aggie1985

Aggie85
Posts: 95
Joined: Wed 11 Nov 2015 17:03

Re: Performance Issues

Post by Aggie85 » Wed 12 Jul 2017 20:07

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

Post Reply