November 2, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/paramterizingviews.asp>http://www.sqlservercentral.com/columnists/awarren/paramterizingviews.asp
November 30, 2003 at 8:09 pm
Good Article. We use the same method here. Only difference is we use @@spid instead of UserName() because all connections use the same user name. (because our middle tier connects to the database server.)
Cheers,
Preethiviraj Kulasingham
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
December 1, 2003 at 8:30 am
I also use this technique regularly with great success.
Steven
December 1, 2003 at 4:36 pm
Andy,
Couldn't you use a function that returns a record-set instead? I mean, for SQL2K people, at least. I found that this works beautifully as a "parameterized" view.
IE:
create function TestView (@Param int)
returns table
as
return (select @Param as Param, (@Param * 10) as Param10)
go
select * from TestView(1)
select * from TestView(2)
Signature is NULL
December 1, 2003 at 4:50 pm
I dont see why not. I think depends on how you want to use it. For what I needed - reporting - I was trying to hide the parameter altogether, just have them use the view. Must be Im tired, cant think of a way to achieve exactly that using the function? Must be a way!
Andy
December 1, 2003 at 5:36 pm
Of course there's a way! 🙂 I liked what you were saying about driving a view by what user is logged in, as either table driven or log-in driven.
One method I've used in the past was actually create a separate UDF for each user, and then they are called using a full name(IE: select * from User.UDFName)
The main reason for doing it this way was that we were actually linking to different tables depending on who the user was. But, it was pretty easy to dynamically script them out with a simple loop of some sort (given that they were self-similar functions)
The limitations of DML statements in a function make the table based version a little harder, but it's still possible as long as you built the table ahead of time.
Or the function could find it on the fly, but it seems this may be hard to manage.
--------------------------------
create function GetUser ()
returns table
as
return
(
select rtrim(loginame) loginname, @@Spid spid
from master.dbo.sysprocesses (nolock)
where spid = @@spid
)
select * from GetUser()
Signature is NULL
December 1, 2003 at 5:51 pm
I'll try to experiment a little. The idea of changing the underlying object is interesting. I guess you can get part of that by having the user own the view. I still under-use functions, not sure why.
As much as I like it Im having to discard most of it, we're starting to use a reporting system where I have no control over the connection process. Possibly I could try to profile and manage it as I saw the events fire, but not sure that's worth trying?
Andy
December 1, 2003 at 6:26 pm
Yeah, it seems to me that there should be SOME method of determining who the actual user is. Not having one method or another would seem to cause a lot of difficulties. Tell them they can't have their cake and eat it, too (or some such appropriate truism :).
I've found I'm sometimes using functions where before I'd use views be default. Being able to explicitely parametarize them is a key feature. But above and beyond that they can perform quite well.
BTW, really enjoyed meeting y'all at PASS. Excellent to hear about the newsletter; It should be quite popular. PASS was in my backyard this year, but I'll be making the trek to Florida for the next one.
Signature is NULL
December 1, 2003 at 6:31 pm
The timing of this is fascinating. I worked out a solution using functions late last week and learned a lot more than I really wanted to know.
In my case I wanted an existing view that did NOT use a parameter to work as it did before, but to work differently if I provided a parameter. Basically a default to the parameter.
That's pretty straightfoward with a table function. Effectively I defined the view as:
create view vw_x as select * from fn_x(default)
and then people can also call the function directly with other parameters. A quirky aspect of this is that the default was actually today's date, and GETDATE() cannot be used in a UDF. However, you can then create a separate view that returns one row containing today's date, and join that view to any table in the UDF to effectively use GETDATE(). But that's a tangent....
The UDF works very nicely as a way to parameterize this. You could either change the underlying object, or have new users simply use the function instead of the view -- syntactically and functionally they are awfully similar (when the UDF returns a table).
HOWEVER, and it is a huge HOWEVER... it does not always work well. I hesitate to say it does not work correctly but there are at least issues.
The UDF I wrote contained a complicated nested select, that is nested about 5 deep and uses maybe 6 or 8 tables. It has been thoroughly debugged and runs in about a minute on an idle system.
The nice thing about the UDF is that in specific circumstances it ran faster (since I could put the parameter into the inner selects).
The bad thing is that for no observable reason, sometimes it ran and never completed. In exactly the same code (but with different underlying data in the tables as transactions were processed) it would hang. By hang I mean it consumed CPU time in one three with zero I/O, and would run for hours. Looking at the statement being executed showed it in the "return" that passed back the table. It (as expected) showed it executing the select statement for about a minute, then hung on the "return" for hours.
Nothing was visible as being blocked, I finally idled the system entirely this evening and it still would not complete.
I changed back, replacing the function with the view (with exactly the same effective parameters) and it ran in a couple minutes in total, worked fine.
I have no idea where to go next. It runs sometimes, hangs others. I think SQL Server is broken, but it is conceivable that this is an optimization problem of some sort -- the tables are large enough it could have been working but taking forever.
Before someone asks about the query plan -- I can't get it to show me anythign useful in the query plan, it treats the UDF as a table it just scans. And the data is much too complex to try to provide a reproducible example to Microsoft (especially when it requires specific data contents to fail, not just the metadata). I've also had trouble getting accurate statistics from within UDF's.
At any rate.... I just wanted to say that the use of UDF provides a neat way to do parameterized views as an alternative to the above, but that at least in SQL2000 SP3 on our system, it is a bit flakey.
December 1, 2003 at 6:46 pm
Yeah, I know what you're talking about...I've seen problems like that as well.
My guess is it has to do with the way functions use memory. For example, if you try to insert to much data into an @ table variable the data will be dumped to tempdb. However, in the case of functions it doesn't appear to work this way. I don't have any hard evidence, but the fact that a function looks like a table/index scan in the execution plan seems indicative.
So, you're reservations are very appropriate. I always take MS's advice and "test, test, test" before using a function.
I've also found breaking functions up into smaller chunks will virtually always take care of performance problems. Done correctly it can also make your code more re-usable and "object oriented".
Fun fun...
Signature is NULL
December 2, 2003 at 1:09 am
It seems more logic to use a table-valued User Defined Function. This is what you would call a 'real' parameterized view, except for that it is technically not a view.
December 2, 2003 at 1:05 pm
I was curious enough to test this, and got some interesting results. Views beat functions when un-parameterized, but a parameterized function beat calling a view using a Where clause:
--47 CPU
select *
from TestViewAgr
where Group1 = 1
--31 CPU
select * from TestParamAgr(1)
use pubs
/*------------------------------------------------
Build test table and insert data
*/------------------------------------------------
if object_ID('TestView') is not null drop table testview
go
create table TestView (tvID int identity Not null,
Group1 int Not Null,
Bit1 bit Null,
Bit2 bit Null,
Bit3 bit Null,
Bit4 bit Null,
Primary Key (Group1, tvID))
go
while 1 = 1
begin
begin tran
Insert TestView Values (cast(@@identity as int)%10, NULL, 1, 0, NULL)
Insert TestView Values (cast(@@identity as int)%10, 0, NULL, 1, 0)
Insert TestView Values (cast(@@identity as int)%10, 1, 0, NULL, 1)
commit tran
if @@Identity >= 10000 Break
end
go
/*------------------------------------------------
create TestView
*/------------------------------------------------
create view TestViewAgr
as
select Group1,
sum(isnull(cast(Bit1 as int), -1)) as sumBit1,
sum(isnull(cast(Bit2 as int), -1)) as sumBit2,
sum(isnull(cast(Bit3 as int), -1)) as sumBit3,
sum(isnull(cast(Bit4 as int), -1)) as sumBit4,
count(isnull(cast(Bit1 as int), -1)) as countBit1,
count(isnull(cast(Bit2 as int), -1)) as countBit2,
count(isnull(cast(Bit3 as int), -1)) as countBit3,
count(isnull(cast(Bit4 as int), -1)) as countBit4
FromTestView (nolock)
group by Group1
/*------------------------------------------------
create Test Function -Non Parametarized
*/------------------------------------------------
create function TestFuncAgr ()
returns table
as
return (
select Group1,
sum(isnull(cast(Bit1 as int), -1)) as sumBit1,
sum(isnull(cast(Bit2 as int), -1)) as sumBit2,
sum(isnull(cast(Bit3 as int), -1)) as sumBit3,
sum(isnull(cast(Bit4 as int), -1)) as sumBit4,
count(isnull(cast(Bit1 as int), -1)) as countBit1,
count(isnull(cast(Bit2 as int), -1)) as countBit2,
count(isnull(cast(Bit3 as int), -1)) as countBit3,
count(isnull(cast(Bit4 as int), -1)) as countBit4
FromTestView (nolock)
group by Group1
)
/*------------------------------------------------
testing....
*/------------------------------------------------
--312 CPU
select * from TestViewAgr
--319 CPU
select * from TestFuncAgr()
/*------------------------------------------------
create Test Function with Parameter
*/------------------------------------------------
create function TestParamAgr (@Group int)
returns table
as
return (
select Group1,
sum(isnull(cast(Bit1 as int), -1)) as sumBit1,
sum(isnull(cast(Bit2 as int), -1)) as sumBit2,
sum(isnull(cast(Bit3 as int), -1)) as sumBit3,
sum(isnull(cast(Bit4 as int), -1)) as sumBit4,
count(isnull(cast(Bit1 as int), -1)) as countBit1,
count(isnull(cast(Bit2 as int), -1)) as countBit2,
count(isnull(cast(Bit3 as int), -1)) as countBit3,
count(isnull(cast(Bit4 as int), -1)) as countBit4
FromTestView (nolock)
wheregroup1 = @Group
group by Group1
)
/*------------------------------------------------
testing....
*/------------------------------------------------
--47 CPU
select *
from TestViewAgr
where Group1 = 1
--31 CPU
select * from TestParamAgr(1)
Signature is NULL
December 3, 2003 at 7:07 am
Hmi, can't really call it a view if its a function can you?! I know what you're saying.
Calvin,
Interesting. Might be fun to run some bigger tests. Functions dont always seem to be great for perf, then again, I still dont use them much (enough).
Andy
December 2, 2004 at 3:46 am
We tried this out to replace a whole set of tables setup to keep the results of each user's selections from the database. We constructed a single table to hold all the results and a parametrised view which each user could call to get just their own results. It's saved a lot of tables and the job of creating a new table every time a new user account is created.
Thanks.
September 3, 2008 at 8:31 pm
i like UDFs. However, I can't use them as a source of data if i need to create a report using Excel 🙂
just a thought.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply