Slow Handling of executemany()

[dagostinelli]

There was a long standing, somewhat significant issue open at Google Code having to do with executemany() and MS SQL Server. It does not yet appear to be fixed. I'd like to call attention to it again. (insert unhelpful chide for leaving open issues at Google Code and not bringing them over)

Issue #250

Basically -- executemany() is taking forever. The problem manifests itself when there are a lot of records (10k's or 100k's) and using MS-SQL Server.

When one uses the SQL Profiler, you can see, for every statement, an exec sp_prepexec, followed by an exec sp_unprepare.

Here's a bit of sample code that makes it happen.

import pyodbc
dest_conn = pyodbc.connect('DSN=MyDSNToASQLServer;UID=someuser;PWD=password;')
dest_cursor = dest_conn.cursor()

sql = 'INSERT INTO MyTable (Name, Address, Phone) VALUES (?, ?, ?)'

data = []

## OMITTED
# Fill data[] with a lot of data 10k's of rows or more
##

# this takes forever when there are a lot of records
dest_cursor.executemany(sql, data)
dest_conn.commit()

[keitherskine]

See issue #62 . I believe one of the problems is that "parameter arrays" require the input data to be converted to a fixed-width encoding, i.e. NOT utf-8 or utf-16, because the data has to be temporarily stored in a block of memory, in grid-like fashion. This means converting the input data to something like UCS-16 (with a small but significant possibility of data loss/corruption), and having to figure out how big the grid should be. Not an easy problem to solve, but it would certainly help me in the work I do.

[dagostinelli]

Is there a workaround that we can use for now?

Someone mentioned using a transaction in the Google Code link -- I'm using that and it doesn't help. In Issue #62, it was mentioned to load 250 records at a time. I can try that.

Did anything work for you?

[keitherskine]

I use the bcp bulk load utility - a lot. Even for routine updates and inserts, I write the updates to a file, bcp them to a temporary table, then update the target table in one transaction. It's a very roundabout way of doing updates, but if you're doing millions of updates to tables containing hundreds of millions of rows, it's the fastest way. By the way, I wrote python modules to do all the heavy lifting (which are company-proprietary and I can't publish - sorry).

Using a separate transaction won't make a blind bit of difference. It's all about the number of round-trips to the database server. With a parameter array, there's one round-trip for all the updates. Without a parameter array, there's one round-trip per update row - very, very slow.

[dagostinelli]

Kicking this issue to keep it alive.

[woodlandhunter]

@dagostinelli The link you provided did not suggest using a transaction, it was suggesting an explicit batch. You are limited to a certain number of parameters (2100 here when I looked) per query so how many statements you can combine will be limited by the specific insert you are doing.

It's a workaround for sure, but it was much faster than executemany when i tested it here (I only tested with a single batch of 699 insert statements with 3 parameters per insert):

699 rows done via executemany in 4.85 secs
699 rows done via batch in 0.36 secs

It would not be difficult to build up an insert as you counted parameters and send each batch as you go.
For anyone thinking of it, I would not recommend trying to work around parameter limits by inserting values directly into the query.

@keitherskine's method is probably still faster but this is simpler IMO.

[BMagill]

Hello
I am a "copy/paste" type of programmer so please excuse me if my ignorance shows through in this post:

I have had a similar experience with executemany writing to an Azure SQL database - excruciatingly slow.

Scouting around, I found this link:
https://gadymargalit.wordpress.com/2014/07/19/python-and-mssql-performance/
which, when implemented has improved performance 100-fold. Literally.

Perhaps someone more knowledgeable than me could explain why the "encapsulate" method is improving performance so radically.

[woodlandhunter]

@BMagill It's batching the inserts so one round trip inserts multiple rows.

This is a similar method to what I mention above, but he's doing it insecurely without using parameters. He's using string formatting everywhere/manually escaping values which is going to be vulnerable to sql injection. This isn't code I would use at all.

What is your use case?

[dirkjonker]

I would suggest people that have issues with pyodbc's executemany speed to try Turbodbc which almost seems to be created for this specific issue.

[BMagill]

@woodlandhunter
Hello. Thank you for your comment.
Sorry, I took so long to get back to you.
My use case is processing water meter readings from connected devices so I could say that I don't need to worry about security too much. But I know that this is a poor response as soon I may well be doing something where security is paramount.
I will have a look at using parameters and also at the Turbodbc mentioned.

[v-chojas]

I am currently working on an implementation using the parameter array feature of ODBC, which is showing some promising results in preliminary benchmarks:

Time in seconds taken to insert 20K rows into a table with single int column, SQL Server 2016 using Microsoft ODBC Driver 13:
original executemany() : 10.8121
parameter array executemany() : 0.3118
execute() in a Python loop : 12.7683

I still need to do some more testing with the other datatypes; this is just a notification that I am looking into it.

[dirkjonker]

That's great news @v-chojas ! Preliminary results look promising, that would make pyodbc usable for our project again (currently we write to CSV and use bulk load).

[mkleehammer]

