Speed up inserts into SQL Server from pyodbc
<p>In <code>python</code>, I have a process to select data from one database (<code>Redshift</code> via <code>psycopg2</code>), then insert that data into <code>SQL Server</code> (via <code>pyodbc</code>).  I chose to do a read / write rather than a read / flat file / load  because the row count is around 100,000 per day.  Seemed easier to simply connect and insert.  However - the insert process is slow, taking several minutes.  </p>

<p>Is there a better way to insert data into SQL Server with Pyodbc?</p>

<pre><code>select_cursor.execute(output_query)

done = False
rowcount = 0

while not done:
    rows = select_cursor.fetchmany(10000)

    insert_list = []

    if rows == []:
        done = True
        break

    for row in rows:
        rowcount += 1

        insert_params = (
            row[0], 
            row[1], 
            row[2]
            )

        insert_list.append(insert_params)            

    insert_cnxn = pyodbc.connect('''Connection Information''')

    insert_cursor = insert_cnxn.cursor()

    insert_cursor.executemany("""
        INSERT INTO Destination (AccountNumber, OrderDate, Value)
        VALUES (?, ?, ?)
        """, insert_list)

    insert_cursor.commit()
    insert_cursor.close()
    insert_cnxn.close()

select_cursor.close()
select_cnxn.close()
</code></pre>

<p><s>It's good that you're already using <code>executemany()</code>.</s>  [<em>Struck out after reading other answer.</em>]  </p>

<p>It should speed up a (very little) bit if you move the <code>connect()</code> and <code>cursor()</code> calls for your <code>insert_cnxn</code> and <code>insert_cursor</code> outside of your while loop.  (Of course, if you do this, you should also move the 2 corresponding <code>close()</code> calls outside of the loop as well.)  In addition to not having to (re)establish the connection every time, re-using the cursor will prevent having to recompile the SQL each time.</p>

<p>However, you probably won't see a huge speed up from this just because you're probably only making ~10 passes through that loop anyway (given that you said ~100,000 a day and your loop groups together 10,000 at a time).</p>

<p>One other thing you might look into is whether there are any "behind-the-scenes" conversions being made on your <code>OrderDate</code> parameter.  You can go to SQL Server Management Studio and look at the execution plan of the query. (Look for your insert query in the "recent expensive queries" list by right-clicking on the server node and choosing "Activity Monitor"; right click on the insert query and look at its Execution Plan.)</p>

python pyodbc