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 biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parameters slow down report

Status
Not open for further replies.

khoke

Programmer
Jul 8, 2005
20
US
I have a report in CR XI, which generates approximately 31,000 records. If I do not use parameters, the report runs within several seconds.
If I use parameters to narrow down the number of records I return, the report slows down, and takes approximately 15 minutes to generate.

Any idea why this is happening? I do not understand why the report slows down when I try to narrow down the number of results I want to return?

I am using CRXI, and am connected to a SQL database through an ODBC connection. I am using 3 parameters, and using them dynamically-
So, I select a 'department,' which then determines the 'unit,' I can selected, and based on that selection will determine which 'type' I can select.

Thanks. Kelly
 
Look in the database\show SQL query, are the dynamic cascading filters showing up in the where clause. If not, CR is handling everything within the report which would slow down your processing time.

Thanks so much!
satinsilhouette
 
Yes, the dynamic parameters are showing within the where clause.
Thanks!
 
If you have proper indexes in palce, then the filtering should speed up the report.

You should have pasted in what is in the Database->ShowSQL Query

There should be 3 different criteria within, since you have 3 parameters.

If that is the case, then copy the SQL and send it to your dba for analysis, sounds like they have an index which is out of synch.

-k
 
Cut and paste your record selection formula, and cut and paste your database->show SQL query to this thread.

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

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown
 
Here is the SQL:

What does the N' stand for???

THANKS!

SELECT HomeDepartment, HomeUnit, Type, MiddleName, LastName, FirstName, StaffHomeCostCenter, InActive, DefaultStaff, ClassDate, ClockHours, HireDate, StartDate
FROM HOV_StaffInformationHomeDeptAndHomeLocation
LEFT OUTER JOIN HOV_AllEmployeeEducationClockHours ON UserID=UserID
WHERE DefaultStaff=0 AND InActive=0 AND StaffHomeCostCenter<>N'' AND HireDate<{ts '2006-10-12 00:00:00'} AND HomeDepartment='Central Clinical' AND HomeUnit=N'Central Clinical' AND (Type=N'CNA' OR Type=N'LPN' OR Type=N'RN')
ORDER BY HomeDepartment, HomeUnit, Type


 
Please post the record selection formula as well. We cannot tell what is and is not passing thru to SQL without both pieces of data.

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

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown
 
Sorry! Thank you for your help!

HOV_StaffInformationHomeDeptAndHomeLocation.DefaultStaff} = False
and {HOV_StaffInformationHomeDeptAndHomeLocation.InActive} = False
and {HOV_StaffInformationHomeDeptAndHomeLocation.StaffHomeCostCenter} <> ''
AND
{HOV_StaffInformationHomeDeptAndHomeLocation.HireDate} <= {?StopDate}
AND
{HOV_StaffInformationHomeDeptAndHomeLocation.HomeDepartment} = {?Location - HomeDepartment}
AND {HOV_StaffInformationHomeDeptAndHomeLocation.HomeUnit} = {?Location - HomeUnit}
AND {HOV_StaffInformationHomeDeptAndHomeLocation.Type} = {?Location - Type}

 
Your parameters for Location, Home Department, Home Unit and Type are not coming through in the where clause of the show sql query.

In your select expert you can take your home dept. field and make it equal to your parameter and the same with the other two pieces of the cascading prompt. Then you should see them show up in the show sql query where clause.

Indexes was a good point, if doing this additionally doesn't speed up your report then look at using an indexed field in your select expert.

Thanks so much!
satinsilhouette
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top