That sounds awesome!. I've been meaning to look into that for a couple of years.

[v-chojas]

I have some preliminary code at https://github.com/v-chojas/pyodbc/tree/paramarray if you'd like to give it a try. @mkleehammer let me know if you'd like a PR. It has only been tested with SQL Server 2016 and the msodbcsql driver on Linux. To implement the data insertion efficiently, the changes are quite significant so I opted to leave most of the existing code alone and have the new implementation alongside it. Notable differences/improvements are---

1. SQLDescribeParam is used to obtain the correct SQL type and characteristics (e.g. size) of the column, which is then supplied to SQLBindParameter so that the driver sends the correct datatype to the server. This possibly eliminates an additional server-side conversion. In other words, instead of setting both SQL type and C type from Python type, I set the SQL type according to the server's recommendation, the C type from the Python type (using the SQL type as a hint, in some cases), and let ODBC driver do conversion.

2. The entire parameter array is allocated at once and then each row is filled in, converted from Python object to the ODBC C representation. If the "schema" changes (e.g. the cell in a previous row was a PyInt and the current one a PyBytes), then I execute with the rows accumulated thus far, and continue with the current row by detecting and assuming the new schema.

3. As a side-effect, a few bugs I discovered in the existing implementation (execute() in a loop) while testing and comparing were coincidentally fixed:

• Conversion from long integers would fail even if the value was representable in the destination column: e.g. decimal(38,0) can hold values near 2**127 but they failed to convert in the existing implementation. With the optimised implementation, the full precision is usable.

• Used SQL_NUMERIC_STRUCT correctly to avoid a trip through (slow) string conversion. Decimal types now insert with full precision.

• Inserted date/time objects into time(n) columns were missing fractional seconds.

Performance test results:
SQL Server 2016
Microsoft ODBC Driver 13.1 for SQL Server (Linux - CentOS 6.8)
Pythons 2.6.6 and 3.6.1

Times are for inserting 20K rows into a single-column table of the specified type, in seconds. There is at least an order of magnitude difference between the old and new executemany().

Type new executemany() old executemany() Python execute() loop
bigint 0.4858 14.5929 13.4796
bit 0.4195 17.5971 13.3187
int 0.4584 14.3637 13.1119
money 0.4811 15.3528 14.9873
smallmoney 0.4576 14.1919 13.4547
smallint 0.395 14.4953 13.3151
tinyint 0.5302 15.5995 14.185
float 0.503 13.8713 13.8131
binary(27) 0.4403 15.0619 17.13
varbinary(20) 0.4516 14.0041 13.5609
date 0.4554 16.3541 13.4959
time(7) 0.3818 13.9678 14.1381
time(5) 0.4966 14.5185 13.2597
time(3) 0.4969 18.4535 15.0969
time(0) 0.4757 14.1939 14.8959
datetime 0.6387 15.4826 14.0731
datetime2(7) 0.3721 13.4521 14.8893
datetime2(5) 0.452 15.3164 15.4111
datetime2(3) 0.4881 14.23 14.3967
datetime2(0) 0.454 15.7699 15.502
uniqueidentifier 0.5077 13.9185 13.6002
decimal(38,0) 0.5451 error error
decimal(13,5) 0.6565 14.1069 20.5524
decimal(19,8) 0.9849 15.2114 13.081
decimal(38,22) 1.0341 13.4947 16.9196
char(15) 0.5015 14.4498 12.8164
char(75) 0.5132 13.9494 13.3094
char(221) 1.1595 16.4242 14.9281
char(750) 1.0887 25.9653 15.1296
varchar(15) 0.4943 16.8314 13.322
varchar(75) 0.5822 18.0023 13.3582
varchar(221) 0.6063 16.1918 13.029
varchar(750) 0.8678 17.2184 13.947
nchar(15) 0.5257 13.7747 16.8729
nchar(75) 0.5039 13.975 13.5015
nchar(221) 0.7486 14.7194 15.1842
nchar(750) 2.7277 (timed out) (timed out)

[mkleehammer]

Wow. That looks really, really good.

We do need to make sure it works with all of the not-so-great drivers out there, so a generic fallback will be needed. Some examples:

Some drivers don't provide SQLDescribeParam so lengths, etc., are not available. (Also, I tried calling it for all parameters, but it slowed it down quite a bit. We might need to watch this - perhaps separate the executemany implementation which would clearly benefit?)

Many drivers didn't get the binary numeric struct right. I think Oracle was one, but I don't have an Oracle test install right now. I spent a few hours setting up a new VM and downloading, but then didn't get it configured. PITA.

[v-chojas]

Yes, I'm aware there are drivers which don't fully implement ODBC correctly. However, it is necessary to determine the lengths and types in order to layout the parameter array... I suppose it would be possible to let the application supply this information when the driver can't (similar to what #213 is proposing), but I'm not sure of the value of that since such a driver might not support parameter arrays either.

