SQLalchemy - pymssql - pandas всегда медленный или я делаю это неправильно?

Question

вопрос

Я пытаюсь прочитать таблицу в MS SQL Server, используя python, в частности SQLalchemy, pymssql и pandas.read_sql. Я хочу выполнить запрос, поместить результаты в Dataframe pandas и продолжить мой день с разумной скоростью, но довольно простой запрос (вывод = 100 МБ) занимает почти 5-10 минут при подключении к локальной сети с использованием Ethernet Кабели - нет Wi-Fi в поле зрения.

Результирующий кадр данных с 3 столбцами и 3214086 записями занял колоссальные 423 секунды. Следующая информация ():

Int64Index: 3214086 entries, 0 to 3214085
Data columns (total 3 columns):
DateTime    datetime64[ns]
TagName     object
Value       float64
dtypes: datetime64[ns](1), float64(1), object(1)
memory usage: 98.1+ MB
None

Если моя математика верна, 100 МБ за 423 секунды - это около 230 КБ / с, что, по-моему, из-за Ethernet-соединения мучительно медленно. Узкое место должно быть в самом сервере sql

---

Информация о запросе

Сам сервер SQL представляет собой установку Wonderware, которая может иметь или не иметь какое-либо отношение к нему. В следующем запросе я запрашиваю значения 6 разных тегов за промежуток времени в 1 месяц, где разрешение составляет 5000 мс или 5 секунд.

Вот как выглядит сам запрос:

sql_query = '''
SELECT DateTime, TagName, Value

    FROM Runtime.dbo.AnalogHistory

    WHERE
        DateTime BETWEEN '2014-05-26 00:00' AND '2014-06-26 00:00'
    AND 
        TagName IN (
            'Tag1.ActualValue', 
            'Tag2.ActualValue', 
            'Tag3.ActualValue', 
            'Tag4.ActualValue', 
            'Tag5.ActualValue', 
            'Tag6.ActualValue')
    AND
        wwRetrievalMode = 'Cyclic'
    AND
        wwResolution = 5000
'''

---

И, наконец, функция executeQuery:

import pandas as pd
import pymssql
import sqlalchemy

def executeQuery(sql_query):
    connection_string = 'mssql+pymssql://user:password@server'
    engine = sqlalchemy.create_engine(connection_string)

    df = pd.read_sql(sql_query, engine)

    if 'DateTime' in df.columns:
        df.DateTime = pd.to_datetime(df.DateTime, infer_datetime_format=True)

    return df

---

Вопрос

Что тут происходит? Это ограничение в одном из пакетов, которые я использую, или я могу ускорить работу с тем, что у меня есть?

Этот вопрос, по- видимому, связан с тем, что ОП пытался записать на сервер SQL. Там говорится, что быстрее использовать "BULK INSERT" вместо панд, использующих файл.csv, но это нереальный вариант для чтения с сервера SQL. Должен ли я иметь каталог, содержащий файлы.csv со всеми датами / временем и всеми тегами!? Это отрицательно скажется на использовании сервера sql, не так ли?

Answer 1

Я полагаю, вы звоните executeQuery() Функция в цикле - довольно много раз, и каждый раз вы заново создаете движок SQLAlchemy, который замедляет работу.

Поэтому попробуйте создать соединение с БД один раз и использовать его несколько раз:

import pandas as pd
import pymssql
import sqlalchemy

connection_string = 'mssql+pymssql://user:password@server'
engine = sqlalchemy.create_engine(connection_string)

def executeQuery(sql_query, engine=engine):
    df = pd.read_sql(sql_query, engine)

    # i'm not sure that you really need it ...
    if 'DateTime' in df.columns:
        df.DateTime = pd.to_datetime(df.DateTime, infer_datetime_format=True)

    return df

PS если ваш DateTime столбец на стороне MS SQL имеет тип данных DateTime, SQLAlchemy должен сопоставить его с Python datetime тип. Так что в конце это уже должно быть np.datetime64...

Если вы звоните по телефону один или несколько раз, я бы сделал следующее:

• Проверьте план выполнения (используемые индексы, асимметрия данных и т. Д.)
• выполнить этот запрос на сервере MS SQL - и измерить время выполнения
• выполнить этот запрос на вашем клиентском компьютере по сети - и измерить время выполнения

После этого вы увидите, где вы больше всего теряете время...