[RESOLVED] ADO / Cpp msado15.dll performance problem sous Visual Studio 2019 (C++ ET VB)

[Philou06450]

Bonjour a tous (je le fais en Fran?ais, mon anglais ?tant trop limit?)

J'ai deux applications hyper simple de test (pour mettre en ?vidence un gros probl?me de performance). => c'est une extraction (donc tout est dans le main) d'une grosse application )
L'une en C++/MFC/ADO (#import msAdo15.dll), l'autre en VB.NET/ADO (ADOX...msAdo15.dll).
Les param?trages des connexions/recordsets sont a priori identiques des deux cot?s.

Les propri?t?s quand on les r?cup?re, ne sont pas identiques => mon instinct me dit, pb de param?trage par d?faut, des plateformes (Cpp/VB), un peu comme avec XP, 7, vista quand on joue avec le OnPaint(), et que l'on n'initialise pas l'ensemble des ?l?ments facultatifs...

Algorithmiquement parlant, on lit une table d'une base Access, que l'on va ?crire dans une autre base Access, une b?te copie de base en somme.

Le code VB s'ex?cute en 5secondes pour 15000 enregistrements, le code Cpp lui met 30minutes...et l'?cart se creuse exponentiellement...(pour 1000 enregistrements VB : 1s CPP : 5s ? la louche )...
=>probl?me probable dans le param?trage du recordset qui doit se rafraichir alors que ce n'est pas n?cessaire...

J'ai vu plein de post sur ce probl?me sur la toile, mais aucune solution pertinente, qui colle ? mon contexte. J'ai beau modifier le param?trage, cela n'impacte par les non perf.
Quand j'utilise le profileur (CPU), je passe 99% de mon temps dans le AddNew() d'ADO (si j'utilise l'update 'implicite') et 99% de mon temps dans l'Update(), si je met celui-ci explicitement.

Y aurait-il donc un 'sp?cialiste' du param?trage ADO dans la salle ?

Mes deux solutions (.sln) sont ? disposition pour qui aurait une piste...ou une id?e...ou voudrait creuser la probl?matique (mais il est imp?ratif de maitriser ADO, donc d?butant s'abstenir)...

Merci...

[2kaud]

Hello everyone (I do this in French, my English is too limited)

I have two super simple test applications (to highlight a big performance problem). => it's an extraction (so everything is in hand) of a large application)
One in C++/MFC/ADO (#import msAdo15.dll), the other in VB.NET/ADO (ADOX...msAdo15.dll).
The connection/recordset settings are a priori identical on both sides.

The properties when we retrieve them, are not identical => my instinct tells me, no default settings, platforms (Cpp/VB), a bit like with XP, 7, vista when we play with OnPaint(), and we do not initialize all the optional elements...

Algorithmically speaking, we read a table from an Access database, which we will write in another Access database, a basic copy beast in short.

The VB code executes in 5 seconds for 15,000 records, the Cpp code takes 30 minutes...and the gap widens exponentially...(for 1000 VB records: 1s CPP: 5s a ladle).. .
=>probable problem in the settings of the recordset which must be refreshed even though it is not necessary...

I've seen lots of posts on this problem on the web, but no relevant solution, which sticks? my context. Even though I modify the settings, it does not impact the non-perf.
When I use the profiler (CPU), I spend 99% of my time in ADO AddNew() (if I use the 'implicit' update) and 99% of my time in Update(), if I put this one explicitly.

So would there be an ADO configuration 'specialist' in the room?

My two solutions (.sln) are ? available for anyone who has a lead...or an idea...or wants to explore the problem (but it is imperative to master ADO, so beginners refrain)...

THANKS...

[Philou06450]

Hello,

After many test, i see, that the performance are dependant of ever the runtime2013 or Access365...
I explain :
With the runtime 2013 only, the cpp code needs 9 seconds...
With Access365 (with or without runtime2013), the cpp code needs 30 minutes...
And that for the 15000 records...

Any suggestions ?

Regards...

[VictorN]

Hello,

After many test, i see, that the performance are dependant of ever the runtime2013 or Access365...
I explain :
With the runtime 2013 only, the cpp code needs 9 seconds...
With Access365 (with or without runtime2013), the cpp code needs 30 minutes...
And that for the 15000 records...

Any suggestions ?

Regards...

Just don't use Access365

[Philou06450]

Just don't use Access365

Access365 comes with Office365, and this product is installed on the user computer...
Before using ADO, i used DAO with the runtime 2013 x86 (and the performance where better)...but on every software update, it was 'replaced' with the x64 one... And DAO will never be x64 !!!
I don't have many choices...use ADO, or use and other provider as ACCESS...

Regards...

[VictorN]

What connection string, what provider do you use for the ADO connection to the Access365?
Did you try to change them?

[Philou06450]

I have try many settings...
//m_strConnection2.Format( "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='%s'; Persist Security Info=False;", strFile2.GetString());
m_strConnection1.Format( "Provider=Microsoft.ACE.OLEDB.16.0;Data Source='%s';Persist Security Info=False;", strFile1.GetString());
and if the string is not ok, i got an exception

The connexion is OK (less than 0.1 ms )

[Philou06450]

Je poste ci-dessous, le r?sultat de l'instrumentalisation...=> 90% dans le AddNew()

|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|
|\|\|\|\|*-*\[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

[VictorN]

Well, if AddNew is too slow then try to use Execute method (Connection or Command) with SQL query "Insert Into ..."

[Philou06450]

It's a good idea, but...the reel software work with recordset . fields. get/put value.
Modifiing this, is not an option, and if there are no other solution, i make what Microsoft want...i give up Access !!!

BUT VBA or VB.NET make the job, in less than 1s !!!
And with DAO (x86) it takes also LESS than 1s !!! ( but DAO is not x64 !!! )
That means to me, there is a mega huge problem somewhere...

Regards

[Philou06450]

I have tried to make an INSERT INTO...

The 'good' thing is that the result don't depends on the installed RunTime ( 2013 or 2016 )
BUT it takes 23 sec !!!

Best Regards

[Philou06450]

My problem locks be solved...

Under VB.NET i have NOT set adUse(Client/Server) , and apparently the default value is optimum
Under CPP i have set to adUseClient my two recordsets/connexions, and set the writing one to adUseServer, make the performance to becommes acceptable (2s)...

The great problem, is that ALL the code sample (and API) i have found, use the adUseClient parameter !!!

Best Regards...