SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Performance Related issue in sql server 2008 r2		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kond.mohan
Posting Yak Master

India
154 Posts
Posted - 07/23/2012 :  07:28:26  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 07/23/2012 :  08:45:05  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000