Re: [gnome-db] Multiple queries in report stuff

[Rodrigo Moya <rodrigo gnome-db org>]

On Mon, 2003-01-20 at 18:39, Santi Camps wrote:
> Hi all again,
> 
> I've been thinking about how data is processed in most report engines,
>
> and I think we can easy make some improvements on it.  I will explain it
> better with an example:
> 
> Imagine we want make a report with all account numbers of all costumers
> of all offices of a bank.  Something like this:
> 
> BANK NAME
> 	OFFICE ID, NAME AND ADDRESS
> 		CUSTOMER ID, NAME AND ADDRESS	
> 			ACCOUNT INFORMATION
> 			ACCOUNT INFORMATION
> 			ACCOUNT INFORMATION
> 		CUSTOMER ID, NAME AND ADDRESS	
> 			ACCOUNT INFORMATION
> 			ACCOUNT INFORMATION
> 			ACCOUNT INFORMATION
> 
> 	OFFICE ID, NAME AND ADDRESS
> 		CUSTOMER ID, NAME AND ADDRESS	
> 			ACCOUNT INFORMATION
> 			ACCOUNT INFORMATION
> 			ACCOUNT INFORMATION
> 		CUSTOMER ID, NAME AND ADDRESS	
> 			ACCOUNT INFORMATION
> 			ACCOUNT INFORMATION
> 			ACCOUNT INFORMATION
> 
> The normal way to do this on most report engines will be to have one big
> joined query with all the data:
> 
> SELECT ...
> FROM   bank, office, customer, account
> WHERE  ...
> 
> And then define groups in the report-engine.  In other words, we first
> obtain a big plain data and then we need to structure it.  Bank
> information appears once in the report but it should be in all the
> records of the recordset.  More memory and more data traffic that are
> not really needed.
>
what do you mean by more memory and data traffic? If the data is read
only once and stored in only one place, there is neither more memory nor
data traffic.

>   And, more of this, AFAIK a big joined query will be
> slower than some smaller queries in most database providers.
> 
this really depends on how the database is structured, and I would even
say that a well done (indexed and such) join query can be a lot quicker
than multiple smaller queries. This is specially true on RDBMS that, for
each query, have to parse, prepare and run it, operations that, for
Oracle for instance, take some time.

> My proposal is to change this way of work, and define multiple nested
> queries.  Something like this:
> 
> <sql_query id="query_bank">
> 	select * from bank
> </sql_query>
> <sql_query id="query_office">
> 	select * 
>         from office 
>         where bank_id=:query_bank.id_bank
> </sql_query>
> <sql_query id="query_customer">
> 	select * 
>         from customer c, accounts a 
>         where c.customer_id = a.customer_id 
>         and a.office_id = :query_office.office_id
> </sql_query>
> <sql_query id="query_accounts>
> 	select * 
>         from accounts 
> 	where customer_id = :query_customer.customer_id
> 	and office_id = :query_office.office_id
> </sql_query>
> 
I think all this is up to the user of the report engine. We just have to
make sure to allow multiple queries, and just let each user design
his/her reports as they wish.

> This has the handicap that is a new way to work, so it could be rare for
> new users.   
> 
It is not. I've made a lot of reports with multiple queries (because it
was impossible to get all in one big query). Unfortunately, I used a
very bad tool for that (Prolifics' report designer www.prolifics.com),
but fortunately that was a long time ago :-)

> Do you think that this could be interesting, or it's better not to
> reinvent the wheel and do the same that other engines ?
> 
I would say it is not only interesting, but mandatory. I personally
don't know too many report tools, but I would say that one that only
allows simple queries is a handicapped one. I wouldn't have been able to
use them, some years ago, for the reports I did.

cheers