|Best performance question [message #915916]
||Tue, 18 September 2012 08:03
| Blaine Mising name
Registered: December 2010
I've a report that shows all our products between 2 dates (date start, date end)|
The report shows all the products in a table. The table shows a product by row. In each row there is other table that shows the product added date, product deleted date and other table that contais the list of all the modifications dates of the product.
Not all the produts must have delete date.
All the information is in a unique table.
The product have an id, then all information of an ID is the same product.
The table contains a column that specifies if the register is inserted(I), updated (U) or deleted (D)
My report actually shows a parameter window asking 2 values:
1. Start Date
2. End Date
It uses 3 Data set:
1. A select to get the insertion date of a product ID.
2. A select to get the product delete date of a product ID (same id as data set 1 and 2)
3. A select to get the updated date of the same product id that data set 1
The problem is that the product table has millions of registers, and the report generation is very slow because the 3 data sets are sql under other sql's. I can't modify table structure.
Is this the best solution? If not, how can I improve the report performance without modifying table?
It's possible for example obtain all data between dates in a unique SQL (a data set) and do conditions with birt to show in each row the needed information?
[Updated on: Tue, 18 September 2012 08:05]
Report message to a moderator
Powered by FUDForum
. Page generated in 0.08391 seconds