Performance Issues
Performance Issues
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
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
Re: Performance Issues
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.
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.
Re: Performance Issues
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
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
Re: Performance Issues
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
Re: Performance Issues
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
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
Re: Performance Issues
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
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
Re: Performance Issues
Maxim,
Just a follow up from playing with the options... the query is executed quickly the slowness is in the retrieval.
Thanks,
Aggie85
Just a follow up from playing with the options... the query is executed quickly the slowness is in the retrieval.
Thanks,
Aggie85
Re: Performance Issues
Please after testing inform us about the results
Re: Performance Issues
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
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
Re: Performance Issues
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
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
Re: Performance Issues
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
Re: Performance Issues
Thanks.
Please send ASAP!
All the best,
Aggie85
Please send ASAP!
All the best,
Aggie85
Re: Performance Issues
The links to download UniDAC night build with the made fixes were sent to your email
Re: Performance Issues
Downloading now!
I will report back.
Much appreciated.
Aggie1985
I will report back.
Much appreciated.
Aggie1985
Re: Performance Issues
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
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