How to deal with large amount of tables and complex where's
We do have a lot tables (several hundreds). For a lot of reports we need dozens of tables, each of these tables does have 10.000+ rows.
The endusers like to filter (add a where) on several columns in different tables. Therefor we add a WHERE in the application.
What is the best way to solve this?
1. create one big query with alias "Table" in the report.
Table with a big query:
select * from table1 inner join table2 inner join table3, .... table15
in the application we add the where (by adding this where the server returns onlu 10 rows):
where table1.col1 = 'ABC' and table5.col3 like 'test%' and table6.col2 between 10 and 15 ;
2. add all the tables to the report, and create a relation between them with a Fastreport relation.
After that add the where per table.
select * from table1 where col1 = 'ABC'
select * from table2
.
select * from table5 where col3 like 'test%'
.
select from table6 where col2 between 10 and 15;
Option 1; can be runned completely at the server and would give great performance but in Fastreport that will give only one Table. Is that a problem for creating report with groups and totals ?
Option 2; seems to me that this option will give HUGE performance problems, because there will be executed 15 seperate SQL statements at the server, all these statements returns a result set for every table that is far to big.
What are the advantages and disadvantages of both options ?
Thanks
Hans
The endusers like to filter (add a where) on several columns in different tables. Therefor we add a WHERE in the application.
What is the best way to solve this?
1. create one big query with alias "Table" in the report.
Table with a big query:
select * from table1 inner join table2 inner join table3, .... table15
in the application we add the where (by adding this where the server returns onlu 10 rows):
where table1.col1 = 'ABC' and table5.col3 like 'test%' and table6.col2 between 10 and 15 ;
2. add all the tables to the report, and create a relation between them with a Fastreport relation.
After that add the where per table.
select * from table1 where col1 = 'ABC'
select * from table2
.
select * from table5 where col3 like 'test%'
.
select from table6 where col2 between 10 and 15;
Option 1; can be runned completely at the server and would give great performance but in Fastreport that will give only one Table. Is that a problem for creating report with groups and totals ?
Option 2; seems to me that this option will give HUGE performance problems, because there will be executed 15 seperate SQL statements at the server, all these statements returns a result set for every table that is far to big.
What are the advantages and disadvantages of both options ?
Thanks
Hans
Comments
The first option is the best solution. There will be no problem with group report because it uses one table only. Two or more tables needed if you want to make master-detail(-subdetail...) report.