On 11/04/2016 04:13 AM, James Burke wrote:
Hi All,
I've run into a odd problem, where calling the count function hangs my
code indefinitely. The odd thing is it was working until recently, so
I'm a little confused.
|
customer
=session.query(Customer).filter(Customer.phone_number.contains([message['metadata']['MIN']]))
logger.debug(len(customer.all()))
logger.debug('Works perfectly fine')
logger.debug(customer.count())
logger.debug('I will never see this')
|
Has anybody run into this problem before?
long running queries are a common problem when working with relational
databases. The "working until recently" part is also very common
because a slow query will usually only become slow once the data size
grows too large.
You need to first identify the queries running when this hang occurs,
e.g. that there's no lock contention, then you need to analyze it.
1. run the program so it hangs.
2. go to the psql monitor elsewhere and run "SELECT * FROM
pg_stat_activity". If your query is the only one there, then it's just
a slow query. If there's lock contention, you'd see that it's
"waiting" and you'd also see the other process that's doing something.
3. figure out why the query is slow using EXPLAIN ANALYZE <the query>.
Make sure you use the identical SQL that the database is actually
running, which you can see either in the pg_stat_activity or in the
echo=True output of SQLAlchemy. You will need to manually substitute
literal values where bound parameters are.
From EXPLAIN, you'd see what's either structurally wrong with the
query, such as if you're doing a cartesian product (e.g. selecting from
multiple tables without correlating rows to each other) or if something
is wrong with indexes, which you'd see in the form of table scan activity.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
