How can I improve PyODBC performance of a single row select (i.e. a lookup)?

user_14106528 0
Viewed 564

I want to improve the performance of an SQL Select call via ODBC/pyODBC.

This is not against a large database (maybe 10K rows), pulling a unique record (15 columns) from the table. The combined size of the 15 columns is about 500 bytes). I'm using pyODBC, and using fetchone, the fastest I have been able to get it down to is about 2 seconds. It used to be roughly 3.5 seconds. I've set the encoding and decoding to UTF-8 to match the database. I have confirmed that the transaction level is read_uncommitted.

I am using DataDirect ODBC driver on Unbuntu Linux.

I can't seem to get it below 2 seconds, but if I run it from a SQL processor (like db visualizer, or dbeaver) the row returns in 0.3 to 0.4 seconds. It's a very simple query with one where clause that uniquely indexed. No wild cards, no exists, etc.

Is this just a minimum amount of time that pyodbc takes to process a query?

query = 'select order_num, pick_ticket_num, package_id, ship_via, name, contact, address1, address2, address3, city, state, postal_code, country, phone from dbc.v_dmv5 where package_id = ?'  

cursor.execute(query, sqlArgs)
row = cursor.fetchone()

I also tried using turbodbc which results in the same performance level of about 2s for the query. But running this exact query in any sql processor is essentially immediate.

It's definitely not the parameterization field either since I've actually hard coded a value in to the where clause, and it still takes 2+ seconds to execute.

pyodbc python
1 Answers

Based on the discussion in the comments to the question, especially

If you run it in iSQL - the result for the data comes back instantaneously - like immediately shows up. But the cursor doesnt come back for another 2 seconds.

and

But if I look fetchone 3x from execute to fetch, it literally is 2s, 2s, 2s

it appears that the driver retrieves the last value in the last row, for example ...

so63171038      27b8-1b2c   EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000000997E788440
        UWORD                        2 
        SWORD                       -8 <SQL_C_WCHAR>
        PTR                 0x00000099746BD0A0 [       6] "bar"
        SQLLEN                  4096
        SQLLEN *            0x00000099722EE380 (6)

... and then when it calls SQLFetch again to see if there is more information to retrieve (and there isn't) ...

so63171038      27b8-1b2c   ENTER SQLFetch 
        HSTMT               0x000000997E788440

so63171038      27b8-1b2c   EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND)
        HSTMT               0x000000997E788440

... that's what is introducing the ~2 second delay.

It definitely looks like a driver (or perhaps database) issue to me.

user_21443900
Related