ADODB performance problem

Question

Hi All, (Win10, Win11)To highlight a performance problem (with ADODB/ACCESS), on a heavy client, I wrote two small programs one in VB.NET (and VBA), the other in CPP/MFC. Using the same component (msado15.dll).This piece of code does nothing but connect to two ACCESS files (with a single table of 15,000 records), and via a recordset copy from one database to the other.Under VB.NET, it takes 1 second.Under CPP, on a machine that has the runtime access 2013 x64, it takes 9 seconds. (i spend 99% of my time in AddNew() )...Under CPP, on a machine that has the runtime access 2016 x64 OR Office365, it takes 20 minutes. (i spend 99% of my time in AddNew() )...The target computer must have Office365...I need an issue...PS : My two sln (exe/cpp/...) are available...There is no sensitive data...A short running test...|Nom de la fonction|Total $$unit, %$$|Self $$unit, %$$|Nombre d'appels|Module||-|-|-|-|-||*+*D:\users\phili\Downloads\Test\_ADO\_Cpp\_VB\test\_minimal\x64\Release\test.exe $PID : $|115,73s $100,00*%$|0ns $0,00*%$|0|Plusieurs modules||\|*+*\_\_report\_gsfailure|115,73s $100,00*%$|9,20?s $0,00*%$|1|test||\|\|*+*\_\_scrt\_common\_main\_seh|115,73s $100,00*%$|13,20?s $0,00*%$|1|test||\|\|\|*+*main|115,73s $100,00*%$|654,83ms $0,57*%$|1|test||\|\|\|\|*-*$$Native$$ msado15.dll!0x00007ffe8947bc40|104,82s $90,58*%$|104,82s $90,58*%$|6215|msado15|&nbsp; &nbsp; &lt;= AddNew() !!!|\|\|\|\|*-*$$Native$$ ucrtbase.dll!0x00007fff373ccad0|9,31s $8,05*%$|9,31s $8,05*%$|1|ucrtbase||\|\|\|\|*-*ADODB::Connection15::Open|413,29ms $0,36*%$|8,80?s $0,00*%$|2|test||\|\|\|\|*-*$$Native$$ msado15.dll!0x00007ffe8947c010|101,83ms $0,09*%$|101,83ms $0,09*%$|2|msado15||\|\|\|\|*-*printf|96,90ms $0,08*%$|157,30?s $0,00*%$|214|test||\|\|\|\|*-*$$Native$$ msado15.dll!0x00007ffe8947d3d0|91,69ms $0,08*%$|91,69ms $0,08*%$|74568|msado15||\|\|\|\|*-*$$Native$$ msado15.dll!0x00007ffe8947d380|50,46ms $0,04*%$|50,46ms $0,04*%$|74568|msado15||\|\|\|\|*-*$$Native$$ msado15.dll!0x00007ffe894d8fc0|30,92ms $0,03*%$|30,92ms $0,03*%$|149136|msado15||\|\|\|\|*-*$$Native$$ msado15.dll!0x00007ffe8947bfc0|28,68ms $0,02*%$|28,68ms $0,02*%$|6214|msado15||\|\|\|\|*-*$$Native$$ combase.dll!0x00007fff39403f10|26,94ms $0,02*%$|26,94ms $0,02*%$|5|combase||\|\|\|\|*-*$$Native$$ msado15.dll!0x00007ffe89513fc0|17,39ms $0,02*%$|17,39ms $0,02*%$|149137|msado15||\|\|\|\|*-*$$Native$$ msado15.dll!0x00007ffe894c1480|15,11ms $0,01*%$|15,11ms $0,01*%$|1|msado15||\|\|\|\|*-*$$Native$$ msado15.dll!0x00007ffe8947c230|14,46ms $0,01*%$|14,46ms $0,01*%$|149137|msado15||\|\|\|\|*-Native\]msado15.dll!0x00007ffe8947d2b0|11,32ms\0,01*%\)|11,32ms\0,01*%\)|149136|msado15|Regards

philippeillinger · Answer

I have modified my test code with your suggestions...and i have 8 sec...thats really better...My VB code takes only 1 sec...But let us not be more royalist than the king...I will apply this tomorrow, on the big software...I hope that the user experience will be 'satisfactory'.PS : Private Message ? Where ? (en Francais ca irait probablement encore mieux...LOL )

jipebenard · Answer

Hi philippeillinger&nbsp;&nbsp;I looked at your two SLNs but neither found any hint on ADO or Provider parameters till now.Agree with you: .Net must have settings overloads over ADODB/ADOX interops that helps optimize the trick, avoiding so much re-read from the destination db file...&nbsp;This piece of code does nothing but connect to two ACCESS files (with a single table of 15,000 records), and via a recordset copy from one database to the other.I need an issue...However, if your goal is here to get a large batch processing logic, why don't you use the settings made for that kind of job (LockType=adLockBatchOptimistic and using UpdateBatch()) ?FYI:&nbsp;using your "test cpp" sample with those two changes =&gt; process time &lt;3,000ms.

philippeillinger · Answer

jipebenard&nbsp;where have you find my test.cpp ?If your batch sample use fields.getvalue and fields.putvalue, and if this takes less than 3sec, i am very interested...My test.cpp, i only an extract off a very big client. The reading is very fast, only writing is slow, and the write algo is field by field, modifiing this is not very easy, the fields are stored in maps...RegardsPS : have you seen the #import ".......msado15.dll" directive ?

jipebenard · Answer

Hi&nbsp;philippeillinger&nbsp;!&nbsp;where have you find my test.cpp ?Answer provided in private message.&nbsp;If your batch sample use fields.getvalue and fields.putvalue, and if this takes less than 3sec, i am very interested...As stated, set your RS LockType to&nbsp;adLockBatchOptimistic&nbsp;and then use RS-&gt;UpdateBatch when appropriate.&nbsp;Therefore, I have had time to take a deeper look on your VB sample and more especially on its RS_out Cursor settings (state after opening the RS😞-&nbsp;CursorLocation&nbsp;= 2 (-&gt; adUseServer)- CursorType&nbsp;= 1 (-&gt; adOpenKeySet)&nbsp;If you apply the same configuration in your C++ sample code RS parameters, then both (VB &amp; C++ samples) RS settings will be aligned -&nbsp;more especially the DBPROP_MAXPENDINGROWS will be set to 1 in the C++ running sample. And performance also aligned&nbsp;(still &lt; 3,000ms).This time without modifying LockType (still set to adLockOptimistic as you seems to need it).&nbsp;However, I still think the best option to fulfill your goal is to set that LockType to adLockBatchOptimistic : will be more scalable as you may increase drastically size of items to copy.&nbsp;Hope that helps !

philippeillinger · Answer

My problem is solved !!!It was a parameter problem...ALL the sample code (and API), use the adUseClient parameter, and that is the problem !!! Using adUseServer, drops to 2sec, the duration !!!In VB.net adUseServer is the default option !!! And that is the answer, that explain the difference!!!Regards...

Forum Discussion

ADODB performance problem

Share

Resources