Python 2.7 pyodbc или pymssql против R RODBC

Я пытаюсь перенести код R на Python 2.7, чтобы сравнить оба. Первая проблема, которую я получаю, это когда я пытаюсь установить соединение odbc. R намного быстрее, чем Python, но, поскольку я новичок в Python, я не уверен, что использую правильный пакет.

В Р я пишу:

ptm <- proc.time()
require(RODBC)
dbXX <- odbcDriverConnect('driver={SQL Server};
    server=s001111;database=XX;trusted_connection=true')
rech<-sqlQuery(dbXX, "select top 10000* from XX.dbo.table ", as.is=T)
proc.time() - ptm

и я получаю:

> proc.time() - ptm
   user  system elapsed 
   2.47    0.11    2.87

Я скачал Anaconda для python 2.7 windows 7 64. Итак, в Spyder я пишу:

import pyodbc
import pandas
from pandas.io.sql import read_frame
sql = 'select top 10000 * from XX.dbo.table'
cnn = pyodbc.connect('DRIVER={SQL Server};SERVER=s001111;DATABASE=XX;Trusted_Connection=yes')

start = time.time()
data=pd.read_sql(sql,cnn)
end = time.time()
print(end - start)

Это занимает 6,35 секунды

Я также пробовал с pymssql:

import pymssql
conn = pymssql.connect(server='s001111', database='XX')  
start = time.time()
data=pd.read_sql(sql,conn)
end = time.time()
print(end - start)

Это занимает 38,3 секунды!

Реальный запрос должен прочитать таблицу, размерность которой составляет 220 000 строк на 353 столбца, и применить фильтр (где).

Мне нужно только извлекать данные из БД.

Есть ли способ сделать это быстрее в Python 2.7?

Я обнаружил отношение использования pyodbc-memory, вызванное SQL Server, но я думаю, что если бы это было проблема SQL будет делать то же самое в R, не так ли?

Я также нашел это: IOPro но это не бесплатно!

В этот момент мне было интересно, была ли проблема в соединении ODBC или в самих пандах, поэтому я попробовал:

cur = conn.cursor();
start = time.time()
cur.execute(sql);
tabla=cur.fetchall() 
end = time.time()
print(end - start)

Но это заняло 29,29 секунды.

Итак, тем не менее: как возможно, что R намного быстрее, чем Python, извлекает данные из базы данных SQL Microsoft?


pandas python-2.7 pymssql performance pyodbc
person GabyLP    schedule 01.02.2016    source источник
comment
Производительность может сильно зависеть от используемого драйвера. Я бы посоветовал попробовать другой драйвер, чтобы увидеть, работает ли он лучше. Например, альтернативой является pymssql (pypi.python.org/pypi/pymssql/2.1.1). См. также список здесь: docs.sqlalchemy.org/en/relpymssql0/dialects/ mssql.html   -  person joris    schedule 02.02.2016
comment
Кроме того, небольшое примечание: read_frame устарело, вместо него следует использовать pd.read_sql. Я также рекомендую использовать SQLAlchemy для указания соединения (но это не зависит от используемого вами драйвера). См. pandas.pydata.org/pandas-docs/stable/io. .html#sql-запросы   -  person joris    schedule 02.02.2016
comment
@joris спасибо! Я скачал с помощью conda pymssql. Также написал код и запустил его. Как я могу измерить время, которое требует каждая библиотека? Я думаю, что все же это занимает гораздо больше времени, чем код R... возможно ли это?   -  person GabyLP    schedule 02.02.2016
comment
хорошо со временем импорта (я это сделал) занимает гораздо больше времени, чем R time.time()   -  person GabyLP    schedule 02.02.2016
comment
Это, безусловно, возможно. Если вы используете IPython, вы можете определить, сколько времени занимает команда, используя %time (например, %time pd.read_sql(...))   -  person joris    schedule 02.02.2016
comment
Если вы хотите проверить, является ли проблема самой pyodbc или pandas, вы можете определить, сколько времени занимает сам запрос, по времени cur = conn.cursor(); cur.execute(sql); cur.fetchall() и сравнить его со временем получения этого запроса с помощью pandas.   -  person joris    schedule 02.02.2016
comment
спасибо @joris, но все еще занимает много времени. Это возможно?   -  person GabyLP    schedule 03.02.2016
comment
Это, безусловно, возможно, это просто означает, что больше всего времени уходит на pyodbc, а не на pandas (чего я и ожидал, поскольку именно pyodbc выполняет фактическое чтение из базы данных в объекты python). Но вы показали, что чтение с помощью read_sql занимает 6,35 с с использованием pyodbc, поэтому чтение с необработанным выполнением не должно занимать больше времени. Кроме того, какую версию pymssql вы используете?   -  person joris    schedule 04.02.2016
comment
Ну, я сделал такой же тест. Я выбрал 10000 лучших строк из таблицы (из 3740224 записей), и время выполнения с pymssql составило 0,269999980927 секунд, что, я думаю, не так уж и плохо. Так что нет проблем ни с pymmsql, ни с python.   -  person Gabor    schedule 21.02.2017

Ответы (1)


arrow_upward
3
arrow_downward

И RODBC, и pyodbc, вероятно, будут тратить большую часть своего времени на передачу данных с сервера базы данных на локальный компьютер, который вы используете для выполнения запроса. Очень важным параметром здесь является количество строк, перемещаемых с сервера БД на локальный компьютер для каждого цикла выборки. И RODBC, и pyodbc позволяют настраивать этот параметр.

Теперь, если вы хотите сравнить яблоки с яблоками:

  1. использовать ту же инфраструктуру: тот же удаленный db, тот же локальный клиент, та же сеть между ними
  2. использовать тот же уровень ODBC: тот же диспетчер драйверов, тот же драйвер, тот же DSN
  3. получить ровно одинаковое количество строк для каждого цикла выборки как с R, так и с python. Внимание: если вы ничего не скажете, функция sqlQuery() RODBC будет получать по 100 строк за раз!

Я использовал этот подход для сравнения RODBC и pyodbc с тремя разными базами данных (не включая SQL Server), и я всегда нахожу производительность сопоставимой.

Вот код R:

library(RODBC)
ch <- odbcConnect("DWN",uid="xyz",pwd="xyz",rows_at_time=1024);
ds <- sqlQuery(ch,"select * from large_table limit 100000");

И вот у вас есть код Python:

>>> import pyodbc
>>> ch = pyodbc.connect('DSN=xyz;UID=xyz;PWD=xyz')
>>> curs = ch.cursor()
>>> curs.execute("select * from large_table limit 100000")
>>> while True:
...      rows = curs.fetchmany(1024)
...      if not rows:
...          break
... 
>>>
person mauro    schedule 04.02.2016