by Hi All,
I need a clarification about the performance issue of a
view and a stored procedure,say for eg this the view that
ive written...WHICH BY EXPLANATION IS NOTHING BUT A SELECT
STATEMENT FROM AROUND 7-8 TABLES IN SQL SERVER..
My point of argument is ,since this following set of
statements involves only select querries views would be
more optimized than stored procedure for the same selects
as below like
CREATE procedure VW_ChildCheckList
AS
etc..etc
Can anyone breif me about the execute plan for this query
and also the detailing of performance of VIEWS and stored
procedures in different situation,and the precise
information about IS A VIEW MORE OPTIMIZED FOR SELECT
STATEMENTS???
The query is
CREATE view VW_ChildCheckList
AS
select getdate() as SYSDATE, A.*,B.* From
(select A.*,B.* from
(select A.*,B.* from
(select a.*,b.* from
( select c.Child_id,convert(numeric(30,0),timestamp + 0)
as
timeflag,c.firstname,c.lastname,c.middlename,c.aka,c.DOB,c.
Sex,c.Child_SSN,c.Court_Date,
c.Ambulatory,c.Ambul_Date,c.Prn_meds,c.Physical,c.Dental,c.
Ltr602sent,c.county_cnt,c.Agreement,
c.Agreemt_fh,c.MED_CONSNT,c.md_cnst_fh,c.id_emrgncy,c.id_em
rg_fh,c.n_s_plan,
c.n_s_pln_fh,c.pers_right,c.prs_rgt_fh,c.prs_rgt_bk,c.prope
rty,c.proprty_fh,c.proprty_bk,
c.md_exam_fh,c.md_exam_bk,c.immunizatn,c.immuniz_fh,c.tb_te
st,c.tb_test_fh,c.dental_fh,
c.store_meds,c.stor_md_fh,c.stor_md_bk,c.intak_stdy,c.bios_
sent,c.court_rpt,c.excpt_24hr,c.dschrg_rec,
dschrg_lst,c.staf_visit,c.billed,c.c_signs,c.ar_signs,c.sw_
7days,c.sw_30days,c.sw_notouch,c.safe_cash,
c.afdc,c.rev_c_chkl,c.rev_fh_ckl,c.over18,c.adopt,c.clothin
g,c.asmt_rpt,c.prn_med_fh,
c.agency_id,c.dsch_pol_o,c.dsch_pol_h,c.provider_id,c.cur_f
phome_id,c.cur_gh_id,
c.cur_sw_id,c.cur_guardian_id,
c.admit,c.discharge,c.move_in,c.move_out
from child c
)A LEFT OUTER JOIN
(SELECT gh_id,ffa_id as gh_ffa_id, gh_name from GH)
B ON A.cur_gh_id=B.gh_id
) A left outer join
(SELECT b.fphome_id ,a.ffa_id as fp_ffa_id, a.ffa_office
from FFA a,fphome b
where a.ffa_id=b.ffa_id)
B on A.cur_fphome_id=B.fphome_id)
A LEFT OUTER JOIN
(select b.*_id ,c.fphome_id as fphomeID,d.lastname as
FP_Lastname ,d.firstname as FP_Firstname ,b.firstname as
Spouse
from *s b,*s d,fp c where b.*_id =
c.*_id and b.gender='M' AND d.GENDER='F' and
b.lastname=d.lastname)
B ON A.fphome_id=B.fphomeID)
A LEFT OUTER JOIN
(SELECT e.employee_id, a.*_id as *ID, a.LastName
AS SwLastName, a.FirstName AS SwFirstName
FROM *S a ,employee e where a.*_id=e.*_id)
B ON B.employee_id=A.cur_sw_id
Thanks and Regards
Suresh Gladstone