Professional Web Applications Themes

User Defined Functions, and other stories.... - Microsoft SQL / MS SQL Server

I have a rather complex function, which I suppose in reality is just a 'view' on various tables, except that it takes three parameters, therefore ruling out the 'View' feature. My other options, to the best of my knowledge, are : (1) User Defined Function that returns a table (2) Stored Procedure that populates a temporary table that the calling process then accesses (yuck) Option 1 is fine but takes forever to run, even as an inline UDF. Option 2 is gick. Any other suggestions?? Brian...

  1. #1

    Default User Defined Functions, and other stories....

    I have a rather complex function, which I suppose in reality is just a
    'view' on various tables, except that it takes three parameters,
    therefore ruling out the 'View' feature.

    My other options, to the best of my knowledge, are :

    (1) User Defined Function that returns a table
    (2) Stored Procedure that populates a temporary table that the calling
    process then accesses (yuck)

    Option 1 is fine but takes forever to run, even as an inline UDF.
    Option 2 is gick.

    Any other suggestions??

    Brian
    Brian McGee Guest

  2. #2

    Default Re: User Defined Functions, and other stories....


    Thanks for the hint!

    The UDF (and I know this sounds outrageous, but I have no option), is
    basically a select from a derived table, grouped by the first column,
    BUT the derived table consists of 26 'union all' selects from different
    tables!)

    Now obviously it will be slow due to the nature of the selects, but by
    running each part individually in Query yzer I can achieve the same
    thing in 8 minutes, whereas running it as a function takes closer to 25.

    I'll have a look to see if it's cursorized at all!



    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Brian McGee Guest

Similar Threads

  1. Replies: 1
    Last Post: April 26th, 06:58 AM
  2. #26037 [NEW]: add user defined flags to imap functions
    By flystop at gmx dot net in forum PHP Development
    Replies: 0
    Last Post: October 30th, 11:25 AM
  3. #24843 [NEW]: session_regenerate_id does not call user-defined session functions
    By luttgens at fusl dot ac dot be in forum PHP Development
    Replies: 0
    Last Post: July 28th, 02:45 PM
  4. How and where to create User-Defined functions
    By Serge Rielau in forum IBM DB2
    Replies: 0
    Last Post: June 30th, 05:16 PM
  5. user defined functions and recursive selects
    By Serge Rielau in forum IBM DB2
    Replies: 0
    Last Post: June 30th, 05:10 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