Good day to all, I have a question which do you suggest is much better a view or a function? I have a transaction data around 12M of rows already. Left joining it around 6 table to get all the necessary information. The other two tables have 5M-6M of datas. I put it on a view naming it as vwCompanyLedger.
view: vwCompanyLedger
select t1.col1
,t2.col2
,t3.col2
,t4.col2
,t5.col2
,t6.col2
,t.*
from tbltransactions as t
left join tbl1 as t1 on t1.id = t.id
left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id
left join tbl4 as t4 on t4.id = t.id
left join tbl5 as t5 on t5.id = t.id
left join tbl6 as t6 on t6.id = t.id
Regarding my function here's my DDL:
CREATE FUNCTION dbo.fnCompanyLedger(
@From as datetime
,@To as datetime
,@CompanyID as numeric(18,0)
) RETURNS @List TABLE
(
Id int identity(1,1),
col1 varchar(200),
col2 varchar(200),
col3 varchar(200),
col4 varchar(200),
col5 varchar(200),
col6 varchar(200),
trans7 varchar(200),
trans8 varchar(200),
trans9 varchar(200),
trans10 varchar(200),
trans11 varchar(200),
trans12 varchar(200),
trans13 varchar(200),
trans14 varchar(200),
transID as numeric(18),
CompanyID as numeric(18)
)
BEGIN
insert into @List (col1, col2, col3, col4, col5, col16,
trans7, trans8, trans9, trans10,
trans11, trans12, trans13, trans14, transID, CompanyID)
select t1.col1
,t2.col2
,t3.col2
,t4.col2
,t5.col2
,t6.col2
,t.trans7, t.trans8, t.trans9, t.trans10,
,t.trans11, t.trans12, t.trans13, t.trans14, transID, t.CompanyID
from tbltransactions as t
left join tbl1 as t1 on t1.id = t.id
left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id
left join tbl4 as t4 on t4.id = t.id
left join tbl5 as t5 on t5.id = t.id
left join tbl6 as t6 on t6.id = t.id
WHERE dtFrom >= @From and dtTo <= @To
and CompanyID = @CompanyID
RETURN
END
from that function and view, most of the report acquisition of my client involves in using that view or function. Say for instance joining it again in two(2) tables around 2-3M of rows each.
Example 2: Using the view
SELECT l.*, p.paymentDate, o.orderDate
FROM vwCompanyLedger as l
left join tblpayments as p on p.id = l.col1
left join tblorders as o on p.id = l.col2
WHERE l.dtFrom >= @From and l.dtTo <= @To
and l.CompanyID = @CompanyID
Example 2: Using the function
SELECT l.*, p.paymentDate, o.orderDate
FROM dbo.fnCompanyLedger(@From, @To, @CompanyID) as l
left join tblpayments as p on p.id = l.col1
left join tblorders as o on p.id = l.col2
Which is better and much faster? example 1 or 2?
Thanks in advance.
Want Philippines to become 1st World COuntry? Go for World War 3...
Which do you suggest?
Posted - 11/08/2007 : 03:49:27
