Performance Related issue in Sql server 2008 r2

Question

Dear all
i have created one Stored procedure. inside that stored procedure one of Select Statement is having Multiple Case logics(more than 15)

example

select
a.nemse.
b.no
c.loc,
..............,
case when col1name like '%new%' then "newvalue"
when col1name2 is not null then "newvalue2"
when col1name3 is not null then "newvalue5"
when col1name4 like '%values%' then "newvalue4"
when col1name like '%new%' then "newvalue"
when col1name2 is not null then "newvalue2"
when col1name3 is not null then "newvalue5"
when col1name4 like '%values%' then "newvalue4" else end "CASECOLUMN"

from a inner join b on a.id =b.id
.............

where A.FLG='N'
and b.create_flg='y'
and case when col1name like '%new%' then "newvalue"
when col1name2 is not null then "newvalue2"
when col1name3 is not null then "newvalue5"
when col1name4 like '%values%' then "newvalue4"
when col1name like '%new%' then "newvalue"
when col1name2 is not null then "newvalue2"
when col1name3 is not null then "newvalue5"
when col1name4 like '%values%' then "newvalue4" else
end IS NOT NULL

HERE i have checked query estimated Execution plan it is not Recommands any indexes on tables .and my query Returns 80lakhs Records but query Execution time is taken more than 30 min

case logic is included in Where clause with not null

i want to execute the query in MIN time

pls provide the Suggestions.

Answer 1

I believe the main issue comes from the Like '%new%' cause here SQL Server cannot use any index to fix it. I think you may consider FULL TEXT SEARCH it'll give you way better performance...

---

Wagdy Ishac www.sqldair.com

Answer 2

You have two choices, optimise your queries (and index structure) or optimise your hardware for sequential scans. In a DW you will often fail in this endeavour to optimise queries as large table or partition scans are typically a business requirement. Many organisations are now moving towards the later hardware solution.

Column like '%value%' is always going to be problematic in complex queries on large fact tables. Although you could try Full Text Search you will then need to maintain your Full Text Catalog which could become a performance hit in itself on large fact tables.

Answer 3

 I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic.  I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient.

try to convert your wherer clause with boolean operator. which must resolve your performance issue.

keep post on this issue.

Thanks,

Zaim Raza.

Answer 4

I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic.  I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient.

try to convert your wherer clause with boolean operator. which must resolve your performance issue.

keep post on this issue.

Thanks,

Zaim Raza.