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>Your code does follow proper form (aside from the few minor tweaks mentioned in the other answer), but be aware that when pyodbc performs an <code>.executemany</code> what it actually does is submit a separate <code>sp_prepexec</code> for each individual row. That is, for the code</p>

<pre class="lang-python prettyprint-override"><code>sql = "INSERT INTO #Temp (id, txtcol) VALUES (?, ?)"
params = [(1, 'foo'), (2, 'bar'), (3, 'baz')]
crsr.executemany(sql, params)
</code></pre>

<p>the SQL Server actually performs the following (as confirmed by SQL Profiler)</p>

<pre class="lang-sql prettyprint-override"><code>exec sp_prepexec @p1 output,N'@P1 bigint,@P2 nvarchar(3)',N'INSERT INTO #Temp (id, txtcol) VALUES (@P1, @P2)',1,N'foo'
exec sp_prepexec @p1 output,N'@P1 bigint,@P2 nvarchar(3)',N'INSERT INTO #Temp (id, txtcol) VALUES (@P1, @P2)',2,N'bar'
exec sp_prepexec @p1 output,N'@P1 bigint,@P2 nvarchar(3)',N'INSERT INTO #Temp (id, txtcol) VALUES (@P1, @P2)',3,N'baz'
</code></pre>

<p>So, for an <code>.executemany</code> "batch" of 10,000 rows you would be </p>

<ul>
<li>performing 10,000 individual inserts,</li>
<li>with 10,000 round-trips to the server, and</li>
<li>sending the identical SQL command text (<code>INSERT INTO ...</code>) 10,000 times.</li>
</ul>

<p>It is <em>possible</em> to have pyodbc send an initial <code>sp_prepare</code> and then do an <code>.executemany</code> calling <code>sp_execute</code>, but the nature of <code>.executemany</code> is that you still would do 10,000 <code>sp_prepexec</code> calls, just executing <code>sp_execute</code> instead of <code>INSERT INTO ...</code>. That could improve performance if the SQL statement was quite long and complex, but for a short one like the example in your question it probably wouldn't make all that much difference.</p>

<p>One could also get creative and build "table value constructors" as illustrated in <a href="http://stackoverflow.com/a/25879264/2144390">this answer</a>, but notice that it is only offered as a "Plan B" when native bulk insert mechanisms are not a feasible solution.</p>

python pyodbc