Professional Web Applications Themes

ASP vs Stored Proc vs UDF - ASP Database

Hi all, I have a field in Table A that must be updated whenever a record is added to Table B. Table A will always only contain one single record and one of the fields gets updated whenever Table B gets a record added. This is basically how I currently have it in ASP: 1. User inputs data into a textbox in the browser; 2. Browser posts the textbox data to the ASP page; 3. ASP page creates a new record in Table B with one of its fields populated with the textbox data; 4. ASP page then updates the ...

  1. #1

    Default ASP vs Stored Proc vs UDF

    Hi all,

    I have a field in Table A that must be updated whenever a record is added to
    Table B. Table A will always only contain one single record and one of the
    fields gets updated whenever Table B gets a record added. This is basically
    how I currently have it in ASP:

    1. User inputs data into a textbox in the browser;
    2. Browser posts the textbox data to the ASP page;
    3. ASP page creates a new record in Table B with one of its fields populated
    with the textbox data;
    4. ASP page then updates the field in Table A.

    All of these operations get completed, and everything works fine for the 30
    or so users using the system.

    Currently, I #include this functionality in all of the ASP pages that need
    to perform steps 3 and 4 above. Again, no problem.

    But... I'm thinking that this type of functionality would be much better
    suited for SQL Server, rather than ASP, via a Stored Proc or a User Defined
    Function (UDF). I've read here that UDF's can be slow if complex
    calculations are used, which is definately not the case in this situation.

    Another thing is... if for some unforseen reason, the server crashed just as
    step 3 completed... and never got to step 4, then the data in Table A would
    not be updated, therefore not valid. SQL Server is on a different machine,
    so I'm thinking that even if the web server crashed after step 3, that step
    4 would still get completed, if done with a stored proc or a UDF.

    Basically I'm also trying to think ahead, performance-wise, to where the
    system might have to serve 2000+ users, and because of my paranioa, I want
    to make sure that I have used the best solution for now and for the future.
    ;]

    Any insights are appreciated.
    ~Brad


    Brad Guest

  2. #2

    Default Re: ASP vs Stored Proc vs UDF

    Brad,

    Your best bet is probably to put a trigger on table B to update table A. If
    this approach isn't suitable, using a single stored procedure to perform
    both the table B insert and the table A update would be another decent
    approach. The insert and update should be wrapped in a transaction in order
    to ensure that both operations succeed or fail together.

    Your current approach requires two database round-trips since both the
    insert and update are driven from the ASP page. Both the approaches
    suggested above would allow you to accomplish the same goal with a single
    call to the database.

    HTH,
    Nicole


    "Brad" <_nospam_disruptedsinnerhotmail.com> wrote in message
    news:%23XEuTTAnDHA.2432TK2MSFTNGP10.phx.gbl...
    > Hi all,
    >
    > I have a field in Table A that must be updated whenever a record is added
    to
    > Table B. Table A will always only contain one single record and one of the
    > fields gets updated whenever Table B gets a record added. This is
    basically
    > how I currently have it in ASP:
    >
    > 1. User inputs data into a textbox in the browser;
    > 2. Browser posts the textbox data to the ASP page;
    > 3. ASP page creates a new record in Table B with one of its fields
    populated
    > with the textbox data;
    > 4. ASP page then updates the field in Table A.
    >
    > All of these operations get completed, and everything works fine for the
    30
    > or so users using the system.
    >
    > Currently, I #include this functionality in all of the ASP pages that need
    > to perform steps 3 and 4 above. Again, no problem.
    >
    > But... I'm thinking that this type of functionality would be much better
    > suited for SQL Server, rather than ASP, via a Stored Proc or a User
    Defined
    > Function (UDF). I've read here that UDF's can be slow if complex
    > calculations are used, which is definately not the case in this situation.
    >
    > Another thing is... if for some unforseen reason, the server crashed just
    as
    > step 3 completed... and never got to step 4, then the data in Table A
    would
    > not be updated, therefore not valid. SQL Server is on a different machine,
    > so I'm thinking that even if the web server crashed after step 3, that
    step
    > 4 would still get completed, if done with a stored proc or a UDF.
    >
    > Basically I'm also trying to think ahead, performance-wise, to where the
    > system might have to serve 2000+ users, and because of my paranioa, I want
    > to make sure that I have used the best solution for now and for the
    future.
    > ;]
    >
    > Any insights are appreciated.
    > ~Brad
    >
    >

    Nicole Calinoiu Guest

  3. #3

    Default Re: ASP vs Stored Proc vs UDF

    Thanks for the info. And yes, avoiding unnecessary round trips to the
    database server is also something I would like. I am not a DBA by any means,
    so now I'll have to try and figure out how to "wrap all this in a
    transaction", as well as code the stored proc. ;]

    There was also no mention of whether or not using a UDF would be any sort of
    good solution - or should this not even come into play?

    ~Brad

    "Nicole Calinoiu" <nicolecsomewhere.net> wrote in message
    news:eqldCaAnDHA.3504TK2MSFTNGP11.phx.gbl...
    > Brad,
    >
    > Your best bet is probably to put a trigger on table B to update table A.
    If
    > this approach isn't suitable, using a single stored procedure to perform
    > both the table B insert and the table A update would be another decent
    > approach. The insert and update should be wrapped in a transaction in
    order
    > to ensure that both operations succeed or fail together.
    >
    > Your current approach requires two database round-trips since both the
    > insert and update are driven from the ASP page. Both the approaches
    > suggested above would allow you to accomplish the same goal with a single
    > call to the database.
    >
    > HTH,
    > Nicole
    >

    Brad Guest

  4. #4

    Default Re: ASP vs Stored Proc vs UDF

    > I have a field in Table A that must be updated whenever a record is added
    to
    > Table B. Table A will always only contain one single record and one of the
    > fields gets updated whenever Table B gets a record added. This is
    basically
    > how I currently have it in ASP:
    Table A gets updated with what? If it's only one row, what does it contain?
    The date/time of the update? Couldn't you just have a column in Table B
    that stores this information, then you could say:

    SELECT * FROM tableB WHERE pkey = (SELECT TOP 1 pkey FROM TableB ORDER BY
    dateUpdateColumn DESC)

    I don't see the need for the second table at all.
    > But... I'm thinking that this type of functionality would be much better
    > suited for SQL Server, rather than ASP, via a Stored Proc or a User
    Defined
    > Function (UDF).
    A UDF does *not* perform DML. A UDF is used for manipulating and returning
    data. Try to create a function with an INSERT or UPDATE statement to see
    what I mean.
    > Another thing is... if for some unforseen reason, the server crashed just
    as
    > step 3 completed...
    This is what transactions are for. If you really need this to be in
    separate tables (I'm not convinced, but please try to justify this), then
    you could use a stored procedure:

    CREATE PROCEDURE dbo.foo
    val INT
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE err1 INT, err2 INT
    BEGIN TRAN
    INSERT TableB ... VALUES(val)
    SET err1 = ERROR
    UPDATE TableA ...
    SET err2 = ERROR
    IF err1 + err2 > 0
    ROLLBACK
    ELSE
    COMMIT
    END
    GO
    > Basically I'm also trying to think ahead, performance-wise, to where the
    > system might have to serve 2000+ users, and because of my paranioa, I want
    > to make sure that I have used the best solution for now and for the
    future.
    > ;]
    Contrary to what Nicole suggested, a trigger is not the best solution if you
    are worried about performance. As long as you can ensure that all inserts
    to tableB can be controlled by the stored procedure, the above is probably
    the safest and fastest solution.

    But again, I fail to see why a second table has to be updated every time a
    table is inserted. This seems like the logic and data could be confined to
    a single table, and take away the complexity of your problem...


    Aaron Bertrand [MVP] Guest

  5. #5

    Default Re: ASP vs Stored Proc vs UDF

    Thanks for the replies. I have my answers.

    ~Brad



    Brad Guest

  6. #6

    Default Re: ASP vs Stored Proc vs UDF

    "Aaron Bertrand [MVP]" <aaronTRASHaspfaq.com> wrote in message
    news:OPBm6zDnDHA.2244TK2MSFTNGP12.phx.gbl...
    > Contrary to what Nicole suggested, a trigger is not the best solution if
    you
    > are worried about performance. As long as you can ensure that all inserts
    > to tableB can be controlled by the stored procedure, the above is probably
    > the safest and fastest solution.
    I wasn't suggesting that is the best solution in terms of performance, just
    that it would offer substantially better performance than driving both
    operations separately from the ASP code. However, it is a better approach
    than a stored procedure if data integrity is a real concern. There is
    absolutely no way to guarantee that all DML operations will be driven
    through stored procedures intended for this purpose. Sooner or later, a DBA
    is going to make a little "harmless" change directly to the table data, and
    there goes the assumption that the rules were applied with every data
    modification. Of course, a DBA can explicitly disable a trigger, but that
    requires an intervention with a higher probability of awareness of the
    rule-breaking nature of the operation.

    > But again, I fail to see why a second table has to be updated every time a
    > table is inserted. This seems like the logic and data could be confined
    to
    > a single table, and take away the complexity of your problem...
    I tried to give him the benefit of the doubt on this one, or maybe I was
    just being lazy because it was Sunday evening... <g>


    Nicole Calinoiu Guest

  7. #7

    Default Re: ASP vs Stored Proc vs UDF

    I will clarify exactly why the dba has a table with one row:

    It's a table that holds system values. For example, we are not using an auto
    increment numerical id as our primary keys, we are using a char(9), which
    holds characters from 0-9 and A-Z, such as:

    Id FName
    NZ434RT1B Brad
    NZ434RT1C Aaron
    NZ434RT1D Bob

    The keys can be any value from '000000000' to 'ZZZZZZZZZ'

    We have an ASP function that is able to create these sequentially, given the
    previous key. For example, if we pass the following value to the ASP key
    generator: AD1BGN3F4, the function will return the next key in the sequence:
    AD1BGN3F5

    Now, we don't want keys to repeat, so we have to store the last generated
    key. And, with that, if the we have to shut down the system, the last key
    generated is available to us when the system comes back up. All we have to
    do is query that value from our system values table.

    Now, instead of having the ASP page generate this key, and then store it in
    our system values (single row) table, we can have SQL Server do that job
    automatically with a stored procedure.

    ~Brad


    "Nicole Calinoiu" <nicolecsomewhere.net> wrote in message
    news:eIdItUJnDHA.2312TK2MSFTNGP12.phx.gbl...
    > "Aaron Bertrand [MVP]" <aaronTRASHaspfaq.com> wrote in message
    > news:OPBm6zDnDHA.2244TK2MSFTNGP12.phx.gbl...
    > > Contrary to what Nicole suggested, a trigger is not the best solution if
    > you
    > > are worried about performance. As long as you can ensure that all
    inserts
    > > to tableB can be controlled by the stored procedure, the above is
    probably
    > > the safest and fastest solution.
    >
    > I wasn't suggesting that is the best solution in terms of performance,
    just
    > that it would offer substantially better performance than driving both
    > operations separately from the ASP code. However, it is a better approach
    > than a stored procedure if data integrity is a real concern. There is
    > absolutely no way to guarantee that all DML operations will be driven
    > through stored procedures intended for this purpose. Sooner or later, a
    DBA
    > is going to make a little "harmless" change directly to the table data,
    and
    > there goes the assumption that the rules were applied with every data
    > modification. Of course, a DBA can explicitly disable a trigger, but that
    > requires an intervention with a higher probability of awareness of the
    > rule-breaking nature of the operation.
    >
    >
    > > But again, I fail to see why a second table has to be updated every time
    a
    > > table is inserted. This seems like the logic and data could be confined
    > to
    > > a single table, and take away the complexity of your problem...
    >
    > I tried to give him the benefit of the doubt on this one, or maybe I was
    > just being lazy because it was Sunday evening... <g>
    >
    >

    Brad Guest

  8. #8

    Default Re: ASP vs Stored Proc vs UDF

    Brad,

    With this sort of situation, you really need to watch out for your
    transaction isolation levels in order to ensure that near-simulataneous
    submissions don't cause problems in the generation and use of your IDs. See
    [url]http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_74bw.asp[/url] for
    details on using transaction isolation levels in SQL Server.

    Nicole


    "Brad" <_nospam_disruptedsinnerhotmail.com> wrote in message
    news:%23XGPlDNnDHA.964TK2MSFTNGP10.phx.gbl...
    > I will clarify exactly why the dba has a table with one row:
    >
    > It's a table that holds system values. For example, we are not using an
    auto
    > increment numerical id as our primary keys, we are using a char(9), which
    > holds characters from 0-9 and A-Z, such as:
    >
    > Id FName
    > NZ434RT1B Brad
    > NZ434RT1C Aaron
    > NZ434RT1D Bob
    >
    > The keys can be any value from '000000000' to 'ZZZZZZZZZ'
    >
    > We have an ASP function that is able to create these sequentially, given
    the
    > previous key. For example, if we pass the following value to the ASP key
    > generator: AD1BGN3F4, the function will return the next key in the
    sequence:
    > AD1BGN3F5
    >
    > Now, we don't want keys to repeat, so we have to store the last generated
    > key. And, with that, if the we have to shut down the system, the last key
    > generated is available to us when the system comes back up. All we have to
    > do is query that value from our system values table.
    >
    > Now, instead of having the ASP page generate this key, and then store it
    in
    > our system values (single row) table, we can have SQL Server do that job
    > automatically with a stored procedure.
    >
    > ~Brad
    >
    >
    > "Nicole Calinoiu" <nicolecsomewhere.net> wrote in message
    > news:eIdItUJnDHA.2312TK2MSFTNGP12.phx.gbl...
    > > "Aaron Bertrand [MVP]" <aaronTRASHaspfaq.com> wrote in message
    > > news:OPBm6zDnDHA.2244TK2MSFTNGP12.phx.gbl...
    > > > Contrary to what Nicole suggested, a trigger is not the best solution
    if
    > > you
    > > > are worried about performance. As long as you can ensure that all
    > inserts
    > > > to tableB can be controlled by the stored procedure, the above is
    > probably
    > > > the safest and fastest solution.
    > >
    > > I wasn't suggesting that is the best solution in terms of performance,
    > just
    > > that it would offer substantially better performance than driving both
    > > operations separately from the ASP code. However, it is a better
    approach
    > > than a stored procedure if data integrity is a real concern. There is
    > > absolutely no way to guarantee that all DML operations will be driven
    > > through stored procedures intended for this purpose. Sooner or later, a
    > DBA
    > > is going to make a little "harmless" change directly to the table data,
    > and
    > > there goes the assumption that the rules were applied with every data
    > > modification. Of course, a DBA can explicitly disable a trigger, but
    that
    > > requires an intervention with a higher probability of awareness of the
    > > rule-breaking nature of the operation.
    > >
    > >
    > > > But again, I fail to see why a second table has to be updated every
    time
    > a
    > > > table is inserted. This seems like the logic and data could be
    confined
    > > to
    > > > a single table, and take away the complexity of your problem...
    > >
    > > I tried to give him the benefit of the doubt on this one, or maybe I was
    > > just being lazy because it was Sunday evening... <g>
    > >
    > >
    >
    >

    Nicole Calinoiu Guest

  9. #9

    Default Re: ASP vs Stored Proc vs UDF

    Nicole,

    << With this sort of situation, you really need to watch out for your
    transaction isolation levels in order to ensure that near-simulataneous
    submissions don't cause problems in the generation and use of your IDs. >>

    Yes, this is definately one issue that I would like to avoid having to deal
    with. Basically, if I understand you correctly: Any time a record is created
    on any table that uses the key generator stored proc, and that also updates
    our system values table -- should be encapsulated in an isolated transaction
    block.

    Now, assuming this is correct, I'm also assuming that transactions are
    processed in the order they came in. For example, two transactions are
    executed, TransA and TransB. Will TransB only be executed when TransA
    completes?

    If this IS the case, I'm wondering how that will effect performance. If for
    example 500 people all added a record within seconds of each other (so the
    stored proc is called 500 times)... I'd hate to think how long it would take
    for the 500th person to get a response back from SQL Server, or IIS with a
    new ASP page.

    ~Brad


    Brad Guest

  10. #10

    Default Re: ASP vs Stored Proc vs UDF

    > Now, assuming this is correct, I'm also assuming that transactions are
    > processed in the order they came in. For example, two transactions are
    > executed, TransA and TransB. Will TransB only be executed when TransA
    > completes?
    Depends on the isolation level, and the nature of the statements contained
    within each transaction. Again, see the URL Nicole posted to get a decent
    background on this.
    > If this IS the case, I'm wondering how that will effect performance. If
    for
    > example 500 people all added a record within seconds of each other (so the
    > stored proc is called 500 times)... I'd hate to think how long it would
    take
    > for the 500th person to get a response back from SQL Server, or IIS with a
    > new ASP page.
    If 500 people are entering data this close to one another, maybe you could
    give a better solution to how you will keep them all from tripping over each
    other, rather than isolating their activity.


    Aaron Bertrand - MVP Guest

  11. #11

    Default Re: ASP vs Stored Proc vs UDF

    Brad wrote:
    > Nicole,
    >
    > << With this sort of situation, you really need to watch out for your
    > transaction isolation levels in order to ensure that
    > near-simulataneous submissions don't cause problems in the generation
    > and use of your IDs. >>
    >
    > Yes, this is definately one issue that I would like to avoid having
    > to deal with. Basically, if I understand you correctly: Any time a
    > record is created on any table that uses the key generator stored
    > proc, and that also updates our system values table -- should be
    > encapsulated in an isolated transaction block.
    >
    > Now, assuming this is correct, I'm also assuming that transactions are
    > processed in the order they came in. For example, two transactions are
    > executed, TransA and TransB. Will TransB only be executed when TransA
    > completes?
    >
    > If this IS the case, I'm wondering how that will effect performance.
    > If for example 500 people all added a record within seconds of each
    > other (so the stored proc is called 500 times)... I'd hate to think
    > how long it would take for the 500th person to get a response back
    > from SQL Server, or IIS with a new ASP page.
    >
    > ~Brad
    Yes, using an incrementing ID will serialize the transactions. This is one
    of the arguments against using them. A better plan, if you are worried about
    performance, might be to use a GUID for your keys. You can create a GUID in
    vbscript by using:

    guid = left(createobject("scriptlet.typelib").guid,38)

    The advantage of course, is that it is random, and is guaranteed to be
    globally unique, so you do not have to worry about creating the same GUID
    twice. This can relieve you of the necessity to maintain the system values
    table.

    The disadvantage is that it is 38 characters wide, making any index that
    uses it that much bigger and that much slower to search.

    HTH,
    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows Guest

  12. #12

    Default Re: ASP vs Stored Proc vs UDF

    Bob Barrows Wrote:
    > Yes, using an incrementing ID will serialize the transactions. This is one
    > of the arguments against using them. A better plan, if you are worried
    about
    > performance, might be to use a GUID for your keys. You can create a GUID
    in
    > vbscript by using:
    >
    > guid = left(createobject("scriptlet.typelib").guid,38)
    >
    > The advantage of course, is that it is random, and is guaranteed to be
    > globally unique, so you do not have to worry about creating the same GUID
    > twice. This can relieve you of the necessity to maintain the system values
    > table.
    >
    > The disadvantage is that it is 38 characters wide, making any index that
    > uses it that much bigger and that much slower to search.
    Yes, 38 characters is quite the Id length. The thing is that we don't want
    to use a numeric Id either. :/

    But... now I'm thinking that instead of all the complexity, maybe it would
    be a better idea just to use the guid.


    Brad Guest

Similar Threads

  1. SQL Stored Proc question
    By Swd1974 in forum Coldfusion Database Access
    Replies: 2
    Last Post: July 27th, 09:11 PM
  2. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  3. stored proc RETURN value in ASP
    By ben h in forum ASP Database
    Replies: 2
    Last Post: June 17th, 11:02 AM
  4. Nesting IF's in Stored Proc
    By Gary in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 7th, 04:25 AM
  5. stored proc and tcp/ip
    By Helmut Wöss in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 08:13 AM

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