Styjun

What are good ways to improve as a writer other than writing courses?

Why do I need to insert 12 characters to clear this bash command-line?

Looking for a new job because of relocation - is it okay to tell the real reason?

Double blind peer review when paper cites author's GitHub repo for code

Where to pee in London?

How do I change the output voltage of the LM7805?

Could one become a successful researcher by writing some really good papers while being outside academia?

How can I tell if a flight itinerary is fake

What are the examples (applications) of the MIPs in which the objective function has nonzero coefficients for only continuous variables?

Word or idiom defining something barely functional

Traveling from Germany to other countries by train?

Our group keeps dying during the Lost Mine of Phandelver campaign. What are we doing wrong?

What is the resistivity of copper at 3 kelvin?

Can we use other things than single-word verbs in our dialog tags?

How is the return type of a ternary operator determined?

In the movie Harry Potter and the Order or the Phoenix, why didn't Mr. Filch succeed to open the Room of Requirement if it's what he needed?

Sets A such that A+A contains the largest set [0,1,..,t]

Scripting a Maintenance Plan in SQL Server Express

Is it double speak?

How to realistically deal with a shield user?

Premier League simulation

How can glass marbles naturally occur in a desert?

Independent table row spacing

How to explain to a team that the project they will work for 6 months will 100% fail?

Sqlalchemy query is very slow after using the in_() method

What is the difference between Python's list methods append and extend?Static methods in Python?Does Python have a string 'contains' substring method?How do I expand the output display to see more columns?“Large data” work flows using pandasSqlalchemy order by calculated columnSQLAlchemy, subqueries and entitiesUsing groupby to do in-place modification on the dataframeDjango: Queryset object filter, against another object's time rangeFlask and SQLAlchemy sort in display without new query?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

