Should I use a T-SQL function, view, or stored proc? -
i've got question reusing table data view won't work in scenario have parameter needs passed in. part of system requires travellerid sent procedure , list of arrangers returned specific traveller. there around 7 business rules used determine arrangers can returned , mutually exclusive, in order accommodate these optional rules have used series of unions inside derived query. working well, , performance seems across large database, need reuse these rules (unions) in 4 other parts of system.
i tried create view these unions didn't work due differing logic in each union , different parameter requirements, thinking maybe function solve issue? if created function took @travellerid param , returned list of arrangerid based on business rules, ideal/fast solution? using union , distinct in outer query proved faster using union's uniqueness of data.
current procedure business rules below (sql server 2008):
create procedure [dbo].[getarrangersfortraveller] @travellerid int declare @costcentreid int declare @departmentid int -- shorthand traveller costcentre , department use in queries below set @costcentreid = (select costcentreid traveller id = @travellerid) set @departmentid = (select departmentid traveller id = @travellerid) select distinct t.id, t.firstname, t.lastname, ti.name title, dv.preferred traveller t inner join title ti on t.titleid = ti.id inner join ( -- preferred arrangers linked department groups select dg.arrangerid id departmentgroup dg inner join department_departmentgroup ddg on (dg.id = ddg.departmentgroupid , ddg.departmentid = @departmentid) union -- preferred arrangers linked cost centre groups select cg.arrangerid id costcentregroup cg inner join costcentre_costcentregroup ccg on (cg.id = ccg.costcentregroupid , ccg.costcentreid = @costcentreid) union -- if cost centre group has linked department , department matches -- travel arrangers department return these travel arrangers select t3.id costcentregroup cg1 inner join costcentre_costcentregroup ccg1 on (cg1.id = ccg1.costcentregroupid , ccg1.costcentreid = @costcentreid) inner join traveller t3 on t3.departmentid = cg1.departmentid t3.accesslevelid > 1 union -- direct linked travel arrangers select t1.travelarrangerid id travelarranger_traveller t1 t1.travellerid = @travellerid union -- cost centre linked arrangers select tc.travelarrangerid id travelarranger_costcentre tc tc.costcentreid = @costcentreid union -- department linked arrangers select td.travelarrangerid travelarranger_department td td.departmentid = @departmentid union -- company flagged arrangers select t2.id traveller t2 inner join company c on t2.companyid = c.id t2.accesslevelid > 1 , ((c.allowtravelarrangerdepartmentaccess = 1 , t2.departmentid = @departmentid) or (c.allowtravelarrangercostcentreaccess = 1 , t2.costcentreid = @costcentreid)) ) dv on dv.id = t.id t.accesslevelid > 1 -- arranger or manager , t.isenabled = 1 order dv.preferred desc, t.lastname, t.firstname;
i tried create view these unions didn't work due differing logic in each union , different parameter requirements, thinking maybe function solve issue? if created function took param @travellerid, , returned list of arrangerid's based on business rules, ideal/fast solution?
you're thinking procedural/oo programming, sql set based.
function work, ensure index not used when use function decision criteria/etc. non-materialized view better; in sql server there's option use indexed view (aka materialized view) notoriously constrained. goes against modular programming concepts, sql works better less try modularize , use need to.
i re-wrote query, noticed dv.preferred column referenced in outer query isn't present in inner one. being dv conglomerate of various tables & logic, id value being returned isn't of real value outside inner query because you'd need know table value came from. said, here is:
select t.id, t.firstname, t.lastname, ti.name title /*, dv.preferred */ traveller t join title ti on t.titleid = ti.id (exists(select null -- preferred arrangers linked department groups departmentgroup dg join department_departmentgroup ddg on ddg.departmentgroupid = dg.id , ddg.departmentid = @departmentid dg.arrangerid = t.id) or exists(select null -- preferred arrangers linked cost centre groups costcentregroup cg join costcentre_costcentregroup ccg on ccg.costcentregroupid = cg.id , ccg.costcentreid = @costcentreid cg.arrangerid = t.id) or exists(select null -- if cost centre group has linked department , department matches travel arrangers department return these travel arrangers costcentregroup cg1 join costcentre_costcentregroup ccg1 on ccg1.costcentregroupid = cg1.id , ccg1.costcentreid = @costcentreid join traveller t3 on t3.departmentid = cg1.departmentid , t3.accesslevelid > 1 t3.id = t.id) or exists(select null -- direct linked travel arrangers travelarranger_traveller t1 t1.travellerid = @travellerid , t1.travelarrangerid = t.id) or exists(select null -- cost centre linked arrangers travelarranger_costcentre tc tc.costcentreid = @costcentreid , tc.travelarrangerid = t.id) or exists(select null -- department linked arrangers travelarranger_department td td.departmentid = @departmentid , td.travelarrangerid = t.id) or exists(select null -- company flagged arrangers traveller t2 join company c on t2.companyid = c.id , t2.accesslevelid > 1 ( (c.allowtravelarrangerdepartmentaccess = 1 , t2.departmentid = @departmentid) or (c.allowtravelarrangercostcentreaccess = 1 , t2.costcentreid = @costcentreid)) , t2.id = t.id)) , t.accesslevelid > 1 -- arranger or manager , t.isenabled = 1 order /*dv.preferred desc,*/ t.lastname, t.firstname; using subquery (in, exists) alleviate duplicates issue comes using joins if there more 1 child record attached parent.
Comments
Post a Comment