August 9, 2007 at 7:46 am
I have a query that performs multiple FTS on SQl 2005 and I would like to use indexed view to optimize it. Currently I have a view that calls another view like that:
create view My_view1 as
select col1,col2,col3, case(when flag = 1 then 'NO', when flag = 2 then 'Y' else 'good' end) as action from my_table1
create view My_view2 as
select col1,col2,col3, case(when overdue= 1 then (select ID1 from My_view1 mv where col1 =t1.col1 , col2 =t1.col2 ,col3 =t1.col3) when overdue= 2 then 2 else 'good' end) as alias from my_table1 t1
This is a very simplified logic but every time I call My_view1 from another view there is a FTS. (all good indexes and manually update statistics)
I tried to create an indexed view to improve the performance but because of the case statement I cannot index it. Tried to create a second view that select * from My_view1 but it did not work... By removing the case statement just for test, I saw that the FTS disapeared if I use indexed view in this scenario.
What can I do to be able to use the indexed view?
I'm also testing if I could use a function.
Thanks a lot, mj
August 10, 2007 at 3:51 am
Why not create a table 'YESNO':
The the first query can change the case into a join becoming something like:
Select a.col1,b.col2,c.col3, yn.text from bla as a join YESNO as yn on a.flag = yn.value
On this result you can create a indexed view.
The 'good' will be added in the end.
August 10, 2007 at 10:42 am
This is what I have tried but I still cannot create unique index on the view because there are repetative values and I cannot use distict.
So still doesn't work for me.
Thanks a lot, mj
August 10, 2007 at 10:44 am
Can you post DDL, actual data of the tables and required output from the view. That'll be the quickest way for us to provide an answer for ya.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply