Forum Discussion
ADODB performance problem
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 (...
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...
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: using your "test cpp" sample with those two changes => process time <3,000ms.
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...
Regards
PS : have you seen the #import ".......msado15.dll" directive ?
Hi philippeillinger !
where have you find my test.cpp ?Answer provided in private message.
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 adLockBatchOptimistic and then use RS->UpdateBatch when appropriate.
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😞
- CursorLocation = 2 (-> adUseServer)
- CursorType = 1 (-> adOpenKeySet)
If you apply the same configuration in your C++ sample code RS parameters, then both (VB & C++ samples) RS settings will be aligned - more especially the DBPROP_MAXPENDINGROWS will be set to 1 in the C++ running sample. And performance also aligned (still < 3,000ms).
This time without modifying LockType (still set to adLockOptimistic as you seems to need it).
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.
Hope that helps !
- 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 )- 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...