filters.append(Flow.time_point >= datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
filters.append(Flow.time_point <= datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S'))
if domain_name != 'all':
 filters.append(Bandwidth.domain_name.in_(domain_name.split('|')))

flow_list = db.session.query(Flow.time_point, db.func.sum(Flow.value).label('value')).filter(*filters).group_by(Flow.time_point).order_by(Flow.time_point.asc()).all()

The query time is 3 to 4 seconds when domain_name is 'all', otherwise the query time is 5 minutes. I have tried to add an index to a column but to no avail. What could be the reason for this?

python sqlalchemy

share|improve this question

edited Mar 27 at 6:22

Ilja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

asked Mar 27 at 6:17

Skyrim SnowSkyrim Snow

333 bronze badges

add a comment | 

0

filters.append(Flow.time_point >= datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
filters.append(Flow.time_point <= datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S'))
if domain_name != 'all':
 filters.append(Bandwidth.domain_name.in_(domain_name.split('|')))

flow_list = db.session.query(Flow.time_point, db.func.sum(Flow.value).label('value')).filter(*filters).group_by(Flow.time_point).order_by(Flow.time_point.asc()).all()

The query time is 3 to 4 seconds when domain_name is 'all', otherwise the query time is 5 minutes. I have tried to add an index to a column but to no avail. What could be the reason for this?

python sqlalchemy

share|improve this question

edited Mar 27 at 6:22

Ilja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

asked Mar 27 at 6:17

Skyrim SnowSkyrim Snow

333 bronze badges

add a comment | 

filters.append(Flow.time_point >= datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
filters.append(Flow.time_point <= datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S'))
if domain_name != 'all':
 filters.append(Bandwidth.domain_name.in_(domain_name.split('|')))

flow_list = db.session.query(Flow.time_point, db.func.sum(Flow.value).label('value')).filter(*filters).group_by(Flow.time_point).order_by(Flow.time_point.asc()).all()

The query time is 3 to 4 seconds when domain_name is 'all', otherwise the query time is 5 minutes. I have tried to add an index to a column but to no avail. What could be the reason for this?

python sqlalchemy

share|improve this question

edited Mar 27 at 6:22

Ilja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

asked Mar 27 at 6:17

Skyrim SnowSkyrim Snow

333 bronze badges

filters.append(Flow.time_point >= datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
filters.append(Flow.time_point <= datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S'))
if domain_name != 'all':
 filters.append(Bandwidth.domain_name.in_(domain_name.split('|')))

flow_list = db.session.query(Flow.time_point, db.func.sum(Flow.value).label('value')).filter(*filters).group_by(Flow.time_point).order_by(Flow.time_point.asc()).all()

share|improve this question

edited Mar 27 at 6:22

Ilja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

asked Mar 27 at 6:17

Skyrim SnowSkyrim Snow

333 bronze badges

share|improve this question

edited Mar 27 at 6:22

Ilja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

asked Mar 27 at 6:17

Skyrim SnowSkyrim Snow

333 bronze badges

edited Mar 27 at 6:22

Ilja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

edited Mar 27 at 6:22

Ilja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

asked Mar 27 at 6:17

Skyrim SnowSkyrim Snow

333 bronze badges

asked Mar 27 at 6:17

Skyrim SnowSkyrim Snow

333 bronze badges

1 Answer
1

active

oldest

votes

2

When domain_name is not 'all' you end up performing an implicit CROSS JOIN between Flow and Bandwidth. When you add the IN predicate to your list of filters SQLAlchemy also picks up Bandwidth as a FROM object. As there is no explicit join between the two, the query will end up as something like:

SELECT flow.time_point, SUM(flow.value) AS value FROM flow, bandwidth WHERE ...
 -- ^
 -- `- This is the problem

In the worst case the planner produces a query that first joins every row from Flow with every row from Bandwidth. If your tables are even moderately big, the resulting set of rows can be huge.

Without seeing your models it is impossible to produce an exact solution, but in general you should include the proper join in your query, if you include Bandwidth:

query = db.session.query(Flow.time_point, db.func.sum(Flow.value).label('value'))

filters.append(Flow.time_point >= datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
filters.append(Flow.time_point <= datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S'))

if domain_name != 'all':
 query = query.join(Bandwidth)
 filters.append(Bandwidth.domain_name.in_(domain_name.split('|')))

flow_list = query.
 filter(*filters).
 group_by(Flow.time_point).
 order_by(Flow.time_point.asc()).
 all()

If there are no foreign keys connecting your models, you must provide the ON clause as the second argument to Query.join() explicitly.

share|improve this answer

edited Mar 27 at 12:42

answered Mar 27 at 6:35

Ilja EveriläIlja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much, I forgot to add a foreign key.
  
  – Skyrim Snow
  Mar 27 at 6:39
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55370923%2fsqlalchemy-query-is-very-slow-after-using-the-in-method%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

When domain_name is not 'all' you end up performing an implicit CROSS JOIN between Flow and Bandwidth. When you add the IN predicate to your list of filters SQLAlchemy also picks up Bandwidth as a FROM object. As there is no explicit join between the two, the query will end up as something like:

SELECT flow.time_point, SUM(flow.value) AS value FROM flow, bandwidth WHERE ...
 -- ^
 -- `- This is the problem

In the worst case the planner produces a query that first joins every row from Flow with every row from Bandwidth. If your tables are even moderately big, the resulting set of rows can be huge.

Without seeing your models it is impossible to produce an exact solution, but in general you should include the proper join in your query, if you include Bandwidth:

query = db.session.query(Flow.time_point, db.func.sum(Flow.value).label('value'))

filters.append(Flow.time_point >= datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
filters.append(Flow.time_point <= datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S'))

if domain_name != 'all':
 query = query.join(Bandwidth)
 filters.append(Bandwidth.domain_name.in_(domain_name.split('|')))

flow_list = query.
 filter(*filters).
 group_by(Flow.time_point).
 order_by(Flow.time_point.asc()).
 all()

If there are no foreign keys connecting your models, you must provide the ON clause as the second argument to Query.join() explicitly.

share|improve this answer

edited Mar 27 at 12:42

answered Mar 27 at 6:35

Ilja EveriläIlja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much, I forgot to add a foreign key.
  
  – Skyrim Snow
  Mar 27 at 6:39
  

  
  
  
  

add a comment | 

2

When domain_name is not 'all' you end up performing an implicit CROSS JOIN between Flow and Bandwidth. When you add the IN predicate to your list of filters SQLAlchemy also picks up Bandwidth as a FROM object. As there is no explicit join between the two, the query will end up as something like:

SELECT flow.time_point, SUM(flow.value) AS value FROM flow, bandwidth WHERE ...
 -- ^
 -- `- This is the problem

In the worst case the planner produces a query that first joins every row from Flow with every row from Bandwidth. If your tables are even moderately big, the resulting set of rows can be huge.

Without seeing your models it is impossible to produce an exact solution, but in general you should include the proper join in your query, if you include Bandwidth:

query = db.session.query(Flow.time_point, db.func.sum(Flow.value).label('value'))

filters.append(Flow.time_point >= datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
filters.append(Flow.time_point <= datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S'))

if domain_name != 'all':
 query = query.join(Bandwidth)
 filters.append(Bandwidth.domain_name.in_(domain_name.split('|')))

flow_list = query.
 filter(*filters).
 group_by(Flow.time_point).
 order_by(Flow.time_point.asc()).
 all()

If there are no foreign keys connecting your models, you must provide the ON clause as the second argument to Query.join() explicitly.

share|improve this answer

edited Mar 27 at 12:42

answered Mar 27 at 6:35

Ilja EveriläIlja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you very much, I forgot to add a foreign key.
  
  – Skyrim Snow
  Mar 27 at 6:39
  

  
  
  
  

add a comment | 

When domain_name is not 'all' you end up performing an implicit CROSS JOIN between Flow and Bandwidth. When you add the IN predicate to your list of filters SQLAlchemy also picks up Bandwidth as a FROM object. As there is no explicit join between the two, the query will end up as something like:

SELECT flow.time_point, SUM(flow.value) AS value FROM flow, bandwidth WHERE ...
 -- ^
 -- `- This is the problem

In the worst case the planner produces a query that first joins every row from Flow with every row from Bandwidth. If your tables are even moderately big, the resulting set of rows can be huge.

Without seeing your models it is impossible to produce an exact solution, but in general you should include the proper join in your query, if you include Bandwidth:

query = db.session.query(Flow.time_point, db.func.sum(Flow.value).label('value'))

filters.append(Flow.time_point >= datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
filters.append(Flow.time_point <= datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S'))

if domain_name != 'all':
 query = query.join(Bandwidth)
 filters.append(Bandwidth.domain_name.in_(domain_name.split('|')))

flow_list = query.
 filter(*filters).
 group_by(Flow.time_point).
 order_by(Flow.time_point.asc()).
 all()

If there are no foreign keys connecting your models, you must provide the ON clause as the second argument to Query.join() explicitly.

share|improve this answer

edited Mar 27 at 12:42

answered Mar 27 at 6:35

Ilja EveriläIlja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

SELECT flow.time_point, SUM(flow.value) AS value FROM flow, bandwidth WHERE ...
 -- ^
 -- `- This is the problem

query = db.session.query(Flow.time_point, db.func.sum(Flow.value).label('value'))

filters.append(Flow.time_point >= datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S'))
filters.append(Flow.time_point <= datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S'))

if domain_name != 'all':
 query = query.join(Bandwidth)
 filters.append(Bandwidth.domain_name.in_(domain_name.split('|')))

flow_list = query.
 filter(*filters).
 group_by(Flow.time_point).
 order_by(Flow.time_point.asc()).
 all()

share|improve this answer

edited Mar 27 at 12:42

answered Mar 27 at 6:35

Ilja EveriläIlja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

answered Mar 27 at 6:35

Ilja EveriläIlja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges

answered Mar 27 at 6:35

Ilja EveriläIlja Everilä

28.2k55 gold badges4040 silver badges6868 bronze badges