Do you do any bulk writes from R to a db? Sqlsave is slow. I'm considering writing a file and picking it up with another tool.
I knew exactly what he was talking about. Sqlsave() is a function to write from R to SQL databases in the RODBC package. I also know that he was likely refactoring my code, or code that he had partially copied from me at one time.
Fortunately, a couple of years ago, I had migrated off of Sqlsave() to another solution. Here was my response to my colleague, in email:
Easier to type on here.
We don't have to do any bulk saves in production, everything there is a single transaction, so we're saving back 6-7 records at any time transactionally (times 100 tx's per minute, yes it's a lot at a time but not a lot at any one execution).As for SQLSAVE. I got fed up with the command and no longer use it. We have an insert stored procedure that we call from R. Much faster. Much more efficient and easier.We basically call an ODBC execute command wrapper, then just execute the proc with parameters to insert our data.I don't know how this would work form your point of view, but you could (sans proc) in theory, create a text string in R that is essentially the bulk insert you want to do, and execute it AS SQL. Which I think is the key... send the command, and let the insert be handled by the ODBC driver as a native ODBC SQL command, not as a hybrid weirdo SQLSAVE.
- Sqlsave runs slow in production jobs. Not sure why, but I would guess it is in a general class of problems I call "Meta-data shenanigans"
- Using SQL directly to write back to the database in some way is generally a faster solution.