How can I speed up the code that contains the sql query?

user_11358642 0
Viewed 163

My dataframe contains about 650 thousand unique rows. For each row, I need to get a value from the database. I use a for loop, but the execution time is catastrophically long, about 25 hours. How can I speed up code execution? I assume that you need to parallelize the execution using joblib or numba. But the presence of an sql query inside the loop body is confusing.

for x in tqdm_notebook(range(len(table))):
    good = table.iloc[x, 0]
    store = table.iloc[x, 1]
    start = table.iloc[x, 6]

    query = f"""
    SELECT 
            good_id,
            store_id,
            AVG(sale) AS avg_sale,
            SUM(sale) AS sum_sale,
            MAX(sale) AS max_sale,
            MIN(sale) AS min_sale
    FROM my_table
    WHERE good_id = {good}
    AND store_id = {store}
    AND date_id BETWEEN DATEADD(MONTH, -2, '{start}') AND DATEADD(MONTH, -1, '{start}')
    GROUP BY good_id, store_id
    """
    temp = pd.read_sql(query, connection)
    if not temp.empty:
        table.iloc[x, 13] = temp['avg_sale'].values
        table.iloc[x, 14] = temp['sum_sale'].values
        table.iloc[x, 15] = temp['max_sale'].values
        table.iloc[x, 16] = temp['min_sale'].values
pandas sql-server python
1 Answers

For this you might just widen out your query and get all the (store,good,day) tuples, fetch partial aggregations for those, and and perform final filtering and aggregation in pandas. Note you change AVG to COUNT(*) and calculate AVG in the final aggregate.

And a handy way to pass a list of parameters to SQL Server is with OPENJSON. Just send a string parameter with a JSON array of scalars like

'[123,324,445,23,1322]'

or

'["abd","def","d"]'

So something like

SELECT 
        good_id,
        store_id,
        date_id,
        count(*)  AS count_sale.
        SUM(sale) AS sum_sale,
        MAX(sale) AS max_sale,
        MIN(sale) AS min_sale
FROM my_table
WHERE good_id in (select cast(value as int) from openjson(?))
AND store_id in (select cast(value as int) from openjson(?))
AND date_id BETWEEN ? and ?
GROUP BY good_id, store_id, date_id
ORDER BY good_id, store_id, date_id
user_72977000
Related