Calling SQLDescribeParam certainly adds some overhead, and it will vary across drivers and network environments, but with my tests on SQL Server in a LAN environment, param arrays are already faster with as little as 10 rows. I tried it with many more parameters (128 to be exact) and the performance did not decrease --- in fact it was even better, with >100x speedup observed. But based on your experience, perhaps it would be better to default to the old execute()-in-a-loop path for executemany(), and provide an option to enable all the improvements (e.g. cursor.fast_executemany = True). What do you think?

[lnxsrt]

I've just tried this fast_executemany code that was merged into master. I am getting the following error:

pyodbc.ProgrammingError: ('24000', '[24000] [Microsoft][ODBC Driver 13 for SQL Server]Invalid cursor state (0) (SQLPrepare)')

Here's the code which works fine with cursor.fast_executemany = False

cursor.executemany("""UPDATE AC_PROPERTY SET AC_PROPERTY.""" + field + """=? WHERE AC_PROPERTY.PROPNUM=?""", grid)

[v-chojas]

Can you give more information on your environment? OS, Python version, etc.

[lnxsrt]

Windows 7 64bit
Python 3.6.2 64bit
MS SQL Server 2008 R2 (10.50.4042)
ODBC Driver 13 for SQL Server (2015.131.4413.46)

[v-chojas]

I am unable to reproduce the error, although I am using Win8.1/64 and SQL 2016 the Python is also 3.6.2.

--- SQL
create table Test120(c1 int, c2 int)
insert into Test120(c1, c2) values (1, 10), (2, 20), (3, 30), (4, 40), (5, 50)

# Python
conn = pyodbc.connect("<my connection string here>")
cursor = conn.cursor()
cursor.fast_executemany=True
cursor.executemany("""UPDATE Test120 SET Test120.c2 = ? WHERE Test120.c1 = ?""", [ [ 10, 1 ], [ 20, 2 ], [ 30, 3 ] ])
cursor.commit()

You can provide an ODBC trace (with any sensitive information removed) to assist in debugging this issue further.

[lnxsrt]

Yes, see attached ODBC trace.

SQL.LOG.txt

[v-chojas]

Thank you. I see that your code is attempting a fast executemany() after it has already used the cursor for some other execution, and the (ODBC, not pyODBC) cursor has not been closed. execute() and regular executemany() closes the cursor first, but the fast executemany implementation does not.

As a workaround, you can consume all the resultsets first, which will automatically close the cursor:

while cursor.nextset():
    pass

(This is recommended in general, especially in the case of statements which return multiple resultsets/rowcounts, because failure to do so causes other problems.)

NOTE: I am referring to the ODBC cursor throughout; this is unrelated to and not solved by using the cursor.close() in Python, since that not only closes but frees the underlying ODBC statement handle.

[lnxsrt]

Adding your workaround code fixed the error. However, the speedup I am getting seems small.

fast_executemany = True --- 630.5218128256312 seconds ---

fast_executemany = False --- 737.002021040133 seconds ---

The data is a python list of 21,876 [float(value), str(key)] pairs.

See attached tracing. (Tracing was off for the above time trials)

SQL.LOG.txt

[v-chojas]

That's under 30 rows/s in the non-fastexecutemany case, and ~35 rows/s with FEM. I don't know what hardware you're using but that is more than an order of magnitude slower than what I'd expect even without FEM, so I suspect the bottleneck for you is somewhere else. Observing network and CPU usage on the client/server and disk use on the server may yield more clues.

From the trace you provided, I can see that it is working as expected - all rows are sent by the driver at once:

pythonw.exe -c  1d68-3ebc	ENTER SQLSetStmtAttr 
		SQLHSTMT            0x0000000000300CC0
		SQLINTEGER                  22 <SQL_ATTR_PARAMSET_SIZE>
		SQLPOINTER             21876
		SQLINTEGER                  -5 

pythonw.exe -c  1d68-3ebc	EXIT  SQLSetStmtAttr  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x0000000000300CC0
		SQLINTEGER                  22 <SQL_ATTR_PARAMSET_SIZE>
		SQLPOINTER             21876
		SQLINTEGER                  -5 

[lnxsrt]

Seems to be a CPU usage issue. Both disk I/O, and network I/O are very low during execution of the query, but CPU usage is pegged on the SQL server. Could the "WHERE" be causing this slowdown? The WHERE column is the primary key so it should be indexed.

[lnxsrt]

I rewrote my query to INSERT INTO a temporary table then UPDATE from the temporary table and it is much, much faster. And your parameterized code is over an order of magnitude quicker. Thanks for the help.

fast_executemany = True --- 0.7819672694129391 seconds ---
fast_executemany = False --- 10.766220919423262 seconds ---

[gordthompson]

Feature included in the 4.0.19 release. See fast_executemany for details.

[64mao]

@keitherskine have you ever use .fmt file to insert different schema tables? I was having an issue as following, even I removed the spaces in between column names. Any ideas or suggestions on that?

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Incorrect host-column number found in BCP format-file

[gordthompson]

Closed due to inactivity. Feel free to re-open with current information if necessary.