0

I can't explain how running the same query manually takes 54 seconds, while the same one from a sqlalchemy python program takes more than 10 minutes (RAW query)!

This is the raw query (54 seconds):

update company_holding c
set "issuerRankPortfolio" = sq."rankPortfolio"
from(
    SELECT quarter, "idCompany", "issuerCusip", "issuerPercPortfolio",
        row_number()
    over(
        partition by quarter, "idCompany"
        order by "issuerPercPortfolio" desc) as "rankPortfolio"
    FROM COMPANY_HOLDING where quarter = '2020Q1') as sq
where c."idCompany" = sq."idCompany"
and c."issuerCusip" = sq."issuerCusip"
and c.quarter = sq.quarter"""

This is the code (> 10 minutes):

query2 = f"""update company_holding c set "issuerRankPortfolio" =
    sq."rankPortfolio"
from(
    SELECT quarter, "idCompany", "issuerCusip", "issuerPercPortfolio", 
        row_number()
    over(partition by quarter, "idCompany" order by 
        "issuerPercPortfolio" desc) as "rankPortfolio"
    FROM COMPANY_HOLDING where quarter = '{propertiesQuarters.value[i]}') as sq
where c."idCompany" = sq."idCompany"
and c."issuerCusip" = sq."issuerCusip"
and c.quarter = sq.quarter"""
db.session.execute(query1)

I printed the value of {propertiesQuarters.value[i]} and it is '2020Q1' (double checked on sqlalchemy log).

piet.t
  • 11,718
  • 21
  • 43
  • 52
Damiano Dotto
  • 105
  • 1
  • 10
  • 1
    Aside - The calendar reads 2020 and people still do not parameterize SQL queries? Please use parameters as shown here for raw queries in sqlalchemy: [Sqlalchemy, raw query and parameters](https://stackoverflow.com/q/29208847/1422451). – Parfait Nov 24 '20 at 15:52
  • 1
    Are you sure that exact `execute` takes over 10 mins. or your entire SQLAlchemy codebase does? – Parfait Nov 24 '20 at 15:52
  • @Parfait yes I parameterize query. But for this test I prefer to use raw query. It's very strange because sometimes the sqlalchemy execution it's consistent with the manual execute. it may depend on db plan? – Damiano Dotto Nov 24 '20 at 16:03
  • @Parfait before this update I have a lot of insert... – Damiano Dotto Nov 24 '20 at 16:04
  • 1
    You execute `query1` but you give us `query2`... – Ruben Helsloot Nov 24 '20 at 17:01
  • it's a typo... Sorry I executed query1 – Damiano Dotto Nov 24 '20 at 17:37
  • Is it reproducible? I mean if you run JUST the query in python, then directly, then in python again and then directly again, what are the results? (you mentined something about doing a lot of insert...) – Petr Blahos Nov 25 '20 at 09:33
  • 1
    Are you sure there are no locks preventing the "slow" query from executing? – snakecharmerb Nov 25 '20 at 13:54

0 Answers0