Select record condition with 'IN' affect the speed of my report

[Benbsrd]

Hello,
I'm trying to use a select record in Crystal Report.

when I put a simple condition, I have the result quickly
{INVENTORY_PART.PART_NO} = {?PART_NO}
=> when I look at the SQL query in Crystal I have this select condition right mentionned.

when I want to use the IN condition it reduces the speed of the query
{INVENTORY_PART.PART_NO} in {?PART_NO}
=> when I look at the SQL query in Crystal I haven't this select condition mentionned => it means that Crystal reads all the records (40000 records) to select the data after (?)


I have to do a select with the IN condition because my parameter {?PART_NO} will have these data : '123344','456789','963254'

How do I improve that ?

 

[Benbsrd]

I add to my Thread these informations :
my report is a simple label with the part number and his designation.

I've read some posts dealing with calling a sub report
Is it possible to call x times a sub report with, for each call, a parameter ?
This parameter should be a part number, belonging to the string '123344','456789','963254','999999' ?

your help would be very appreciate

 

[kray4660]

Using the IN can change the performance. It is just a result on how the query has to process the data. I do not how your main report is formatted, but if you were to group by the part number and put the sub-report in the group header, detail, or group footer, then it would call the sub-report for each part number. I do not know if the performance would be any better.

 

[jf111]

I have always found the performance considerably better when using the SQL command directly (rather than letting Crystal build it). It might be worth a try.

The syntax would be something like this;

select part_no, etc, etc
from inventory_part
where inventory_part.part_no in ({?PART_NO})