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.

image

Take the OR out, boom. All seeks.  A nasty bookmark lookup resulted, but I’ll fix that little red wagon later.

image

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: