@Salami said:
Instead of this
<font size="+1">
If IfNull(result!name) Then name$ = "" Else name$ = result!name EndIf
</font>you can do this
<font size="+1">
name$ = result!name & ""
</font>
I would have thought the limiting factor for moving data between databases would be how fast the receiving database can insert the data. C should be 10 or 20% faster than VB6 in string manipulation.
That works fine for strings (which surprised me frankly) but just shifts the run-time errors to integers & floats.
C is probably twice as fast as VB when handling strings, I'd have to benchmark it to be certain. However, the big time sink is handing the information up and down the stack through ADO/ODBC. Think about all the detailed crap that "magically" happens when you're using an abstraction layer like ADO.
When I pull information out of MySQL, at some point the ODBC code is making the same C API call that my DLL is. From there, there's error checking, allocating memory for the BSTR, converting 8bit chars to 16bit wide chars, creating the collection, insert string into collection, index all the column names, etc. At this point the string is back in VB land, where run-time error checking kicks in, need to quote the string, allocating more BSTRs to build SQL statement (if you're not using prepared statements) and then make the ADO call to pass it back to SQLite. Going back down into ADO/DLL requires the BSTR to be converted back into 8bit chars, which requires at least one more memory allocation before ADO makes the same SQLite insert API call that my DLL does. Repeat the above for 20+ columns and several million rows and it certainly adds up.
By writing my own DLL, I can eliminate all of that pointless bit twiddling. MySQL API hands me a char pointer to string, I hand that pointer to SQLite's prepared statement insert API. Done.
Writing ADO code is certainly more convenient than all the hoops I'm jumping through with my own DLL interface code. That's why my first pass was written with ADO. However once I hit those performance problems (and a bug in ODBC as well) I needed to roll my own code to speed things up.