Poor Performance with Current Date – 1 1

[Bennie47250]

Using Crystal Ver. 7.0 against a DB2 (AS400) table with a 3rd party ODBC driver

When the report run using CurrentDate -1 to pull the previous days records, the report takes over 5 minutes to run. However if I use just CurrentDate, the report runs in less than 15 seconds.

Why is this occurring and how can I work around it?

The report will be scheduled to run each day but I prefer to have reports run in the morning for the previous day. I do this because if a report fails to run or doesn’t run properly, it is easier to recover using Current Date -1.

Thanks
Bennie

 

[BlurredVision]

Is it any quicker if you use say, {date_field} = 04/01/2003?

Try this

{date_field} = trunc(sysdate -1)

 

[Bennie47250]

Yes the report does run faster with the date hard coded.

I can't seem to find the trunc function. I'm using version 7.0 so it must be a newer function.

 

[Naith]

The reason you're finding this is most likely because your CurrentDate-1 is not passing to the database, while CurrentDate and specific hardcoded dates are.

Check this by looking at your generated SQL.

If CurrentDate is not passing to the database, use the DateDiff function - (which I can't remember if version 7 has or not. You may have to download this from the Crystal support site) - and check if this passes to the database instead.

Naith

 

[BlurredVision]

Insert the trunc(sysdate -1) into the SQL statement of your report.. That should work. Sorry about that.

 

[Bennie47250]

Naith, Looking at the SQL statement, it dosent appear that the date is being passed. I have searched the Crystal Decesion site and can't find the I can't find the DateDiff function. Can you help me find it?

BlurredVision. I tried what you suggested and still can't get it to work.

 

[Naith]

http://support.crystaldecisions.com/updates

and search for ufldtdif.exe.

Blurred Visions sysdate suggestion is a good one, and will pass to the database, if you enter it directly into your SQL query. However, SysDate is not a universally accepted syntax. It is recognised by Oracle, but is GetDate on Sybase or SQL Server, for example. It really depends on what your back end is.

If you were to use whatever your system date function was for your database, and use it as Blurred Vision has suggested, but in place of sysdate, you should find this also passes to the database successfully.

Naith

 

[IdoMillet]

Or just create an "SQL Expression" in Crystal (just like creating a formula, but restricted to the functions supported by the specific DBMS you are connecting to).

Then, use the SQL expression in your record selection formula.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Bennie47250]

Thanks everyone for the suggestions.

I checked the SQL for the AS400 book and it has the Current Date syntax as CURDATE() but Crystal does not like it.

Uncle Uncle (I give up)

I will use a suggestion Ken Hamady made on another posting to a similar problem. Kens post was “You can also add a fixed literal rule to the select formula to always eliminate records that are older than 1/1/2001, since you should never need those again. Use this rule in addition to the rule you have, and it will keep the report from needing a full table scan. At most it will start with the beginning of this year, and then select based on your parameter.”

So the formula I’m using looks like this:
{OBXXX.COODT} > Date (2003, 06, 09) and
{OBXXX.COODT} = CurrentDate -1

I think I will put myself a note in Outlook to update the report every so often to change the > date.

 

[dgillz]

The dateadd() function is here:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/ufldateadd.exe.asp

In your case, your record selection formula would be:

{YourDateField} = Dateadd("d",-1,CurrentDate)

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Bennie47250]

Thanks dgillz!

 

[Naith]

Cheers dgillz. I'm always mixing those two up.