Report Builder Performance Tips

Yahoo Archive
1
FYI - Yesterday I watched the tech video on the Epicor Support site about Crystal Subreports and noticed the RB Performance one so I watched it too. Very good info there too. Short but well worth it to learn about using indexes better.
-Todd C.

-----Original Message-----
From: Jasper Recto [mailto:jrecto@...]
Sent: Thursday, January 13, 2005 10:12 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Performance Tips


Boy,

I wish I would of asked these questions a long time ago. I've taken some of my reports I've made and recreated them using your tips. DRASTIC performance improvements!! Minutes to seconds!
Thanks!!
Jasper





[Non-text portions of this message have been removed]
2
If you had a choice between creating a group and then aggregating to get a value OR creating a calculated field to keep a running value, which would be the more efficient way?

Which is the lesser of 2 evils, having more groups or having more calculations?

Thanks,

Jasper





[Non-text portions of this message have been removed]
3
Sort/Aggregate can possibly mean a seperate pass thru the collected record complex after all the filter selections have been performed. Plain arithmetic calculations are done on the client end thus are more effecient. If the record complex is going to be in the correct sort order already (based on primary index of the master table) then it would be faster (I think) to calculate it. Especially if there is a very large number of "records" involved. Otherwise if a sort has to be done anyway then aggregate it. I've never actually tried a running total with a calculated field since 90% of my reports report at group footers rather than at the record level (which I suppress printing of) so sorting is happening anyway. But if a sort matches an index RB will use the index anyway. I think the only time aggregating really bites performance is when using pre-pass and it has to add another pass thru the complex.

While looking up info on this another tip came to mind for filters....never use In-Range or In-List on large tables. These are evaluated at the server not the client so the data has to pass to the server for each test (and the server is "serving" other users too). Better to make a calculated field to set a boolean value for the filter to use. The situation that most commonly hits performance is using In-Range with a pair of dates...far better to use two seperate filters for >= and <= on the start/end dates.

-Todd C.

-----Original Message-----
From: Jasper Recto [mailto:jrecto@...]
Sent: Monday, January 10, 2005 2:38 PM
To: Vantage Groups (E-mail)
Subject: [Vantage] Report Builder Performance Tips


If you had a choice between creating a group and then aggregating to get a value OR creating a calculated field to keep a running value, which would be the more efficient way?

Which is the lesser of 2 evils, having more groups or having more calculations?

Thanks,

Jasper






[Non-text portions of this message have been removed]
4
Boy,

I wish I would of asked these questions a long time ago. I've taken some of my reports I've made and recreated them using your tips. DRASTIC performance improvements!! Minutes to seconds!
Thanks!!
Jasper

-----Original Message-----
From: Todd Caughey [mailto:caugheyt@...]
Sent: Monday, January 10, 2005 4:44 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Performance Tips



Sort/Aggregate can possibly mean a seperate pass thru the collected record complex after all the filter selections have been performed. Plain arithmetic calculations are done on the client end thus are more effecient. If the record complex is going to be in the correct sort order already (based on primary index of the master table) then it would be faster (I think) to calculate it. Especially if there is a very large number of "records" involved. Otherwise if a sort has to be done anyway then aggregate it. I've never actually tried a running total with a calculated field since 90% of my reports report at group footers rather than at the record level (which I suppress printing of) so sorting is happening anyway. But if a sort matches an index RB will use the index anyway. I think the only time aggregating really bites performance is when using pre-pass and it has to add another pass thru the complex.

While looking up info on this another tip came to mind for filters....never use In-Range or In-List on large tables. These are evaluated at the server not the client so the data has to pass to the server for each test (and the server is "serving" other users too). Better to make a calculated field to set a boolean value for the filter to use. The situation that most commonly hits performance is using In-Range with a pair of dates...far better to use two seperate filters for >= and <= on the start/end dates.

-Todd C.

-----Original Message-----
From: Jasper Recto [mailto:jrecto@...]
Sent: Monday, January 10, 2005 2:38 PM
To: Vantage Groups (E-mail)
Subject: [Vantage] Report Builder Performance Tips


If you had a choice between creating a group and then aggregating to get a value OR creating a calculated field to keep a running value, which would be the more efficient way?

Which is the lesser of 2 evils, having more groups or having more calculations?

Thanks,

Jasper






[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links