Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
does date range selection slow down a report considerably?
- Thread starter mwhalen
- Start date
Rhinok is right when he asks for a more detailed explanation.But I will try to give information for selection criterias.
The database structure achieves the records faster the fields are indexed.Ýf you create a sql statement whose criteria deals with the indexed fields the results achievement time will be shorter.
I tihnk in your report,those date fields are ordinary fields.So when you enter a date selection criteria the performance decreases.
In fact there can not be much things to be done.Because if you put indexes to each field the performance will be much slower that you expect.
Try this : If your user doesn't enter a date selection criteria as parameter,don't put it into your selection criteria.I will try to explain this.
Consider that I have a report that has selection criteria lets say hotel names and say the user doesn't enter a criteria for that.ok?If you send "0000" and "ZZZZ" and in your selection criteria you type <field> in param1 to param2.Then it will work slower.
Instead you can check your parameters:
if param1="0000" then true else field>param1 and
if param2="ZZZZ" then true else field<param2
Hope I was clear enough
The database structure achieves the records faster the fields are indexed.Ýf you create a sql statement whose criteria deals with the indexed fields the results achievement time will be shorter.
I tihnk in your report,those date fields are ordinary fields.So when you enter a date selection criteria the performance decreases.
In fact there can not be much things to be done.Because if you put indexes to each field the performance will be much slower that you expect.
Try this : If your user doesn't enter a date selection criteria as parameter,don't put it into your selection criteria.I will try to explain this.
Consider that I have a report that has selection criteria lets say hotel names and say the user doesn't enter a criteria for that.ok?If you send "0000" and "ZZZZ" and in your selection criteria you type <field> in param1 to param2.Then it will work slower.
Instead you can check your parameters:
if param1="0000" then true else field>param1 and
if param2="ZZZZ" then true else field<param2
Hope I was clear enough
- Thread starter
- #4
There may be things done in your database part also.Creating views in a database can increase the performanca and allows you making additional progress.For example I need 2 tables in my report.One consists of 5 fields and the other consists of 4.
But you need only some of the fields of that table.Then create view that has only these fields. It is sure to work faster than the classical one.
Moreover you can find more interesting solutions if you are using a sophisticated database management system like DB2,Oracle etc.
But you need only some of the fields of that table.Then create view that has only these fields. It is sure to work faster than the classical one.
Moreover you can find more interesting solutions if you are using a sophisticated database management system like DB2,Oracle etc.
the first question is whether {PS_Y_GIFT.ENTRY_DTE} is a date field or a datetime field?
If it is datetime then the range should also be in that format.
{PS_Y_GIFT.ENTRY_DTE} in Date (2000,04,01) to Date (2001,05,31)
this expression ultimately is translated by Crystal to
{PS_Y_GIFT.ENTRY_DTE} >= Date (2000,04,01) and
{PS_Y_GIFT.ENTRY_DTE} <= Date (2000,05,31)
So normally I present my date selection in this format and it gets passed down to the server with no difficulty.
If it is datetime then the range should also be in that format.
{PS_Y_GIFT.ENTRY_DTE} in Date (2000,04,01) to Date (2001,05,31)
this expression ultimately is translated by Crystal to
{PS_Y_GIFT.ENTRY_DTE} >= Date (2000,04,01) and
{PS_Y_GIFT.ENTRY_DTE} <= Date (2000,05,31)
So normally I present my date selection in this format and it gets passed down to the server with no difficulty.
Server side processing is a big help in performance. You need to have a Summary report to do this - no details section can be available for viewing or for drilldown. Basically this does all the number crunching on the server and sends the client only the summary info, thus cutting back on network traffic. I have seen this make an 80% reduction in processing time. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office
- Status
Similar threads
- Locked
- Question
- Locked
- Question