search

joyfullservice / msaccess-vcs-addin

Synchronize your Access Forms, Macros, Modules, Queries, Reports, and more with a version control system.
Other
203 stars 40 forks source link

Query export is much slower than original plugin #112

Closed mmzmusicnotes closed 3 years ago

mmzmusicnotes commented 3 years ago

We've got a database with 25,000+ queries in it (this is because they are generated programmatically). Exporting those queries takes a very very long time with this plugin, at least the first time, even with fast save on, exporting query as SQL off, and aggressive sanitize off. It only takes ~10-15 minutes with the old version of the plugin.

Separately, I'd like us to consider enabling the option to just not export certain types of objects - but discounting that, any ideas as to why this might be so much slower?

Editing to add: I see very little difference between what this plugin does, and the original version: https://github.com/msaccess-vcs-integration/msaccess-vcs-integration/blob/master/MSAccess-VCS/VCS_Query.bas - so it seems to me this must be a problem with something surrounding the export process, not the query itself. That is, I think there's an overhead issue with high volumes of files.

joyfullservice commented 3 years ago

The performance report in the export log would be a great tool to drill down on what is causing the slow export... What do you see there after an export of all the queries?

You can also add performance monitors anywhere in the code to drill down on slow sections. They can be nested in or around other performance monitors. (It maintains a "call stack" of active monitors) Just remember to close any operation you start.

Perf.OperationStart "Some Code"
' Run other code
Perf.OperationEnd
A9G-Data-Droid commented 3 years ago

I have now collected good data on .ReadFile with and without the 128k chunking. In the most recent version where the chunking was removed I found my export time to be up. I put the chunking back in place and I'm seeing a large improvement. @joyfullservice I think you were right about the chunking. We should put it back in.

Reading Line at a time: Read File 129 1.09

Reading 128k chunks Read File 129 0.25

It's 4x faster? I find that hard to believe but that's what just happened on my system.

joyfullservice commented 3 years ago

Yes, I am seeing the same thing on my system when exporting a large project that involves 955 file reads. It went from 5 to 0.9 seconds. I will go ahead and add a commit to put that back in...

joyfullservice commented 3 years ago

Just a note on this issue, I think a big part of the performance issue was due to checking for and removing orphaned source files. This has been significantly reworked and optimized since this issue was created, and the original poster is no longer working directly on this project. I think I will go ahead and close this out for now, and we can revisit it later if needed.