How Can I optimize this query (OR inside AND validation)?

I'm having problems with this query:

SELECT col1, col2,col3,...,coln FROM MyTable
WHERE col1 = @value1
AND
(
ISNULL(col2,'c') = ISNULL(@value2,ISNULL(col2,'c'))
OR
ISNULL(col3,'c') = ISNULL(@value2,ISNULL(col3,'c'))
)
 AND coln = 'valueN'

I have to stop the execution, it's so slow. But editing:

SELECT col1, col2,col3,...,coln FROM MyTable
WHERE col1 = @value1
AND
(
ISNULL(col2,'c') = ISNULL(@value2,ISNULL(col2,'c'))
)
AND coln = 'valueN'

This query is faster. Can someone help me? How can I replace the or statement or replace the query but validating col1 and col2?. Thanks.

UPDATE:

Thank you very much guys. Really my query does not use '=' but use 'Like', sorry about that. However I use your suggestions to build my query and it works fine:

SELECT col1, col2,col3,...,coln FROM MyTable
WHERE col1 like '%' + @value1 + '%'
AND
(
 (@value2 IS NULL)
 OR
  (col2 IS NOT NULL AND col2 LIKE '%' + @value2 + '%')
 OR
 (col3 IS NOT NULL AND col3 LIKE '%' + @value2 + '%')
)
 AND coln = 'valueN'

I use this query with a page where I have many fields to filter a search and I need that a col2 textbox apply to col3 in database too, I want to mean, only one textbox for name1 and name2 in database.

Sorry about my wrong question and thanks for your suggestions.

Answers


ISNULL(col2,'c') = ISNULL('value2',ISNULL(col2,'c'))

is the same as

col2 = 'value2' or (col2 is null and 'value2' is null)

Replace the occurrences and you will most likely have a better performance.

Update

There's one fundamental difference between this solution and the one proposed by @onedaywhen: when the value provided in 'value2' (which I suppose is just a parameter assembled into a SQL string) is NULL, OP only wants to bring back only records where col2 is NULL. Take a closer look into OP's logic, you will see that there. OP's logic always filters: when the parameter is NULL, OP wants records where col2 is NULL.

@onedaywhen's solution brings every record when the parameter is NULL. Although this is a very common query, it's not what OP is looking for.


Need Your Help

How to get Left outer join in Linq?

c# .net linq linq-to-sql

I have two tables in my database that look like that:

Monitor directory for new files only

c linux monitoring solaris filesystems

I'd like to monitor a directory for new files from a C app. However, I'm not interested in modified files, only in new files. Currently I'm using readdir/stat for that purpose:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.