Lesson Learned #200: Slow when fetching large volume of data compared with PYODBC.

[Jose_Manuel_Jurado]

A customer asked today, why using ODBC Driver 17 for SQL Server in Python with PYODBC we have a slightly difference in terms of time taken if we compare with C# System.Data.Odbc. Following, I would like to share my lesson learned about it.

Background

We have the following Python code and C# code. Both codes, are executing a query "SELECT * FROM Table1". This table has 300K rows and 55 columns. 

Python code

import os
#os.environ['TDSDUMP'] = 'stdout'
import pyodbc
import time

connReadonly = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=tcp:servername,1433;UID=username;PWD=password;database=dbname;Readonly=true;UseFMTOnly=yes");  

#ODBC Readonly fetchone
start_timeReadonly = time.time()
cursorReadonly = connReadonly.cursor()
cursorReadonly.execute('select * from table1')
rowReadonly = cursorReadonly.fetchone() 
totalODBCReadonly=0
while rowReadonly: 
    totalODBCReadonly=totalODBCReadonly+1
    rowReadonly = cursorReadonly.fetchone()
connReadonly.close()
print("-(ODBC-Readonly)-- %s seconds ---" % (time.time() - start_timeReadonly) )
print("-(ODBC-Readonly)-- %i rows ---" % totalODBCReadonly)

C# code

using O = System.Data.Odbc;
using H = System.Threading;
        public void IniciaODBC(String sMsg = "")
        {
            try
            {
                Stopwatch stopWatchConnection = new Stopwatch();
                Stopwatch stopWatch = new Stopwatch();
                int iNumberErrorRetriable = 0;
                int iNumberErrorFatal = 0;
                String sConnectionString = "";
                sConnectionString = sConnectionString + "Server=TCP:servername,1433";
                sConnectionString = sConnectionString + ";Database=dbname";
                sConnectionString = sConnectionString + ";UID=username";
                sConnectionString = sConnectionString + ";PWD=Password;Readonly=true;UseFmtOnly=yes";
                sConnectionString = sConnectionString + ";Driver={ODBC Driver 17 for SQL Server}";

                O.OdbcConnection oODBCConnection = new O.OdbcConnection();

                if (RetryPolicyODBC(sConnectionString, oODBCConnection, ref stopWatchConnection, ref iNumberErrorFatal, ref iNumberErrorRetriable))
                {
                    stopWatch.Start();
                    Console.WriteLine("DataReader");
                    Console.WriteLine("-----------------------------------");
                    int cnt = 0;
                    O.OdbcCommand Sqlcommand = new O.OdbcCommand("select * from table1", oODBCConnection);
                    Sqlcommand.CommandTimeout = 60;
                    Sqlcommand.CommandType = CommandType.Text;
                    O.OdbcDataReader SqlReaderC = Sqlcommand.ExecuteReader();
                    int iLen = SqlReaderC.FieldCount - 1;
                    while (SqlReaderC.Read())
                    {
                        cnt++;
                    }
                    stopWatch.Stop();
                    TimeSpan ts = stopWatch.Elapsed;
                    SqlReaderC.Close();
                    Console.WriteLine(sMsg + "----------------------------------- {0} -- ", cnt);
                    string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                        ts.Hours, ts.Minutes, ts.Seconds,
                        ts.Milliseconds / 10);
                    Console.WriteLine("Tiempo:{0}", elapsedTime);
                    Console.ReadLine();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Ups!!" + e.Message);
            }
        }
    }

Our customer mentioned that the execution time is higher in Python that C#, when we enabled the tracing options Setting Tracing Options - Open Database Connectivity (ODBC) | Microsoft Docs I saw the difference:

• In PyODBC, for every column and row, Python executes SQLGetData

ApplicationName.v 9e60-8108 ENTER SQLGetData
HSTMT 0x06D03538
UWORD 41
SWORD -8 <SQL_C_WCHAR>
PTR 0x05714868
SQLLEN 4094
SQLLEN * 0x06BBEAE8

• Meanwhile in C#, only will execute a ODBC request SQLGetData if this column is used in the code. 

This difference can be explained in terms of their API - the pyODBC fetchone() returns the row data directly, so it must necessarily fetch all the cells in the row and create Python objects for them every time it is called. However in C# execution execution returns an OdbcDataReader object which only fetches a cell when it is called to do so.

For example, changing the code in C# we could have the same time experience in PyOdbc.

using O = System.Data.Odbc;
using H = System.Threading;
        public void IniciaODBC(String sMsg = "")
        {
            try
            {
                Stopwatch stopWatchConnection = new Stopwatch();
                Stopwatch stopWatch = new Stopwatch();
                int iNumberErrorRetriable = 0;
                int iNumberErrorFatal = 0;
                String sConnectionString = "";
                sConnectionString = sConnectionString + "Server=TCP:servername,1433";
                sConnectionString = sConnectionString + ";Database=dbname";
                sConnectionString = sConnectionString + ";UID=username";
                sConnectionString = sConnectionString + ";PWD=Password;Readonly=true;UseFmtOnly=yes";
                sConnectionString = sConnectionString + ";Driver={ODBC Driver 17 for SQL Server}";

                O.OdbcConnection oODBCConnection = new O.OdbcConnection();

                if (RetryPolicyODBC(sConnectionString, oODBCConnection, ref stopWatchConnection, ref iNumberErrorFatal, ref iNumberErrorRetriable))
                {
                    stopWatch.Start();
                    Console.WriteLine("DataReader");
                    Console.WriteLine("-----------------------------------");
                    int cnt = 0;
                    O.OdbcCommand Sqlcommand = new O.OdbcCommand("select * from table1", oODBCConnection);
                    Sqlcommand.CommandTimeout = 60;
                    Sqlcommand.CommandType = CommandType.Text;
                    O.OdbcDataReader SqlReaderC = Sqlcommand.ExecuteReader();
                    int iLen = SqlReaderC.FieldCount - 1;
                    while (SqlReaderC.Read())
                    {
                        for (int col = 0; col <= oReader.FieldCount - 1; col++)
                        {
                            var sValue = oReader.GetValue(col);
                        }
                        cnt++;
                    }
                    stopWatch.Stop();
                    TimeSpan ts = stopWatch.Elapsed;
                    SqlReaderC.Close();
                    Console.WriteLine(sMsg + "----------------------------------- {0} -- ", cnt);
                    string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                        ts.Hours, ts.Minutes, ts.Seconds,
                        ts.Milliseconds / 10);
                    Console.WriteLine("Tiempo:{0}", elapsedTime);
                    Console.ReadLine();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Ups!!" + e.Message);
            }
        }
    }

Enjoy!