report optimization, new to stored procedures

Results 1 to 2 of 2

Thread: report optimization, new to stored procedures

  1. #1
    Join Date
    Dec 1969

    Default report optimization, new to stored procedures

    hello all-<BR><BR>im fairly new to stored procedures and was looking to optimize a report that is currently being used. <BR><BR>The report displays unique affiliates who have generated traffic sent to the website, the counts of said traffic (broken into total hit counts and Unique users), and any sales generated by the traffic or the registration of a user from said traffic.. <BR><BR>the report currently uses asp to call the first stored procedure<BR>(spAX_LoadList) and the uses a while loop to iterate through the IDs and load the Sales counts ( using another stored procedure ). <BR><BR>this solution although it works, is slow and will not lend it self to much scaling. as it is the report takes a few minutes to load ( 2-3 mins) and thats only with tens of thousands of hits to report on.<BR><BR>This is the main procedure that loads the daily affiliates and their users visitor count.. <BR><BR>---------------------------------------------------<BR><BR>SELECT A.AX_ID as aID, <BR>COUNT(A.WEB_COUNT) AS vtHits, <BR>COUNT DISTINCT(A.USER_CID))AS vtCnt<BR>FROM AFF_TRACKS A INNER JOIN<BR>AFX_MEMBERS AM ON <BR>(A.AX_ID = AM.AX_ID) <BR>WHERE A.AX_DTime between @adate AND @edate<BR>GROUP BY A.AX_ID<BR>ORDER BY A.AX_ID<BR><BR>-------------------------------------------------------<BR><BR>&#062;&#062; results are used in a while loop<BR>while not eof<BR><BR><BR>call stored procedure to count affiliates sales ( if any for the day )<BR><BR>( sql used in stored procedure )<BR><BR>Select count(S.C_TOR_ID) as Sales <BR>from SALES_CONFIRMED S<BR>Where S.AX_ID = @aID<BR><BR><BR><BR><BR><BR>results = results & results <BR><BR><BR>movenext <BR>wend <BR><BR><BR>response.write results<BR><BR>---------------------------------------------------------<BR><BR>Ive heard mention of cursors, but I really dont know much about this. <BR><BR>What should/can be done to optimize this? Any help would be appreciated

  2. #2
    Join Date
    Dec 1969

    Default Nothing to do with SP's

    It wouldn&#039;t matter whether you made this query in a Stored Procedure or you did it via a standard SQL query.<BR><BR>You *NEVER* want to put *ANY* query inside the DO UNTIL RS.EOF loop of another query.<BR><BR>If you are doing that, you know you have screwed up, immediately. You need, instead, to join another table to the outer query.<BR><BR>So rethink that whole process and figure out how to make do with one single query. One non-SP query will be tons faster than a nested loop thing, even if all the nested queries are SP&#039;s.<BR><BR>

Posting Permissions

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