Crystal Report 2008 taking 2 hours to run

I have a report that I created that is taking over 2 hrs to run.   I am attaching as much information about the report as I can without giving out Patient Information.  

If you need anything else, please let me know.

KR
H--CR-report-Question.docx
kvrogersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
The problem is probably due to the
{@DOS} = {?Date of Service}
in the record selection condition.

Find a way to convert {@DOS} from a regular formula to a SQL Expression or use another approach to make sure that condition finds its way into the SQL WHERE clause.
0
mlmccCommented:
Agree.  If you look at the SQL, the date filter isn't included therefore all the data is being returned to the report (client) for filtering based on the date.

What is the @DOS formula?
I suspect it is converting the date from the database format to a date.  Rather than converting the database date how about converting the date the user enters to the correct format for comparison with the database date.

One thing you could do would be to add a filter on the date of service field in the database to limit the records to this year.  That would probably pass fewer records to the report for filtering.
0
kvrogersAuthor Commented:
Sorry, I have responded I was off work yesterday.  

The @DOS formula is CDATE({Assc99.D_Appt}-18261).  The software dictionary says:
"The CDate (number) is a value representing the number of days starting from December 30,
1899. The date can be positive or negative, and is truncated if fractional. There are 18261 days
between 01/01/1850 and 12/30/1899 and then another 18263 days to 12/31/1950. The long
integer dates would be CDate (value-18261) and the short integer dates would be CDate
(value+18263)."

This is the formula that is used in the report:
ToText ({@DOS}, "MMddyyyy")

KR
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
That confirms the suggestions above.
0
kvrogersAuthor Commented:
The report also has a date range which is usually 1 weeks worth of data.
When the report is run the date range to pull is entered.

How would I make this SQL formula appear in the WHERE CLAUSE  to show the DOS formula?  Do I use the @DOS formula or the @New DOS?

KR
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
0
mlmccCommented:
You need to convert the date entered into a number.

If the date field is really the number of days since 30 Dec 1899 try this as the filter

{Assc99.D_Appt} = DateDiff('d',Date(1899,12,30),{?Date of Service})

mlmcc
0
kvrogersAuthor Commented:
I tried entering the formula above in the Formula Workshop and got this error attached.

KR
H--CR-error.docx
0
James0628Commented:
What type of value is {?Date of Service} ?  The assumption was that it's a date or datetime, but apparently it's something else.

 James
0
kvrogersAuthor Commented:
{?Date of Service} is a parameter.  I have added it to the original file I sent.
H--H--CR-report-Question.docx
0
kvrogersAuthor Commented:
I have eliminated all patient data and here is the report.
H--FPO-Press-Ganey-test-date.rpt
0
mlmccCommented:
You allow a range for the date of service.

Try this

{Mrpx99.Pat_UD_Label} = "Language" and
{Assc99.Appt_Status} = "OUT" and
{Assc99.D_Appt} >=DateDiff('d',Date(1899,12,30),  minimum({?Date of Service}))  + 18261 and
{Assc99.D_Appt} <= DateDiff('d',Date(1899,12,30),  maximum({?Date of Service})) + 18261 and
{Ppprov99.Prov_Kind} <> "Psych"

Open in new window


mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kvrogersAuthor Commented:
OMG.  It only took 1 minute to run this report.  I ran it three times and changed the dates because I couldn't believe it.  
One question though.  In the script you say  + 18261 and my script said -18261.  I just wondered why that still worked?

KR
0
mlmccCommented:
It is on the other side of the compare.

mlmcc
0
kvrogersAuthor Commented:
thanks again for all your help.

KR
0
James0628Commented:
Just in case you don't really understand what's happening ...

 Your original formulas subtracted 18261 from Assc99.D_Appt, converted the result to a date, and then compared that date to the parameter.  That meant that CR had to read each record that met your other conditions, so that it could get D_Appt, subtract 18261, etc.

 The new formula takes the opposite approach, converting the dates in your parameter to numbers and adjusting them to match the values in D_Appt by adding 18261, so CR can just pass those values to the server, so that they can be compared to D_Appt on the server, instead of CR reading each record and checking it.  So CR is only sent the records for the desired dates.  If you're dealing with a lot of data, that kind of thing can make a huge difference.

 And if you already knew all of that, nevermind.  :-)

 James
0
kvrogersAuthor Commented:
Thanks to everyone.  I am keeping all this valuable information in a folder for future reference.

KR
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.