March 8, 2011 at 9:14 am
Hello fellas, I have a very huge table on which I am trying to create a view. I'm tryng to write it this way:
select A.*, B.Col8
from
(select Col1, Col2, Col3, Col4, Col5, Col6, max(Col7) as Col7
from T1
group by Col1, Col2, Col3, Col4, Col5, Col6) A
inner join (select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8
from T1) B
on A.Col1= B.Col1
and A.Col2 = B.Col2
and A.Col3 = B.Col3
and A.Col4 = B.Col4
and A.Col5 = B.Col5
and A.Col6 = B.Col6
and A.Col7 = B.Col7
)
Its taking too much time. Is it possible to write it a better way ?
March 8, 2011 at 9:21 am
An index on Col1, Col2, Col3, Col4, Col5, Col6, Col7 would help.
The following syntax may be more efficient:
;WITH MaxCol
AS
(
SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8
,MAX(Col7) OVER (PARTITION BY Col1, Col2, Col3, Col4, Col5, Col6) AS MaxCol7
FROM T1
)
SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8
FROM MaxCol
WHERE Col7 = MaxCol7
March 8, 2011 at 12:10 pm
Thanks Ken. Its really nice, however can we write a CTE inside a function ?
March 8, 2011 at 1:33 pm
Sorry for this question. I have written the CTEs inside the function. Thanks ken, that was of great help.
March 9, 2011 at 12:12 pm
Ken, I've put this logic into a function along with some selective features so that indexes can be used and replaced this funciton frfom views used in stored procedures. All looks fine. However is there any thumb rule or recommendations of which to use [views or functions] inside a stored procedure which is used with very high frequency with different set of parameters [Though I think its better to use functions].
March 14, 2011 at 9:08 am
Scalar functions should be avoided. I'm not aware of any general performance difference between views and table valued functions (unless the function is multi-statement) but there easily could be in different situations. Do your own performance testing to see which works better in your situation. And don't forget to test just not under the current amount of data but what you'll want to scale up to in order to make sure you don't have issues later on.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply