Speeding up inserts that use a SQL Server stored procedure

Shubham Yadav 0
Viewed 48

I am using stored procedure to update the data in a table on azure database. the table name is "demo_output" Code for stored procedure -

CREATE PROCEDURE demo_Insertdatainto_table
@Model int, @output float , @batchid int
AS
BEGIN
    INSERT INTO [dbo].[demo_output](
        model, output, batchid)
        VALUES(@model, @output, @batchid)
END

Consider I have a dataframe as df. In order to insert values in the table I have to iterate through the data on stored procedure. But it take lot of time to insert the values. Please suggest a solution which can insert values in the table faster. Currrent code I am using to update the table -

for index, row in df.iterrows():
    cursor.execute('exec demo_Insertdatainto_table  ?,?,?', [row.Model, row.output, row.batchid])
    cursor.commit()
pyodbc sql-server pandas python
1 Answers

You don't need to loop through the rows of the DataFrame yourself. You can convert the DataFrame to a dict and use SQLAlchemy to execute the inserts in a single "batch". (SQLAlchemy will call .executemany() internally.)

import pandas as pd
import sqlalchemy as sa

connection_string = "DSN=mssql_199;UID=scott;PWD=tiger^5HHH;"
engine = sa.create_engine(
    sa.engine.URL.create(
        "mssql+pyodbc", query=dict(odbc_connect=connection_string)
    ),
    fast_executemany=True,
)
df = pd.DataFrame(
    [
        (1, 1.23, 1),
        (2, 2.34, 2),
    ],
    columns=["model", "output", "batchid"],
)
sql = sa.text("exec demo_Insertdatainto_table :model, :output, :batchid")
with engine.begin() as conn:
    conn.execute(sql, df.to_dict("records"))

# check results
with engine.begin() as conn:
    print(conn.exec_driver_sql("select * from demo_output").all())
    # [(1, 1.23, 1), (2, 2.34, 2)]
Gord Thompson0
Related