why use inline functions to increase the power of indexed views

Posted on 2003-03-31
Medium Priority
Last Modified: 2008-03-04
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
FROM SalesDB.dbo.vw_QuarterlySales
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?
Question by:Wouter Boevink
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 12

Expert Comment

ID: 8243500
just a quess. at least i spare some network traffic when requesting.

CREATE FUNCTION dbo.stringf ( @userId int)  
     from  sysString s
     where a=1 and s.lcid= dbo.userLcId(@userId)

create view dbo.stringv
from  sysString s
where a=1

select * from dbo.stringv where lcid=dbo.userLcId(-1)
select * from dbo.stringf(-1)

LVL 12

Author Comment

by:Wouter Boevink
ID: 8243868
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.
LVL 12

Expert Comment

ID: 8244363
"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) )
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)
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

LVL 12

Author Comment

by:Wouter Boevink
ID: 8244732
I agree UDF's increase power on views. But the question was why does it increase power particulary on INDEXED views.
LVL 23

Accepted Solution

adathelad earned 1500 total points
ID: 8253388

I may be barking up the wrong tree, but I spose it's better to speak up rather than say nothing! Also, please not that I have not actually tested this so I'm speaking purely on my theory.

From what I understand, there are limitations with what you can do in indexed views (e.g. you cannot use AVG()).
Can these restrictions be effectively lifted by using inline functions from within the view?

e.g. inside a view:

SELECT CustomerId, dbo.fxn_GetAverageSpend(CustomerId)
FROM Transactions

So here, a UDF would do the work of finding the average amount spent by a customer.

Also, if a CREATE VIEW statement cannot used derived tables, you could use an inline UDF to return a table of results which are then queried by the view.

Please point out if I am wrong or have missed the point!

Expert Comment

ID: 9275418
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 
Post your closing recommendations!  No comment means you don't care.
LVL 12

Expert Comment

ID: 11183876
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.

EE Cleanup Volunteer

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question