Which is the better approach?

Discussion in 'ASP General' started by Rob Meade, May 31, 2006.

  1. Rob Meade

    Rob Meade Guest

    Hi all,

    I have a databse which I'm pulling the data from for my ASP page.

    I have 4 tables, Course, Feature, Objective, and PreRequisite. The last
    three all contain a course product code and a text column - the course
    product code links the row(s) to the Course table.

    So, I might have 1 entry in Course, but perhaps 3 in Feature, 5 in Objective
    and 2 in PreRequisite.

    I grab all of these using my stored procedure which, using the LEFT JOIN's
    returns a multitude of rows, what I'm wondering is whether I am better
    returning say 20 rows of data, and then having to iterate through
    determining where the Features start/end, Objectives start/end and
    PreRequisites start/end (ie, they are repeated in the data) or - whether it
    would be more effecient to hit each of the tables with a stored procedure,
    using the same connection and thus getting just the 1 course, then 3
    features to iterate through, then 5 objectives to iterate through and then 2
    pre-requisites to iterate through - all of which would then get displayed to
    the page.

    The latter option sounds "clearer", and obviously a bit easier code wise -
    but I just wondered whether there would be much of a difference performance
    wise - seems that I either get lots of rows in one hit and walk away from
    SQL Server and let the web server do the work, or I do less on the web
    server and hit the SQL Server several times....

    Any suggestions would be appreciated.

    Best regards

    Rob
     
    Rob Meade, May 31, 2006
    #1
    1. Advertising

  2. Rob Meade wrote:
    > Hi all,
    >
    > I have a databse which I'm pulling the data from for my ASP page.
    >
    > I have 4 tables, Course, Feature, Objective, and PreRequisite. The
    > last three all contain a course product code and a text column - the
    > course product code links the row(s) to the Course table.
    >
    > So, I might have 1 entry in Course, but perhaps 3 in Feature, 5 in
    > Objective and 2 in PreRequisite.


    Hmm, I would probably have made a single table out of these, perhaps
    calling the table CourseAttributes. It would have three columns: product
    code, Attribute, Attribute Type (either "Feature", "Objective" or
    "PreRequisite")

    >
    > I grab all of these using my stored procedure which, using the LEFT
    > JOIN's returns a multitude of rows, what I'm wondering is whether I
    > am better returning say 20 rows of data, and then having to iterate
    > through determining where the Features start/end, Objectives
    > start/end and PreRequisites start/end (ie, they are repeated in the
    > data) or - whether it would be more effecient to hit each of the
    > tables with a stored procedure, using the same connection and thus
    > getting just the 1 course, then 3 features to iterate through, then 5
    > objectives to iterate through and then 2 pre-requisites to iterate
    > through - all of which would then get displayed to the page.
    >
    > The latter option sounds "clearer", and obviously a bit easier code
    > wise - but I just wondered whether there would be much of a
    > difference performance wise - seems that I either get lots of rows in
    > one hit and walk away from SQL Server and let the web server do the
    > work, or I do less on the web server and hit the SQL Server several
    > times....
    >
    > Any suggestions would be appreciated.
    >
    > Best regards
    >
    > Rob

    My inclination is to make as few trips to the database (out-of-process)
    as possible.
    Two options:
    1 Return multiple resultsets from the procedure, using the recordset's
    NextRecordset method to move to the next recordset.
    2.Use a union query to return a single resultset with the structure
    suggested for the CourseAttributes table above - you could make a view
    out of this.

    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 [MVP], May 31, 2006
    #2
    1. Advertising

  3. Rob Meade

    Rob Meade Guest

    "Bob Barrows [MVP]" wrote ...

    > Hmm, I would probably have made a single table out of these, perhaps
    > calling the table CourseAttributes. It would have three columns: product
    > code, Attribute, Attribute Type (either "Feature", "Objective" or
    > "PreRequisite")


    ok - at this time it seems that the data structure is pretty set, ie, I have
    no knowledge of additional items being need for a type of "Feature" only for
    example - but if there were I'd probably have to adopt the existing
    structure at that point - so I could make this change now that you've
    suggested.

    > My inclination is to make as few trips to the database (out-of-process)
    > as possible.


    Would you define a "trip" as a connection, or the execution of a command? I
    was always lead to believe that it was the number of connections that could
    have a big impact on the server (especially if you dont close 'em - tee hee
    :eek:D), but I thought perhaps if there was one connection that fired those 4
    SP's I mentioned in my other post that might not be so bad...I guess the
    execution time might be slightly longer?

    > Two options:
    > 1 Return multiple resultsets from the procedure, using the recordset's
    > NextRecordset method to move to the next recordset.


    I've never tried that in ASP - I've done something similar in .net with the
    dataset/datatables - I guess its similar is it?

    > 2.Use a union query to return a single resultset with the structure
    > suggested for the CourseAttributes table above - you could make a view
    > out of this.


    If I do this Bob, isn't that going to be exactly what I get from the SP
    right now though? ie, about 20 rows per course where most of the columns in
    the rows have the same data (ie the data from the Course table) because it
    cant have the empty columns? I'd still have to iterate through a larger
    record set and look for the differences to know when I'd got all of the
    features or all of the objectives wouldn't I?

    Thanks for your reply Bob - and further information appreciated.

    Regards

    Rob
     
    Rob Meade, Jun 1, 2006
    #3
  4. Rob Meade wrote:
    > "Bob Barrows [MVP]" wrote ...
    >
    >> Hmm, I would probably have made a single table out of these, perhaps
    >> calling the table CourseAttributes. It would have three columns:
    >> product code, Attribute, Attribute Type (either "Feature",
    >> "Objective" or "PreRequisite")

    >
    > ok - at this time it seems that the data structure is pretty set, ie,
    > I have no knowledge of additional items being need for a type of
    > "Feature" only for example - but if there were I'd probably have to
    > adopt the existing structure at that point - so I could make this
    > change now that you've suggested.
    >
    >> My inclination is to make as few trips to the database
    >> (out-of-process) as possible.

    >
    > Would you define a "trip" as a connection, or the execution of a
    > command?


    I'm referring to the execution of a command, which requires the command to
    be sent out-of-process to the database, and results to be marshalled back
    into process.

    > I was always lead to believe that it was the number of
    > connections that could have a big impact on the server (especially if
    > you dont close 'em - tee hee
    >> oD), but I thought perhaps if there was one connection that fired
    >> those 4

    > SP's I mentioned in my other post that might not be so bad...I guess
    > the execution time might be slightly longer?
    >
    >> Two options:
    >> 1 Return multiple resultsets from the procedure, using the
    >> recordset's NextRecordset method to move to the next recordset.

    >
    > I've never tried that in ASP - I've done something similar in .net
    > with the dataset/datatables - I guess its similar is it?


    Very. A stored procedure with multiple select statements, or a batched set
    of queries, will return multiple resultsets which can be processed by using
    set rs = rs.NextRecordset.
    But, this is really only a little more efficient than using multiple calls
    to separate procedures, since the request for the next resultset has to be
    sent to the database which has the results cached. It's the caching that
    makes the difference.

    >
    >> 2.Use a union query to return a single resultset with the structure
    >> suggested for the CourseAttributes table above - you could make a
    >> view out of this.

    >
    > If I do this Bob, isn't that going to be exactly what I get from the
    > SP right now though? ie, about 20 rows per course where most of the
    > columns in the rows have the same data (ie the data from the Course
    > table) because it cant have the empty columns?


    Well, you could do something like:

    select 'Course' as Source, courseid, coursecolint, coursecolvarchar,
    '' as Attribute
    from Course where courseid = ...
    union all
    select 'Feature', courseid,null,'', Feature
    from Feature where courseid = ...
    etc.

    > I'd still have to
    > iterate through a larger record set and look for the differences to
    > know when I'd got all of the features or all of the objectives
    > wouldn't I?

    The cost of iterating through 20 rows is likely to be small, especially if
    you disconnect from the database while doing it.
    How many columns from the Course table are we talking about? If only a few,
    don't worry about it. If there are a large number of columns, then I might
    lean toward using two resultsets: one containing the data from the Course
    table, and the other containing the attributes.

    Only testing can tell which approach is better.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 1, 2006
    #4
  5. Rob Meade wrote:
    > Hi all,
    >
    > I have a databse which I'm pulling the data from for my ASP page.
    >
    > I have 4 tables, Course, Feature, Objective, and PreRequisite. The
    > last three all contain a course product code and a text column - the
    > course product code links the row(s) to the Course table.
    >
    > So, I might have 1 entry in Course, but perhaps 3 in Feature, 5 in
    > Objective and 2 in PreRequisite.
    >


    Another option would be to pivot (crosstab) the data in the "attribute"
    tables so you wind up returning a single row for each course to the client.
    Unless you are using sql2005, which AIUI may contain builtin pivoting
    functionality, this will likely involve some messy dynamic sql. Google
    should yield you several examples of how to do it.

    Again. Each approach should be tested and benchmarked for comparison.
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 1, 2006
    #5
  6. Rob Meade

    Rob Meade Guest

    "Bob Barrows [MVP]" wrote ...

    > I'm referring to the execution of a command, which requires the command to
    > be sent out-of-process to the database, and results to be marshalled back
    > into process.


    I see, or rather I don't :eek:) Not sure what you mean by "out of process" and
    "into process" etc etc

    > Very. A stored procedure with multiple select statements, or a batched set
    > of queries, will return multiple resultsets which can be processed by
    > using set rs = rs.NextRecordset.
    > But, this is really only a little more efficient than using multiple calls
    > to separate procedures, since the request for the next resultset has to be
    > sent to the database which has the results cached. It's the caching that
    > makes the difference.


    I see..thanks..

    > The cost of iterating through 20 rows is likely to be small, especially if
    > you disconnect from the database while doing it.
    > How many columns from the Course table are we talking about?


    There's about 8 or 9 - not many, just the items from the course description
    (xml) that didn't have repeating items...for example, CourseTitle,
    CourseSynopsis, CourseAudience etc etc

    > If only a few, don't worry about it. If there are a large number of
    > columns, then I might lean toward using two resultsets: one containing the
    > data from the Course table, and the other containing the attributes.
    >
    > Only testing can tell which approach is better.


    Thanks Bob - appreciate you reply and info - originally the course pages
    were comprimising of 1587 html files, I needed to discard parts of these and
    insert bits here and there - thankfully I managed to get an XML source for
    each course so was able to write a little .net importer which populated the
    database (hence being able to change the design if its deemed
    inappropriate). There will be ONE .asp page which will receive a course id
    and populate the page with the details, I dont think its going to be
    massively popular hits wise, although the rest of the site is so I guess it
    could be over time. Just wanted to try and get things as good as I could
    from the outset rather than have to re-address it later on.

    Regards

    Rob
     
    Rob Meade, Jun 1, 2006
    #6
  7. Rob Meade

    Rob Meade Guest

    "Bob Barrows [MVP]" wrote ...

    > Another option would be to pivot (crosstab) the data in the "attribute"
    > tables so you wind up returning a single row for each course to the
    > client.
    > Unless you are using sql2005, which AIUI may contain builtin pivoting
    > functionality, this will likely involve some messy dynamic sql. Google
    > should yield you several examples of how to do it.


    Hi Bob,

    I remember trying to do this a long time ago with a dataset at "work" - what
    a nightmare that was - I dont think we even managed to do it in the end - I
    think at the moment, whilst I do want something really efficient, I would
    probably pass on this option unless someone could give me a REALLY easy to
    understand example etc - not because I'm lazy, but because at the moment I
    can't justify the time to research that against getting the rest of the
    project launch (I've gotta through some eCommerce stuff together for this
    yet too - never done that before)...

    Cheers for the thought though,

    Rob
     
    Rob Meade, Jun 1, 2006
    #7
  8. Rob Meade wrote:
    > "Bob Barrows [MVP]" wrote ...
    >
    >> I'm referring to the execution of a command, which requires the
    >> command to be sent out-of-process to the database, and results to be
    >> marshalled back into process.

    >
    > I see, or rather I don't :eek:) Not sure what you mean by "out of
    > process" and "into process" etc etc
    >

    Well, you have a process in inetinfo processing the vbscript code in
    your asp page. You have another process on your sql server processing
    commands, etc. It's always expensive to marshal information between
    processes.

    --
    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 [MVP], Jun 1, 2006
    #8
  9. Rob Meade

    Mike Brind Guest

    Rob Meade wrote:
    [snip]
    >
    > Thanks Bob - appreciate you reply and info - originally the course pages
    > were comprimising of 1587 html files, I needed to discard parts of these and
    > insert bits here and there - thankfully I managed to get an XML source for
    > each course


    LOL. I bet you were relieved! That saved you having to fiddle around
    with regular expressions :)

    --
    Mike Brind
     
    Mike Brind, Jun 1, 2006
    #9
  10. Rob Meade

    Rob Meade Guest

    "Bob Barrows [MVP]" wrote...

    > Well, you have a process in inetinfo processing the vbscript code in
    > your asp page. You have another process on your sql server processing
    > commands, etc. It's always expensive to marshal information between
    > processes.


    OIC! Thanks :eek:)

    Oh - and with regards to the RS.NextRecordSet stuff.....how would I do
    this...

    Get first course from first record set

    Get second recordset and iterate through all records

    Get third recordset and iterate through all records

    Get forth recordset and iterate through all records

    Move on to the next course

    ??

    I've just typed out what I thought, but then it didn't look right - I'll
    need more than 1 recordset object won't I? ie, one for the courses (big
    outer loop), and then 1 that gets used several times but re-populated from
    the 2nd, 3rd, and 4th recodsets iterating through all rows in each - then,
    the RS.MoveNext at the end to move to the next course - that sound about
    right?

    Regards

    Rob
     
    Rob Meade, Jun 1, 2006
    #10
  11. Rob Meade wrote:
    > "Bob Barrows [MVP]" wrote...
    >
    >> Well, you have a process in inetinfo processing the vbscript code in
    >> your asp page. You have another process on your sql server processing
    >> commands, etc. It's always expensive to marshal information between
    >> processes.

    >
    > OIC! Thanks :eek:)
    >
    > Oh - and with regards to the RS.NextRecordSet stuff.....how would I do
    > this...
    >
    > Get first course from first record set
    >
    > Get second recordset and iterate through all records
    >
    > Get third recordset and iterate through all records
    >
    > Get forth recordset and iterate through all records
    >
    > Move on to the next course
    >
    > ??
    >
    > I've just typed out what I thought, but then it didn't look right -
    > I'll need more than 1 recordset object won't I? ie, one for the
    > courses (big outer loop), and then 1 that gets used several times but
    > re-populated from the 2nd, 3rd, and 4th recodsets iterating through
    > all rows in each - then, the RS.MoveNext at the end to move to the
    > next course - that sound about right?
    >
    > Regards
    >

    set rs=cn.execute(<proc_that_returns_3_resultsets>)
    if not rs.eof then
    ...
    end if
    set rs=rs.nextrecordset
    if not rs.eof then
    ...
    end if
    set rs=rs.nextrecordset
    if not rs.eof then
    ...
    end if

    --
    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 [MVP], Jun 1, 2006
    #11
  12. Rob Meade

    Rob Meade Guest

    "Bob Barrows [MVP]" wrote ...

    > A stored procedure with multiple select statements, or a batched set of
    > queries, will return multiple resultsets which can be processed by using
    > set rs = rs.NextRecordset.


    I'm getting this Bob - any ideas?

    ADODB.Recordset error '800a0cb3'
    Current provider does not support returning multiple recordsets from a
    single execution.
    /parasolit/statics/mainbody-training-course.asp, line 78
    That line would read:

    Set RS2 = RS.NextRecordset

    Basically, because I need to keep the "course" recordset alive, I've created
    two - an outer looping one, and a series of inner looping ones...

    Regards

    Rob
     
    Rob Meade, Jun 1, 2006
    #12
  13. Rob Meade

    Rob Meade Guest

    "Mike Brind" wrote ...

    > LOL. I bet you were relieved! That saved you having to fiddle around
    > with regular expressions :)


    hehe, hi Mike - no sh!t :eek:)

    The problem now is that I need to get the heirarchy too! Each course
    belongs to a "series" - the series can be made up of several courses - they
    have yet to send me this - which, I'd put good money on the fact that
    they'll use some other kind of identifier that I *dont* have in the courses
    xml file!!!

    They've clearly got all this in a database at their end, a simple export
    would have sufficed, but ooooohhhh no - couldn't just have it made easy
    could I! :eek:)

    Regards

    Rob
    PS: If you know anything about multiple recordsets out of a stored proc -
    feel free to check out the problems I'm having ;o)
     
    Rob Meade, Jun 1, 2006
    #13
  14. Rob Meade wrote:
    > "Bob Barrows [MVP]" wrote ...
    >
    >> A stored procedure with multiple select statements, or a batched set
    >> of queries, will return multiple resultsets which can be processed
    >> by using set rs = rs.NextRecordset.

    >
    > I'm getting this Bob - any ideas?
    >
    > ADODB.Recordset error '800a0cb3'
    > Current provider does not support returning multiple recordsets from a
    > single execution.
    > /parasolit/statics/mainbody-training-course.asp, line 78
    > That line would read:


    What provider are you using? You're using SQL Server, right? I've never used
    the MSDASQL provider (ODBC) with SQL Server, but I assume that can handle
    multiple resultsets ...
    If you're using SQLOLEDB, well, I've never run into this error message ...

    >
    > Set RS2 = RS.NextRecordset
    >
    > Basically, because I need to keep the "course" recordset alive, I've


    Well, you could use GetRows to put the first one into an array ... nothing
    says you HAVE to loop through a recordset, does it?


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 2, 2006
    #14
  15. Rob Meade

    Rob Meade Guest

    "Bob Barrows [MVP]" wrote ...

    > What provider are you using? You're using SQL Server, right? I've never
    > used the MSDASQL provider (ODBC) with SQL Server, but I assume that can
    > handle multiple resultsets ...
    > If you're using SQLOLEDB, well, I've never run into this error message ...


    My connection string stuff goes like this:

    Set objConnection = Server.CreateObject("ADODB.Connection")
    objConnection.Open "Provider=SQLOLEDB;Data Source=TITUS;User
    ID=<userid>;Password=<password>;Initial Catalog=ParasolTraining"
    Set objCommand = Server.CreateObject("ADODB.Command")
    Set RS = Server.CreateObject("ADODB.Recordset")
    objCommand.CommandText = SQL
    objCommand.CommandType = adCmdText
    Set objCommand.ActiveConnection = objConnection
    RS.Open objCommand,,adOpenKeySet, adLockOptimistic

    I don't really know what the adOpenKeySet/ adLockOptimistic stuff does (I
    never really have) - could it be anything to do with that?

    > Well, you could use GetRows to put the first one into an array ... nothing
    > says you HAVE to loop through a recordset, does it?


    Wouldn't I have the same problem though Bob? ie, populating "anything" from
    a recordset it cant get? Or did I miss a step here?

    Thanks for any further help

    Rob
     
    Rob Meade, Jun 3, 2006
    #15
  16. Rob Meade wrote:
    > "Bob Barrows [MVP]" wrote ...
    >
    >> What provider are you using? You're using SQL Server, right? I've
    >> never used the MSDASQL provider (ODBC) with SQL Server, but I assume
    >> that can handle multiple resultsets ...
    >> If you're using SQLOLEDB, well, I've never run into this error
    >> message ...

    >
    > My connection string stuff goes like this:
    >
    > Set objConnection = Server.CreateObject("ADODB.Connection")
    > objConnection.Open "Provider=SQLOLEDB;Data Source=TITUS;User
    > ID=<userid>;Password=<password>;Initial Catalog=ParasolTraining"
    > Set objCommand = Server.CreateObject("ADODB.Command")
    > Set RS = Server.CreateObject("ADODB.Recordset")
    > objCommand.CommandText = SQL
    > objCommand.CommandType = adCmdText
    > Set objCommand.ActiveConnection = objConnection


    Nothing to do with your problem, but I'm not sure why you are bothering with
    an explicit Command object here - you aren't passing any parameters that I
    can see. And why use adCmdText when calling a stored procedure? Use
    adCmdStoredProc in this case.

    Oh! Are you concatenating the parameter values into the SQL variable? IMO,
    that's a bad idea. See the text at the end of this message for my reasoning.


    > RS.Open objCommand,,adOpenKeySet, adLockOptimistic
    >
    > I don't really know what the adOpenKeySet/ adLockOptimistic stuff
    > does (I never really have) -


    Time to learn :)
    http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstcursortypeenum.asp

    http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthnextrec.asp

    I probably would not use a keyset cursor in this situation. Actually I would
    not use anything but either a server-side forward-only cursor (most likely)
    or a client-side static cursor (rare - only if I need bookmark support -
    http://msdn.microsoft.com/library/en-us/ado270/htm/mdprobookmark.asp - very
    rare) in ASP. I am never intending to have a cursor open long enough to care
    about changes made by other users, which is the main reason for using
    dynamic or keyset cursors.

    > could it be anything to do with that?


    I don't think so, but I've never tested it. Try testing using a simple
    forwardonly cursor:

    Set RS = Server.CreateObject("ADODB.Recordset")
    objConnection.NameOfProcedure Parm1,...,ParmN,RS
    if not rs.eof then arCourses = rs.GetRows
    Set rs=rs.NextRecordset
    if not rs.eof then arFeatures=rs.GetRows
    etc.


    >
    >> Well, you could use GetRows to put the first one into an array ...
    >> nothing says you HAVE to loop through a recordset, does it?

    >
    > Wouldn't I have the same problem though Bob? ie, populating
    > "anything" from a recordset it cant get?


    I was not addressing your inability to use NextRecordset. I was addressing
    your assertion that you needed a second recordset object.

    -------------------------------------------------------------------------------------------
    There are several ways to pass parameter values to stored procedures:


    1. Use the technique described here: http://www.aspfaq.com/show.asp?id=2201


    Personally, I don't like this technique since:
    a. You have to worry about preventing hackers from injecting SQL into your
    code (there are ways to prevent this - see the SQL Injection FAQ at
    www.sqlsecurity.com)


    b. You have to correctly delimit your parameter values, just as if you were
    creating a dynamic SQL statement (actually, that is exactly what you are
    doing here). You also have to correctly handle string values that contain
    literal characters that are normally used as delimiters. While I've done
    this enough times so that it is second nature to me now, in the beginning
    this was the largest stumbling block to my learning how to create strings
    containing dynamic SQL statements.


    c. There is some performance-impairing overhead involved with both the
    concatenation of the SQL statement that ultimately runs the stored
    procedure, and the preparation of the statement on the SQL Server box, which
    happens before the statement is actually executed.


    d. It forces you to return data only by recordsets: no output or return
    values can be used with this technique. Recordsets require substantial
    resources, both on the SQL Server which has to assemble the resultset and
    pass it back to the client, and on the web server which has to marshal the
    resultset and transform it into an ADO recordset. This is a lot of overhead
    when we're talking about returning one or two values to the client.


    However, a lot of people do like this technique because:
    a. They have no problem knowing when and how to concatenate delimiters into
    the SQL statement, and how to handle string parameters that contain literal
    characters that are normally used as delimiters
    b. They have taken the necessary steps to prevent SQL Injection
    c. You can assign the statement to a variable and, if there's an error
    during the debug process, you can response.write the variable to see the
    actual statement being sent to the SQL Server. If the statement has been
    created correctly, you can copy and paste it from the browser window into
    Query Analyzer and further debug it
    d. They are aware of the performance hit, and consider it to be too minor to
    worry about. (To be fair, in many cases, this perfomance hit is relatively
    minor)


    The alternatives I prefer completely eliminate objection b from above.


    1. If you have output parameters, or you are interested in using the Return
    value from your procedure, use an explicit ADO Command object. Now, this can
    be tricky, especially if you do it the correct way (manually create the
    Parameters collection using CreateParameter instead of using
    Parameters.Refresh which involves an extra time-consuming trip to the
    database). However, there are many stored procedure code generators out
    there that vastly simplify this process, including the one I wrote which is
    available here:
    http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

    2. The technique I use most often is the "procedure-as-connection-method"
    technique. With ADO 2.5 and higher, stored procedures can be called as if
    they were native methods of the connection object, like this:
    conn.MyProcedure parmval1,...,parmvalN
    This completely avoids the need to worry about delimiters, literal or
    otherwise. Plus it turns out that this technique also causes the procedure
    to be executed in a very efficient manner on the SQL Server box.

    You can also use this technique if your procedure returns a recordset:
    set rs=server.createobject("adodb.recordset")
    'optionally, set the cursor location and type properties
    conn.MyProcedure parmval1,...,parmvalN, rs

    HTH,
    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 3, 2006
    #16
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. valentin tihomirov
    Replies:
    9
    Views:
    2,500
    Ray Andraka
    Apr 15, 2004
  2. msnews.microsoft.com
    Replies:
    2
    Views:
    2,324
    Brock Allen
    Mar 29, 2005
  3. Steve Green

    Better Approach to Code?

    Steve Green, Mar 25, 2005, in forum: Java
    Replies:
    2
    Views:
    278
    Tim Tyler
    Mar 27, 2005
  4. Amy G
    Replies:
    3
    Views:
    299
    Amy G
    Dec 27, 2003
  5. Hitesh Joshi
    Replies:
    1
    Views:
    262
    Sybren Stuvel
    Jul 19, 2006
Loading...

Share This Page