Optimizing Boolean Operators
There are some special considerations when using a selection formula that has multiple conditions. By default, when you use the Select Expert dialog box, each condition is joined using a Boolean And. For a record to be selected for printing it must meet all the criteria specified in the selection formula. If it doesn’t match any one of the criteria, then it doesn’t get printed. When Crystal Reports tries to convert the record selection formula to SQL, it looks at each condition individually and it converts the ones that can be converted. If a condition can’t be converted, then it leaves it out of the SQL WHERE clause and performs the logic itself while printing the report.
The tricky part comes into play when you click the Show Formula button on the Select Expert dialog box and make changes to the formula. At this point, you can modify the formula to use a combination of Boolean operators including the Or operator. When a selection formula uses the Or operator, the rules change. The conversion now becomes an “all or nothing” process. Either all the conditions are converted to SQL or none of them are. To understand this process, you have to understand how each Boolean operator works.
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 possible and leave the rest for the client to perform. 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 and you will see slower performance.
The reason for this is that when using the Or operator, every record has to be tested for each of the stated conditions. 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 and it didn’t help 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.
One way to ensure the selection formula gets converted into SQL is to write it using SQL expressions. As mentioned previously, a SQL expression field is a formula that is built using only valid SQL functions. Since it only has valid SQL functions, Crystal Reports will always be able to convert it to a valid WHERE clause. SQL Expressions are explained in greater detail in a later section.
