Monday, August 13, 2012

TSQL - Remove Views in SPs quickly


Yesterday I had a task in TSQL.  Task is to eliminate views from SQL procedures and use the tables instead (with the same logic of views) . i.e., SP contains combination of tables and views, and these  views also prepared with other tables and views. Now I need to write a single SP with complete logic (by considering nested views inside).


To make it easier, I followed this approach with the help of my friend.
Sample:

create procedure SampleForYouGuys()
Begin
Select * from Table1 T
Left Join View1 vw1 on T.ID = Vw1.ID
End

CREATE VIEW dbo.View1
AS
Select * from View2 vw2
Left Join View3 vw3 on vw3.ID = Vw2.ID

.
.
.

Create View dbo.View10
AS
Select ......



Solution in Phase-I
create procedure SampleForYouGuys()
Begin
Select * from Table1 T
Left Join
(
    Select * from
    (
         Select * from Table2,table3
    )
     vw2
    Left Join
    (
         Select * from Table3,table4,...
    )
     vw3 on vw3.ID = Vw2.ID
)
vw1 on T.ID = Vw1.ID
End

Like this first we can re-write the SP and check the results (Obviously it would same J)

Due to this approach, existing UI/application wont be changed and it will work as expected and business wont’ be affected.

Phase-II solution, analyze the query (plz dont scold me :-)) and then proceed. Because we need to consider too many things while we optimize these sory of queries right.




No comments: