pyodbc slowness

[Larry Martell]

On Wed, Mar 4, 2020 at 3:20 PM Larry Martell <larry.martell at gmail.com> wrote:
>
> I have some Python code that uses pyodbc to talk to a SQL Server database. In that code I do an  INSERT INTO xxxx SELECT * FROM yyyy
>
> That query takes around 3 times longer to run when invoked from Python with pyodbc than when run with direct SQL.
>
> On one system we have 1,667 rows and the timings are direct sql 1.7s pyodbc 4.6s. On another system we have 15,000 rows and it's 15s vs 48s
>
> In both cases, I am running from the same machine over the same network talking to the same SQL Server.
>
> The only difference I can find is the driver. When I run the direct SQL the client I have uses the driver net.sourceforge.jtds.jdbc.Driver jtds12.jar where as pyodbc uses {ODBC Driver 17 for SQL Server}.
>
> Could this be the cause of the difference? Is there a different driver I can use with pyodbc that would be faster?

I discovered the issue is that I am selecting from a view. When
selecting from a table the time is the same. So what about a view
would make it take longer when accessed from pyodbc?