Performance Problem with Record Selection Criteria

[LoriWillard]

I am using Crystal Reports 8.5. We have a custom written .NET application that prints the reports thru a Enterprise version 8 web server.

My data source is SQL server 2000. My report is based on a view that is used in multiple reports. It has 3.5 million records in it if you open it with no filters.

My report has 2 parameters - {?PeriodEnd} and {?Employees}. {?Employees} parameter in the report is set to allow single value. Our custom front end allows us to pass multiple parameters for a field (like Employees).

The report selection criteria looks like this:
{Dept} = "123" and
{PeriodEndDate} = {?PeriodEnd} and
{EmpNo} IN {?Employees}

The report this way takes 10 minutes to run. If we change the {EmpNo} IN {?Employees} line to be {EmpNo}="1234" then it runs in about 10 seconds.

Does anyone have any suggestions for improving performance?

Thanks

 

[TheBlondOne]

Hi there,

Instead of {EmpNo} IN {?Employees} have you tried
{EmpNo} = {?Employees}. As far as i'm aware if you use a multiple parameter its really held as an 'array', so when you assign a field to equal the parameter it will just check each entry in the 'array' thus meaning that you can use an = sign instead of IN.

HTH





-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[GJParker]

Steve is correct if you use = instead of IN then CR wlll still filter on each employee.

Using = will also add the filter to the generated SQL and pass this to the database rather than using IN which will not be added to teh SQL and will perform the filtering locally. hence the difference in performance.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[LoriWillard]

Unfortunately that does not work for me. When I use = instead of IN for my record selection formula I get no results. Even if I set the parameter to allow multiple values - when I run the report from our web interface my report is empty.

Is this possibly from the syntax we are using to send the string? After I select my report parameters and click run, this is the url that is passed to the server:
http: //servername/reports/Test083105.rpt?init=actx:connect&promptOnRefresh=0&prompt0=1200,0627,6844,7915&prompt1=Date(2005,8,31)

Prompt0 are the EmpNo values. I have confirmed that there is actually data there that should be coming out.

Any thoughts?
Lori