where clause

Question

where clause

ranuganti

Ten Centuries

Points: 1092
More actions
November 6, 2011 at 7:29 am

#251186

Hi,
In a stored procedure where clause i had to use 'or' condition multiple time on a single column. so, my query is running for more than 4 min.
here is the example
where
(a.column1 =@parmeter) or (a.column1 = right(@parmeter,22) or @parmeter =right(a.column1,22)
can any one help me with a better example.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

JonFox SSCrazy Points: 2157 More actions · Answer 1

ranuganti (11/6/2011)
where
(a.column1 =@parmeter) or (a.column1 = right(@parmeter,22) or @parmeter =right(a.column1,22)

Without seeing the whole query and knowing something about the underlying table structure and data, it's hard to say for sure. But, based on what you've provided, my guess is that it has to do with the last part of your WHERE clause. Note that the first two times you are comparing column1 directly against a constant; the third time, you compare the rightmost 22 characters of column1 against the constant. That last bit isn't SARGable; SQL Server will need to look at the value of column1 for every single row in your table (or index, if you have one that covers your query) and extract the rightmost 22 characters. Even if you have a good, highly selective index on column1 it won't help, it has to look at every single row. If you look at your actual execution plan, I expect you'll see a SCAN operator there.

So, if you have a large number of rows in your table, this could be the source of your problems. Or at least, a contributing factor.

ranuganti Ten Centuries Points: 1092 More actions · Answer 2

here you go with the full stored procedure. And yes my table is very large it contains 64 million records.

can you please help me with this.

Thanks.

JonFox SSCrazy Points: 2157 More actions · Answer 3

Well...the first thing I notice is that you're actually selecting from vw_PackageInfo; based on the naming convention I assume that this is a view, rather than a table. When SQL Server generates the execution plan it "expands" any underlying views to make one big query. So, I'm still missing key info without the view definition. However...

In addition to the problem I pointed out with your first post, now seeing the full query I noticed this:

((@ZipCode is null) or (@ZipCode = DB.ZipCode))

and (((@dtStart is null) or (@dtEnd = null)) or (DB.Receiveddate between @dtStart and @dtEnd))

This particular "optional parameters" design pattern also tends to be a performance killer. At compile time, the Optimizer has to generate an optimal query plan without knowing the actual values of the parameters that will be passed to it. So, it has to "play it safe" and create a plan that will always get the right results. Looking at this bit of code, we can tell that in a best case scenario, @dtStart and @dtEnd will both have actual values set, the range between the two dates will be small and therefore highly selective, and so (assuming the underlying table has a usable index on ReceivedDate) we can get what we need with a relatively "cheap" index seek to retrieve a small subset of rows.

Now, imagine the worst case scenario: @dtStart and @dtEnd are both passed in as NULL. This means that, even if we have an index on ReceivedDate, we can't seek on it; we have to look at the entire table (assuming that there aren't any other significantly limiting statements in the search predicate) and return all the rows. When the Optimizer looks at your query the first time to generate the execution plan, it knows this, and since it has no idea what future executions may set @dtStart and @dtEnd to, it plays it safe and generates an execution plan that will scan the whole table. Even if you then execute your stored procedure with a highly selective set of dates, it will still execute the previously compiled plan and guess what...table scan.

Actually, there are a lot of generalities in the above explanation, and it doesn't hold true in all situations, with all versions of SQL Server, etc. But, that's the gist of the issue. There are a number of ways to go about solving this problem, and I've seen some very good articles that go into it in depth; I'll round up a few and post them in a moment.

For now, though, try testing with a new version of your SP that removes the "OR..IS NULL" statements for @ZipCode, @dtStart, and @dtEnd. Also, remove the comparison on RIGHT(column1,22) that I mentioned in my first post. Then, execute the new version of the SP with explicit values set for @ZipCode, @dtStart and @dtEnd. Of course, you won't get the correct results, but that's ok...this is just for testing and demonstration purposes. My guess is that your SP will run much faster with those changes. Now, generate the actual execution plan for both versions of the SP and compare them. Assuming (big assumption) that you have effective indexing on the underlying tables, you should see a big difference.

I'll round up some articles with good explanations for various ways of solving this...

JonFox SSCrazy Points: 2157 More actions · Answer 4

OK, I should have just directed you to this blog post by Gail Shaw to begin with, probably a lot easier to understand than my ramblings above and has good examples with the graphical execution plans included: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries

Erland Sommerskog has a very in-depth writeup that covers the options available for solving this problem in SQL 2008 SP1 CU5 and later: http://www.sommarskog.se/dyn-search-2008.html

If you're using a version of SQL Server that is older than that, read the other version of his article here: http://www.sommarskog.se/dyn-search-2005.html

The short of it: although there are a few different options depending on the version of SQL Server you're using (for example on SQL 2008 SP1 CU5 and later you can probably get away with using OPTION (RECOMPILE) ), using dynamic SQL (via parameterized query strings executed with sp_executesql) is probably your best bet for ensuring that you always get an optimal query plan when you need to allow for optional parameters.

Edit: Fixed the link to Gail's post and clarified the required SP and CU needed to use OPTION (RECOMPILE) successfully.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

JonFox (11/6/2011)
(for example on 2008 SP1 you can probably get away with using OPTION (RECOMPILE) )

SP1 no, you need at least SP1 CU5 (or SP2) to be able to do that.

btw, the correct link to my blog post on catch-all queries is: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JonFox SSCrazy Points: 2157 More actions · Answer 6

Thanks for the catch Gail, not sure what happened with that URL tag! :unsure: (Although It might have had something to do with needing another cup of coffee...)