How would UDF affect performance?

Last Post 13 May 2006 02:23 PM by SQLUSA. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQL_Jr
New Member
New Member

--
12 May 2006 08:05 AM
One of my devs is creating a large view composed of many tables. It will be the master view that all users will hit. He wants to create the view using a UDF in SQL. Will this have any performance benefits or minuses? Please let me know. TIA
nosepicker
New Member
New Member

--
12 May 2006 08:19 AM
"Create a view using a UDF"? Taking what you wrote literally, you can't create a view within a UDF. Reading between the lines, I think you're talking about selecting data from a bunch of tables within a UDF. Are you asking about performance difference between using a view versus using a UDF? The select statements within both will perform the same. Performance will vary depending on how they're used. What exactly are you trying to accomplish? Do you need to return a value or set of records from the select statements? Generally, a view and a UDF are used to accomplish different tasks, and they're usually not interchangeable.
cmdr_jpskywalker
New Member
New Member

--
12 May 2006 08:33 AM
are you saying based on the user id, the UDF will populate the view? If it is, I suggest that you use the indexed view and add the WHERE clause constraint on the user id. It is better to use the view rather than a UDF because UDF (specially with dynamic sql) tends to have recompile overhead rather than using INDEXED VIEW/VIEW. Check what you're really trying to accomplish and paste some code here. We may be able to help you more.
SQL_Jr
New Member
New Member

--
12 May 2006 08:39 AM
I think cmd_jpskywalker is in the galaxy.......remember a developer explained to me ;-)
May the force be w/you. I'll post back if I get clarification.

THX!
cmdr_jpskywalker
New Member
New Member

--
12 May 2006 08:46 AM
there is a lot of developer in the galaxy and I am not sure who's who. I usually don't use the force to guess . But I'll try what i can to help.
mwesch
New Member
New Member

--
12 May 2006 04:36 PM
I've created a number of views that are based on an underlying UDF like this.

create view MyView
as
select * from MyFuntion()

Big reason I have done this is that we use MS Excel for a lot of internal reporting and MS Query (or ODBC)doesn't support queries against UDF's. I do believe there is some performace hit against re-writing my UDF as a view, but I gain a lot in code maintenance because I use the same UDF's in a lot of other stored procedures and I didn't want to have to replicate the code.
cmdr_jpskywalker
New Member
New Member

--
12 May 2006 06:28 PM
have you tried OPENDATASOURCE/OPENROWSET?
SQLUSA
New Member
New Member

--
13 May 2006 02:23 PM
The answer to your question is dependent on the size of tables, indexes and your platform.

Definitely dynamic constructs, views and functions should only be used for small tables.

For large tables you need clean joins with good indexes.

SQL_Jr
New Member
New Member

--
15 May 2006 04:49 AM
Thx, ALL:

Will give due consideration to all your replies..........
You are not authorized to post a reply.

Acceptable Use Policy