Professional Web Applications Themes

How many times has a UDF been called? - Microsoft SQL / MS SQL Server

Hi everybody, I'm looking for an easy way to find out how many times a user-defined function has been called. That's not trivial, because a UDF is not allowed to create any objects so I can't just let the UDF write into a table. -- Arthur Hoornweg (please remove the ".net" from my e-mail address)...

  1. #1

    Default How many times has a UDF been called?

    Hi everybody,

    I'm looking for an easy way to find out how many times a
    user-defined function has been called. That's not trivial,
    because a UDF is not allowed to create any objects so
    I can't just let the UDF write into a table.




    --
    Arthur Hoornweg
    (please remove the ".net" from my e-mail address)

    Arthur Guest

  2. #2

    Default Re: How many times has a UDF been called?

    Hi Arthur,

    You can use SQL Profiler and run a trace looking for the function's name in
    the textdata column, you can raise a user configurable event from your
    function with sp_trace_generateevent (which despite its sp_ prefix is an
    extended procedure, so you can use it in a function) and trace that with
    Profiler, or you can log a message from the function to the Windows Event
    log with xp_logevent.

    You can also work around the limitation of not being able to write to a
    table, by calling osql, the SQL Server commandline utility, with
    xp_cmdshell, or using the sp_OA procedures to call a COM component and let
    that do the work.

    My preferred solution would be to use sp_trace_generateevent, but you need
    to give permissions to it first, as it defaults to members os the sysadmin
    role only.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Arthur Hoornweg" <nl.net> wrote in message
    news:en#x#phx.gbl... 


    Jacco Guest

  3. #3

    Default Re: How many times has a UDF been called?

    Hi Jacco,

    sp_trace_generateevent doesn't seem to be working though I'm both
    dbo and administrator. I can see in the profiler that the rights
    for sp_trace_generateevent are checked once but it isn't executed.
    Any clues?


    --
    Arthur Hoornweg
    (please remove the ".net" from my e-mail address)

    Arthur Guest

  4. #4

    Default Re: How many times has a UDF been called?

    Hi Arthur,

    I just ran
    exec sp_trace_generateevent 82, N'test'
    from Query yzer and it worked fine. Do you trace for the event
    Userconfigurable:0 ?

    If it doesn't work, can you post your code and the relevant bits of what
    Profiler shows?

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Arthur Hoornweg" <nl.net> wrote in message
    news:phx.gbl... 


    Jacco Guest

  5. #5

    Default Re: How many times has a UDF been called?

    Hello Jacco,

    the profiler says:

    Audit object Permission, exec sp_trace_generateevent 82,N'TEST'
    Exception Error:557, Severity:16, State:2

    as soon as I call sp_trace_generateevent in my user-defined function.
    Any idea what could be wrong here? I am both dbo and admin on
    the server. Do I need to give myself rights on this
    exec_Trace_generateevent, and how do I do that?


    I have some doubts whether the UDF is really evaluated
    before the rest of the WHERE clauses; my table currently
    has 250,000 records. If I SELECT some 20 records from the
    view the server answers within 10 milliseconds. The UDF
    performs at least 2 SELECTS for every record so it would
    be pretty amazing if it really did 500,000 Selects in
    10 milliseconds.


    The database stores oilwell data.
    An oilwell can consist of multiple boreholes.
    An oilfield can contain multiple oilwells.

    A user can have access either to single boreholes,
    single oilwells or the whole oilfield. These access
    rights are to be evaluated in my view and UDF.


    A query typically looks like:

    "select depth,C1,C2,C3,modified from hiresdata_view
    WHERE fk_Track='{1234-12345-6544-98098098}' AND
    depth BETWEEN 1000 AND 1010 ORDER BY depth"




    Here's the definition of the view.

    CREATE VIEW hiresdata_view AS
    SELECT * FROM hiresdata H
    where dbo.sec_HaveAccess (h.fk_track,'T')=1
    GO






    In the table sec_rights I store the access rights of the
    individual users. The field "righttype" can be either
    "F" for oilfield, "W" for oilwell, or "T" for borehole.
    If a user requires data from a borehole the UDF checks
    if the user has access rights for the borehole/well/
    oilfield.


    create table sec_rights(
    uname VARCHAR (128) NOT NULL,
    Righttype CHAR NOT NULL, /* F=FIELD, W=WELL, T=BOREHOLE */
    objectid Uniqueidentifier not null
    );
    GO
    create index idx_rights ON sec_rights (uname,righttype,objectid);
    GO



    /* THIS is the function that is called in the view */

    Create function sec_HaveAccess (id uniqueidentifier, typ CHAR)
    returns int as
    begin
    /* exec sp_trace_generateevent 82, N'test' */
    return dbo.sec_UserHasAccess (system_user,id,typ)
    end
    GO




    Create function sec_UserHasAccess (uname varchar(256),
    id uniqueidentifier, typ CHAR) returns int as
    begin
    declare temp int,
    id2 uniqueidentifier

    if typ='T'
    BEGIN
    /*does user have access to borehole? */
    set temp=dbo.sec_HasDirectAccess(uname,id,'T')
    if temp=1 return 1

    /*does user have access to oilwell? */
    select id2=fk_wellnr from tracks where tracknr=id
    set temp=dbo.sec_HasDirectAccess(uname,id2,'W')
    if temp=1 return 1

    /*does user have access to oilfield? */
    select id2=fk_oilfieldnr from wells where wellnr=id2
    set temp=dbo.sec_HasDirectAccess(uname,id2,'F')
    if temp=1 return 1
    END

    if typ='W'
    BEGIN
    /*does user have access to oilwell? */
    set temp=dbo.sec_HasDirectAccess(uname,id,'W')
    if temp=1 return 1

    /*does user have access to oilfield? */
    select id2=fk_oilfieldnr from wells where wellnr=id
    set temp=dbo.sec_HasDirectAccess(uname,id2,'F')
    if temp=1 return 1
    END


    if typ='F' return dbo.sec_HasDirectAccess(uname,id,'F')

    return 0
    end
    GO



    Create function sec_HasDirectAccess (uname varchar(256), id
    uniqueidentifier, typ CHAR)
    returns int as
    BEGIN
    IF EXISTS (SELECT * FROM sec_rights WHERE uname=uname
    AND righttype=typ AND objectid=id)
    RETURN 1
    ELSE RETURN 0
    RETURN 0
    END
    GO







    --
    Arthur Hoornweg
    (please remove the ".net" from my e-mail address)

    Arthur Guest

  6. #6

    Default Re: How many times has a UDF been called?

    Hi Arthur,

    Even thought sp_trace_generateevent is an extended procedure (you can
    confirm this with:
    use master
    SELECT OBJECTPROPERTY(OBJECT_ID('sp_trace_generateevent') ,
    'ItendedProc'))
    and you should be allowed to call extended procedures from a function, this
    apparently only works for extended procedures whose name starts with xp_.
    I'm not sure whether this is a bug in SQL Server or a doentation error.

    You can assume that in most cases the Query Optimizer would limit the
    resultset by the other WHERE clauses before it evaluates the WHERE clause
    with the UDF in it. You can check this by looking at the execution plan in
    Query yzer.

    If the query doesn't perform like you would like, you should try to put all
    the permission checking in the view instead of using functions. Functions
    work on a row by row basis, and this is an order of magnitude slower than
    using a set based solution. You probably don't notice the difference at the
    moment because the number of rows that is returned is small, but with larger
    number of rows you will see the difference.


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Arthur Hoornweg" <nl.net> wrote in message
    news:%23$phx.gbl... 


    Jacco Guest

Similar Threads

  1. Event handlers called multiple times
    By Tumurbaatar S. in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: November 11th, 10:26 AM
  2. Count the no of times a script is called
    By Amit Phatak in forum PERL Beginners
    Replies: 8
    Last Post: November 27th, 08:37 PM
  3. OnItemCreated and OnItemDataBound called too many times?
    By Pete Coolidge in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: July 4th, 09:25 PM
  4. COM dll being called multiple times
    By Adrian Forbes [ASP MVP] in forum ASP Components
    Replies: 0
    Last Post: July 2nd, 09:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139