When a formula uses multiple conditions, they can be joined with either the AND operator or the OR operator. Each affects performance differently. To understand how this works, you have to understand how each operator is used.
When two or more conditions are combined using the AND operator, Crystal looks at each condition independently. If a condition can be converted to SQL, it is appended to the WHERE clause and passed down to the server. Any conditions that can’t be converted are left for the report engine to process. Crystal will pass as many conditions down to the server as it can and leave the rest for the client. The result is improved performance because, even though the client has to process some of the records, there will be fewer to process. Many records have already been filtered out by the server.
The OR operator works differently from the AND operator. When using the OR operator, Crystal Reports looks at all the conditions as a whole. Like the AND operator, it tries to convert each condition into SQL. But this time, if it finds that any of the conditions can’t be converted, none of them will be converted. For example, assume a record selection formula has three conditions and they are joined using the OR operator. If the first two conditions can be converted to SQL, but the last one can’t, then none of them will be passed down to the server. The entire record selection formula will be processed by the client.
The reason for this is that when using the OR operator, all records are tested for every of the stated condition. Only passing some of the conditions doesn’t reduce the number or records that need to be passed to the client. For example, assume that there are two conditions and one of the two conditions was passed to the server. After the server processes the SELECT statement, it is left with 100 records. Even if the server performs the first test and 70 records fail, there is still a chance that these 70 records will pass the second test. However, the second test is on the client, which means the 70 records have to get passed to the client for testing. In effect, the server ends up passing all the records to the client using the OR operator didn’t speed up the processing at all.
If you are using a PC database, then those rules don’t apply. Using a PC database means you can’t use the OR operator at all. Whether the individual conditions can be converted to SQL or not won’t have any effect.
