Monthly Archives: April 2016

Getting all values when input parameter is NULL and still get IX seek behavior!!

Recently while working on a requirement for a client we came across this scenario. It’s fairly widely used although I would not recommend it. The example is in very simple terms explained below:-

SEEK

SCAN

 

If you look at the above query you will see that when the @int parameter has a value we get an Index Seek and everything is fine. When the parameter is NULL however we get a scan and we fetch all rows those with NULLS and those without NULLs. In this table the id column is a PK.

So the developer basically wants to fetch all rows when he doesn’t pass a value to the stored procedure input so naturally we need to scan the table else we seek a single value within the Index.

The problem with the way above query is written is that it becomes cumbersome when we deal with 3 or more parameters, such that when a combination is being passed we need to identify the combo and fetch rows that meet that combo.

So we rewrite the query as below:-

In the above query we replace the IF ELSE Logic with ISNULL and OR Conditions to fetch rows based on the input being passed. However if you notice the execution plan you will see that we now have a SCAN happening even when filtering for only a single row in the PK Column. So while we were able to simplify the query we have introduced a performance issue resulting in an increase in query execution time and number of logical reads.

Another common way the above query is written when your browse the forums is as below:-

In the above query we replace the OR condition and ISNULL logic with a case statement. This still doesn’t address the SCAN issue with the index.

At this point we have three different solutions that will work functionally but do not provide performance improvement we would like. Most people stop at one of the above three as shown in the forums listed below.

http://stackoverflow.com/questions/1671084/stored-proc-return-all-rows-when-parameters-are-null

http://stackoverflow.com/questions/10999322/sql-select-all-rows-if-parameter-is-null-else-only-select-matching-rows

http://stackoverflow.com/questions/6131542/how-to-return-all-records-if-parameter-is-null

There is also one approach as shown in the last link where Dynamic SQL is used to modify where criteria as needed. Naturally this is not recommended so all the reasons why Dynamic SQL is not recommended.

So what is the other choice we have!! Well the assumption is that the same logic needs to be applied to all the columns. This is not needed. For example in the above table the id column is a PK and will never have a NULL value in it. So we rewrite the query as shown below

Notice how by changing the way we filter the PK column we are able to achieve a IX Seek.

See the query execution plan for different combination below

Both parameters have values

 

NON PK Columns is filtered

 

BOTH inputs are NULL

 

Ideally the last case should never occur coz there will not be a need to perform select * from a table.

As you can see by filtering the PK column differently than the other columns we are able to perform SEEK operations when fetching just 1 row, 100 rows or all rows. Thus reducing the performance impact of OR conditions and CASE Statements.

Addendum

A comparison of time and Io stats for above approach vs ISNULL and OR Condition approach. Notice how while performing similar number of scans we are still able to avoid a parallelism operator. Also compare the relative costs of the two execution plans to each other.

Waging war against India Standard Time

If you’re an Indian you already know what I mean when I say India Standard Time. It’s the unique time zone that adds an additional 30 min to 1 hour to any time that was previously agreed upon. This UIST (unofficial IST) has been a part of Indian life since childhood. Waiting for buses that don’t come on time or waiting for people to inaugurate a function only to have them arrive very fashionably late or booking an appointment at a doctors or restaurant only to find out there is an additional 30 mins added coz the doctor has not arrived yet or the table is still in use.

There was a time when I prided myself on my patience, always letting the lady behind me step in front coz her kids were making a fuss or waiting an additional 10 min coz the bus I wanted to get on was so full that it just wasn’t worth it. So after all these years what broke me?? I wondered the same thing and here is what I think:-

  • Do you really respect someone if you make them wait 30 min, would you do that to your parents or your boss?
  • Has time stopped becoming money, so it’s ok to make someone wait the whole day for a service call that was only going to happen in the evening anyway?
  • When you arrive late, how can you be on the moral high ground when someone else does it to you?
  • Would you forgive me if I made you wait 1 hour then offered a cheeky smile and an insincere apology, if not then why is it offered in the first place?

If like me you know the answer to the above then you also know we can’t be hypocrites with time, treating ours as the most precious and others as something they will attribute to just “this is how we do things”.

The part that irks me the most is when professionals do this. It’s one thing when a friend or family member is late and blames it on UIST but for a client to hear this excuse from a vendor or a colleague to hear it from another is unacceptable. The reason is because here is how the cycle propagates, the first time A is late by 5 min so next time B is late by 10 min to teach him a lesson. Next thing you know A takes it as acceptance that it’s OK to not be on time with B coz we both don’t stick to our timeslots and being who we are we don’t raise the matter with each other to avoid hurting each other’s feelings and as a result this becomes the norm.

The problem is this is a symptom of a much larger issue where we take others around us for granted. Be it their time, money, space, opinion etc. The common way we define this is “Chalta Hai” an Indian version of “Sh** happens”. The other day a guy cut the queue to the counter while I went to watch a movie and everybody just stood and watched until I finally went and told him there was a queue at which point he obliged this is just one example of how one person took another space for granted.

After all the time we spend stuck in traffic , waiting at airports and what not , from one human to another at least lets gift each other the courtesy of time and together we might actually bring about a change that make us more reliable and respectful as a society. I simple thing I learned while growing up.

If you don’t value your word no one else will either. So the next time you say “Just give me 5min I am on my way”. Take a moment to think how much time it will actually take and say that instead.