ODBC prepared statement memory explosion

Databases
1

Hi all,

I’m using ODBC on a Mac (with the Actual Technologies ODBC driver) to SQL Server 2014. I’m running a moderately long loop (32,000 records) with INSERTs.

For each record, I’m doing essentially this:

ps = ODBCPreparedStatement(msdb.Prepare(sqlPrototype)) for i as integer = 0 to fieldVals.UBound ps.BindType(i, ODBCPreparedStatement.ODBC_TYPE_STRING) ps.Bind(i, fieldVals(i)) next ps.SQLExecute

At the end of this loop, my memory usage is up to 560 MB (from an initial start of 7 MB).

If I comment out the ps.Bind() call, memory usage at the end of the loop is only 28 MB.

Am I doing something wrong, or does Bind have a big memory leak?

I’d like to run it with 100,000 records, but it runs out of memory.

Any suggestions welcomed!

2

Dim ps As ODBCPreparedStatement = ODBCPreparedStatement(msdb.Prepare(sqlPrototype)) // Bind the filed types only one time For j As Integer = 0 to fieldVals.UBound ps.BindType(i, ODBCPreparedStatement.ODBC_TYPE_STRING) Next For i As Integer = 0 To 32000 For j As Integer = 0 to fieldVals.UBound ps.Bind(i, fieldVals(i)) Next ps.SQLExecute() If i Mod 100 = 0 Then msdb.Commit() // Commit after hundred INSERT statements End Next

3

Eli,

THANK YOU!

That works like a charm.