Performance Related issue in sql server 2008 r2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Performance Related issue in sql server 2008 r2

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-07-23 : 07:28:26

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-23 : 08:45:05

Indexes may not be of much use in this instance because of the presence of WHERE conditions such as

when col1name like '%new%' then "newvalue"

. If the query must find col1names that have the string "new" embedded somewhere within, then it has no choice but to look through every row that otherwise qualifies.

If your requirement was to find only those that begin with the word new (which means your where ckayse would be when col1name like 'new%' then "newvalue") that can use an index. Is it possible that your business needs can allow restricting the query to such cases?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources