why use inline functions to increase the power of indexed views
You can also use inline functions to increase the power of indexed views. An
indexed view cannot use parameters in the WHERE clause search conditions to
tailor a stored result set to specific users. However, you can define an indexed
view that stores the complete set of data that matches the view, and then define
an inline function over the indexed view, which contains parameterized search
conditions that allow users to tailor their results. If the view definition is
complex, most of the work that is performed to build a result set involves operations
such as building aggregates or joining several tables when the clustered index
is created on the view. If you then create an inline function that references
the view, the function can apply the user's parameterized filters to pull specific
rows from the result set that was built by the CREATE INDEX statement. The complex
aggregations and joins are performed once, at CREATE INDEX time, and all subsequent
queries that reference the inline function filter rows from the simplified, stored
result set. For example:
Define a view named vw_QuarterlySales that aggregates all sales data into a
result set, which reports summarized sales data by quarter for all stores.
Create a clustered index on vw_QuarterlySales to materialize a result set that contains the summarized data.
Create an inline function to filer the summarized data:
CREATE FUNCTION fn_QuarterlySalesByStore
(
@StoreID int
)
RETURNS table
AS
RETURN (
SELECT *
FROM SalesDB.dbo.vw_QuarterlySa
WHERE StoreID = @StoreID
)
Users can then get the data for their specific store by selecting from the inline function:
SELECT * FROM fn_QuarterlySalesByStore( 14432 )
Most of the work that is needed to satisfy the queries that are issued in step 4
is to aggregate the sales data by quarter. This work is performed once in step 2.
Each individual SELECT statement in step 4 uses the fn_QuarterlySalesByStore
function to filter out the aggregated data that is specific to the user's store.
Can somebody explain why the use of inline functions increase the power of indexed views?
Why is the function better than using
SELECT * FROM fn_QuarterlySalesByStore where OrderId = 14432
Or creating a stored procedure?
indexed view cannot use parameters in the WHERE clause search conditions to
tailor a stored result set to specific users. However, you can define an indexed
view that stores the complete set of data that matches the view, and then define
an inline function over the indexed view, which contains parameterized search
conditions that allow users to tailor their results. If the view definition is
complex, most of the work that is performed to build a result set involves operations
such as building aggregates or joining several tables when the clustered index
is created on the view. If you then create an inline function that references
the view, the function can apply the user's parameterized filters to pull specific
rows from the result set that was built by the CREATE INDEX statement. The complex
aggregations and joins are performed once, at CREATE INDEX time, and all subsequent
queries that reference the inline function filter rows from the simplified, stored
result set. For example:
Define a view named vw_QuarterlySales that aggregates all sales data into a
result set, which reports summarized sales data by quarter for all stores.
Create a clustered index on vw_QuarterlySales to materialize a result set that contains the summarized data.
Create an inline function to filer the summarized data:
CREATE FUNCTION fn_QuarterlySalesByStore
(
@StoreID int
)
RETURNS table
AS
RETURN (
SELECT *
FROM SalesDB.dbo.vw_QuarterlySa
WHERE StoreID = @StoreID
)
Users can then get the data for their specific store by selecting from the inline function:
SELECT * FROM fn_QuarterlySalesByStore( 14432 )
Most of the work that is needed to satisfy the queries that are issued in step 4
is to aggregate the sales data by quarter. This work is performed once in step 2.
Each individual SELECT statement in step 4 uses the fn_QuarterlySalesByStore
function to filter out the aggregated data that is specific to the user's store.
Can somebody explain why the use of inline functions increase the power of indexed views?
Why is the function better than using
SELECT * FROM fn_QuarterlySalesByStore where OrderId = 14432
Or creating a stored procedure?
ASKER
I don't see any real performance improvement or power increasement of indexed views. I don't get why a UDF can enhance the power of an INDEXED view.
You've a point stating it's decreasing network traqffic so performance is increased but it's just a marginal improvement. When using a function the execution plan of the query is cached which improves performance but the same can be accomplished by using a stored procedure.
In all kinds of articles and powerpoint presentation I see the statement that the power of indexed views is increeased by using udf's but nowhere an explanation.
You've a point stating it's decreasing network traqffic so performance is increased but it's just a marginal improvement. When using a function the execution plan of the query is cached which improves performance but the same can be accomplished by using a stored procedure.
In all kinds of articles and powerpoint presentation I see the statement that the power of indexed views is increeased by using udf's but nowhere an explanation.
"but the same can be accomplished by using a stored procedure" ...you can't do select * from myProc
"the power of indexed views is increeased by using udf's" ... i think it doesn't mean only inline
for example tree structures traversing or parsing the string cannot be done in view, but you can do it with udf returning table
CREATE function dbo.strSplit ( @string nvarchar( 4000), @splitter char( 1) )
returns @res table ( id int primary key, value nvarchar( 4000) )
as
begin
declare @start int, @word nvarchar(4000), @charindex int
set @start=1
set @charindex= charindex( @splitter, @string, @start)
while (@charindex <> 0)begin
set @word= substring( @string, @start, @charindex - @start)
set @start= @charindex +1
set @charindex= charindex( @splitter, @string, @start)
insert into @res values ( @start, @word)
end
return
end
"the power of indexed views is increeased by using udf's" ... i think it doesn't mean only inline
for example tree structures traversing or parsing the string cannot be done in view, but you can do it with udf returning table
CREATE function dbo.strSplit ( @string nvarchar( 4000), @splitter char( 1) )
returns @res table ( id int primary key, value nvarchar( 4000) )
as
begin
declare @start int, @word nvarchar(4000), @charindex int
set @start=1
set @charindex= charindex( @splitter, @string, @start)
while (@charindex <> 0)begin
set @word= substring( @string, @start, @charindex - @start)
set @start= @charindex +1
set @charindex= charindex( @splitter, @string, @start)
insert into @res values ( @start, @word)
end
return
end
ASKER
I agree UDF's increase power on views. But the question was why does it increase power particulary on INDEXED views.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
wboevink:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.
I will leave the following recommendation for this question in the Cleanup topic area:
Accept: adathelad http:#8253388
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
monosodiumg
EE Cleanup Volunteer
I will leave the following recommendation for this question in the Cleanup topic area:
Accept: adathelad http:#8253388
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
monosodiumg
EE Cleanup Volunteer
function:
CREATE FUNCTION dbo.stringf ( @userId int)
RETURNS table
AS
return(
select
s.id,
s.val
from sysString s
where a=1 and s.lcid= dbo.userLcId(@userId)
)
view:
create view dbo.stringv
AS
select
s.id,
s.val,
s.lcid
from sysString s
where a=1
select * from dbo.stringv where lcid=dbo.userLcId(-1)
select * from dbo.stringf(-1)