Results 1 to 5 of 5

Thread: More T-SQL!!!

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

    More T-SQL!!!

    I basically re-wrote my ASP code using 7 Stored Procs and the performance is as bad as ever. I think part of the problem may be that this particular query involves looping thru the data and running the queries for each row. If I take out the call to the stored procs (in an ASP class module) the table pops up real quick. Another page where I am running a similar stored proc to output info does perform very quickly, so there's definately an advantage to using stored procs.

    However, just to get things going I re-wrote what I did in ASP/ADO queries to SQL Server to 7 stored procs that do the same thing. Perhaps I can make things quicker by putting all the logic in ONE stored proc....

    Here's how one of the stored procs looks:

    PROCEDURE TPR_Recs
    @ComplainantID INT
    AS
    set nocount on
    SELECT Count(ComplainantID)
    FROM sskelton.Reported_ThirdPartyResources
    WHERE ComplainantID = @ComplainantID

    What I'd like to do is run this, store the result (count =1 is EXIST) as a variable and do the same thing on the others -- basically load 6 variables and call the values out in ASP.

    Any suggestions?

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

    In particular, I am trying to test that the value passed in @ComplainantID EXISTS in a given table

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

    Re: More T-SQL!!!

    The poor performance comes because you're having to create 7 objects on the Web Server (creating objects is one of the slowest non-external operations that can be done in VBScript), then making 7 separate calls across the network to the SQL Server, then processing 7 separate results using VBScript logic.

    The best performance will come from consolidating as much logic as possible into a single Stored Procedure - as we've discussed in previous threads. You can easily declare variables in T-SQL using this syntax: DECLARE @VariableName Type (example: DECLARE @complaintCounter INT). See "DECLARE" in SQL Server Books Online (SQL BOL). You can set variables using the SELECT or SET command - similar, but used for different things.

    Your Stored Procedure might look like:

    CREATE PROCEDURE <YourProcedureName>
    @ComplaintId INT
    AS

    DECLARE @complaintCount int

    SELECT @complaintCount = COUNT(ComplaintID)
    FROM <Table1>
    WHERE ComplaintID = @ComplaintID

    SELECT @complaintCount = @complaintCount + COUNT(ComplaintID)
    FROM <Table2>
    WHERE ComplaintID = @ComplaintID

    ---and so on---

    ---Finaly, return the variable---
    SELECT @complaintCount

    Also, is there a reason you don't have CREATE in front of your procedure?

    Just remember - fewer calls to SQL, more logic processed by SQL (when appropriate)

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

    Re: More T-SQL!!!

    DECLARE, huh? looks interesting. in your example, you use ComplaintCount to add up all the results from the different SELECT statements. I'll need to output 6 different vars (which may be a count or a 1/0, whichever) and do Response.Write output depending on each var's own value. well, will play around with it.

    Why do I not have CREATE in front of the PROCEDURE? I didn't think it was necessary. CREATE is used to create a proc; once it's done it's done. no need to keep CREATEing the same stored proc once it's, errr... stored, I should think. Otherwise, wouldn't you be overwriting an existing stored proc over and over again?

    OK< I'll play with the DECLARE dealie. Looks like it might just be what I need.

    Thanks!

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

    Re: More T-SQL!!!

    Examine any of the Stored Procedures in the sample Northwind SQL database and you'll see CREATE PROCEDURE at the beginning of each. I've never tried using a Stored Procedure without CREATE, so that *could* be contributing to your speed problems.

    If you need to output multiple variables, you can capture them as OUTPUT parameters. The only catch is that you have to declare the parameters from the ASP side and add them to the Parameters collection of the command object when you call the procedure. Also, be sure to set the direction property of the parameter to Output (Input is default).

    An example would be this:

    CREATE PROCEDURE GetResults
    @someId int,
    @result1 int OUTPUT,
    @result2 int OUTPUT,
    @result3 int OUTPUT,
    @result4 int OUTPUT
    AS

    SELECT @result1 = COUNT(*) FROM Table1 WHERE IDField = @someId

    SELECT @result2 = COUNT(*) FROM Table2 WHERE IDField = @someId

    SELECT @result3 = COUNT(*) FROM Table3 WHERE IDField = @someId

    SELECT @result4 = COUNT(*) FROM Table4 WHERE IDField = @someId


    You will find LOTS of info on DECLARE and OUTPUT parameters in the SQL Books Online or from Google.

  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: More T-SQL!!!

    Well, things seem to be getting closer...

    here's a stored proc that seems to do most of what I want:

    PROCEDURE ExistingRecords
    @ComplainantID int


    AS

    DECLARE @Provider_Recs int
    DECLARE @Recipient_Recs int
    DECLARE @StateEmp_Recs int
    DECLARE @TPR_Recs int
    DECLARE @TYC_Recs int
    DECLARE @BVS_Recs int

    set nocount on

    SELECT @Provider_Recs = Count(ComplainantID)
    FROM sskelton.Reported_MPI_Audit
    WHERE ComplainantID = @ComplainantID

    SELECT @Provider_Recs Provider

    SELECT @Recipient_Recs = Count(ComplainantID)
    FROM sskelton.Reported_Limited_Gen_Inv
    WHERE ComplainantID = @ComplainantID

    SELECT @Recipient_Recs Recipient

    SELECT @StateEmp_Recs = Count(ComplainantID)
    FROM sskelton.Reported_StateEmployee
    WHERE ComplainantID = @ComplainantID

    SELECT @StateEmp_Recs State_Employee

    SELECT @TPR_Recs = Count(ComplainantID)
    FROM sskelton.Reported_ThirdPartyResources
    WHERE ComplainantID = @ComplainantID

    SELECT @TPR_Recs TPR

    SELECT @TYC_Recs = Count(ComplainantID)
    FROM sskelton.Reported_TexasYouthCamp
    WHERE ComplainantID = @ComplainantID

    SELECT @TYC_Recs TYC

    SELECT @BVS_Recs = Count(ComplainantID)
    FROM sskelton.Reported_BVS
    WHERE ComplainantID = @ComplainantID

    SELECT @BVS_Recs BVS

    When you do Open in QA and load a variable for ComplainantID you get an 6 outputs (or columns) but on the ASP Side I haven't had much luck exctracting the results data. and setting the vars as OUTPUT generates an error when running the code.

    At least it *looks* nice and clean... so, some progress! <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

Posting Permissions

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