My general advice for refactoring code is to do it step-by-step. Don't try to fix everything at once, as it will likely break your code (as it did in your case).
I wasn't able to load your VI snippet. I'm not sure why though (either I'm to stupid to do it or Lava sanitizes images)
My suggestions below are based on your original VI
Step 1 - Improve readability
Arrays and for loops
The outer for loop iterates over multiple arrays at the same time. This means that the loop will end after processing each element of the smallest array. I suggest you use one array for iteration and use the Array Index function to access elements from the other arrays. Here is an example:
You should play around with it to see the different outcomes. Note that indexing an element from an array of smaller size returns a default value (i.e. zero for numbers, an empty string for Strings and false for Boolean).
The outer for loop uses a shift register for the SQLite instance as well as the error cluster. The inner for loop, however, doesn't. This can be problematic for two reasons:
1) The SQLite instance in the inner for loop gets reset after each iteration.
2) An error in the inner for loop is reset after each iteration and only the last error is returned.
The solution depends on what you want to do. If you want to abort as soon as possible on any error, I suggest not using a shift register but making the for loop conditional instead (exit on error). Otherwise use shift registers on both loops.
Step 2 - Optimize your code
Use transaction outside of for loops
Transactions are very useful for improving performance if used correctly. In your second example, you already added the BEGIN and COMMIT Transaction using "MySavepoint". This is generally a good idea.
Disable transactions on Execute SQL
The Execute SQL function has an optional input on the top which you can set to false in order to disable transactions. This will already improve performance considerably.
SQL Prepare and SQL Finalize should be placed outside the for loop and you need to inject values for your parameters at some point (unless your SQL Statement is static, which it isn't according to your info above).
The "INSERT many rows Template code.vi" provided by the library (see Code Templates palette) visualizes that very well:
I highly suggest not using "SELECT *" but explicitly selecting particular columns instead. That way your SQL Statement becomes predictable.
Here is an example that may or may not work (I can't test it for obvious reasons):
I suggest making a VI for each statement (without transactions of course). That way you can test things in isolation and be certain that it works.
I'm not sure what you mean by that. Can you explain what you mean in more detail?