Will Query Report joining 4 tables have system impact?

[kothagunda]

Hello

I am working on a functionality where a member doctype will do sale, pur, payments etc. So I am writing a Query Report(as i dont know python script query) , which will join 4-5 tables. Like Sales Inv, Pur Inv, Payment Entry, Journal Entry for that member to get all sales, pur, payments in one report.

I see that all the current queries are not specific to a member or customer. First the query is run without any where clause, and then a filter of customer/member can be put on top of it. I am also thinking of using the same approach

But my My question/concern is:

1. If i join these huge 4 tables, will the system crash , as it runs the query irrespective of a member (as i dont put a WHERE member=xyz), in the first run. [is there a batch size, or will it get every row to the browser]. As it tries to get every row.
2. Once the query is run for all members, then in the filter we put member-id, will this, send a second query to the server side.
3. If i put a docField as member_id in the QueryReport DocType, can i use it in the sql query as doc.member_id the way we do in custom script. (But the member_id will be shown as a field in all the query report forms, which is not good)

Please let me know.

[tundebabzy]

Joins always have a performance impact. How many tables you should join just depends on the environment and requirements.

@kothagunda

If i join these huge 4 tables, will the system crash , as it runs the query irrespective of a member (as i dont put a WHERE member=xyz), in the first run. [is there a batch size, or will it get every row to the browser]. As it tries to get every row.

There’s no way to tell. Test it

@kothagunda

Once the query is run for all members, then in the filter we put member-id, will this, send a second query to the server side.

Yes

I believe you should just test your concerns on your development setup and then optimise as needed.

[kothagunda]

Thanks for the reply. Couple of queries.

1. Is there a pagination setting on how many rows are fetched.
2. If i put a docField as member_id in the QueryReport DocType, can i use it in the sql query as doc.member_id the way we do in custom script. (But the member_id will be shown as a field in all the query report forms, which is not good)

[tundebabzy]

@kothagunda

Is there a pagination setting on how many rows are fetched.

Most of the ORM methods are abstractions over the execute function in db_query.py (in frappe). The parameters include limit, limit_start, limit_page_length which you can use to slice query results

@kothagunda

If i put a docField as member_id in the QueryReport DocType, can i use it in the sql query as doc.member_id the way we do in custom script. (But the member_id will be shown as a field in all the query report forms, which is not good)

sure

[kothagunda]

Actually i am talking about query report, not script report.
Can you point me to any query reports which uses these limits, and uses fields from the QueryReport docType please.