OR statement in WHERE clause
Don’t do it. Here is a good example why:
|The images are blurry on purpose. What I want you to notice is the thickness of the lines on the plans between the two images.
Table scans galore against a table with 1 million + rows. This is the most commonly called sql statement in the database as well. In case you can’t read it in the image below, it’s returning almost 750,000 rows from the scan.
|Take the OR out, boom. All seeks. A nasty bookmark lookup resulted, but I’ll fix that little red wagon later.|
Due to the removal of the OR statement it’s not returning as many rows, but the row count difference is marginal with and without the OR. As opposed to the above, this returns only 369 rows from the table.
Unfortunately, the developers have coded about 5 OR statements in a dynamically generated sql string. They’ve essentially painted themselves into a corner. It’s pretty evil.