Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert ASP code to a Stored Procedure

    I have a set of class modules in ASP that work just fine but in one particular case the page times out while processing the code. I can reset the IIS param to let the script run but I think it would be good to rewrite the code using SQL Server stored procedures. Not only may this speed things up, but it'll teach me how to write a stored procedure. Everyone wins!!

    However, I am not getting very far on this as I don't know what the heck I am doing. Any advise on how to write and implement stored procedures in SQL Server? (if this is not the right forum, let me know).

    The code I want to convert does the following:

    1) Open up an outer loop for a table
    2) Compare all rows in 6 other tables for matches
    3) Output any unmatched rows in the outer loop
    4) Output all associated (matched) rows found in the other 6 tables

    I have the code done up in ASP using ADO to instantiate the record sets.

    TIA
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Convert ASP code to a Stored Procedure

    Do you have a big documentation file from Microsoft called "SQL Books Online"? That should cover a lot of the nitty gritty of T-SQL; books and consultations might also be needed.

    I agree this is not the right board. (Which one is, I'm not sure... maybe Access?)

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ASP code to a Stored Procedure

    I agree completely - Stored Procedures are MUCH faster than VB/VBScript code. The only thing I would recommend is to avoid loops (cursors in SQL) when possible. You'll find that the SQL language in general (and T-SQL specifically) has some very powerful select capabilities that work very well without looping. Iterating through cursors is one of the slowest, worst performing operations in SQL Server.

    You may find some good bits of knowledge at SQL Team. I found that the learning curve for T-SQL was rather steep, but it really pays off once you learn it!

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ASP code to a Stored Procedure

    yes I have books on-line. they're scary! i want to learn how to open a table and do operations on it as one can do in access VBA or ASP, etc. and books online probably can tell me that but it's all a little dense.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert ASP code to a Stored Procedure

    I guess what you're saying here is that, instead of doing LOOP stuff I can hatch a SQL Statement that returns an array? nifty!

    Do you have some general tips on setting up a stored procedure? I have never done one. As you may gather from my thumbnail of what I want out of a stored procedure, arrays are the goal for the output.

    My ASP code is nice too because I managed to encapsulate the code in some class mods and call them on output to a table. Because it employs loops, it's easy to understand but, because it employs loops, it's expensive. Programming 101 here.

    I'll graze around on the link you recommended and see what I can figure out.

    Thanks!

Posting Permissions

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