Parameters slow down report

[khoke]

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

 

[satinsilhouette]

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

 

[khoke]

Yes, the dynamic parameters are showing within the where clause.
Thanks!

 

[satinsilhouette]

So it is still slow?

Thanks so much!
satinsilhouette

 

[synapsevampire]

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

 

[dgillz]

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

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[khoke]

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

 

[dgillz]

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

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[khoke]

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}

 

[satinsilhouette]

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