Re: ASP, looping, and stored procedures.... error '800a0bb9' ...

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 11/30/04 

Date: Tue, 30 Nov 2004 13:51:29 -0500

Beau wrote:
> Ok, the joins made it work a bit faster. There are 8 SP calls on the
> page PER loop. The looping goes by month from and to dates selected
> by the user. I am using alot of data and a large date range on a
> single test box. (specs unknown)
>
> The page also has to do a little FSO at the beginning to delete any
> previous .gif's in the output folder. (the page uses a graphing
> component to draw out all the data collected/processed by the SP's
> and ASP code.
>
> In pure ASP the page takes about 15 seconds. With the SP's and joins
> in the SP's that you suggested and graciously helped me with, the
> page run time is down to 10-11 seconds.(for that particular call,
> date range, client data, etc. (Keeping test params the same each run.)
>
> Anything else I can do to speed this up?

It depends on where your bottlenecks are. You need to quantify where the
time is being consumed. You can use this type of code to determine this:

dim t
t=now
'do some work
response.write "this bit took " & datediff("ms",t,now)/1000 & " sec.<BR>"
'do some more
'etc.

If your bottleneck turns out to be occurring in the ASP code itself rather
than retrieving the results from the database, you might think about putting
all this ASP looping into a stored procedure. I'm not sure what your
objective here is so I really can't get too specific.

If you are unable to think of a way to do all this processing in a single
stored procedure, then you may want to think about utilizing GetRows arrays
instead of recordset loops, which are very inefficient. See

>
> Oh, and by the way, the test box has SQL server and IIS on it. It's
> the same box.
>
> -Won't I notice an improvement in performance much more in the actual
> live environment if the SQL boxes are clustered and the IIS boxes are
> in a farm seperated? (I realize that network speed, server speed,
> other factors, etc will come into play)

I would think so, yes. SQL Server performs much better if it is given
sufficient RAM to use/

> -Do SP's reduce network traffic?

Yes. Even if a SP performs a single query, you've just sent a procedure name
and parameter values across the wire, instead of a long sql statement. So
network traffic is reduced even with a simple SP. Now, if you go ahead and
encapsulate several queries into a single SP, the reduction in network
traffic can be enormous (one call to the database, instead of calls for each
of the queries)

> I know that they share the page's
> processor burden by making the SQL box work instead of ths IIS's ASP
> doing it all. 

-- 
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.

Relevant Pages

  • Re: SQL beginner help
    ... statements that are embedded in the code(Sorta like how I can use SQL ... statements to the server)? ... with the Asp code it will decide what how to handle what the user is able ... But I still need some client side execution;/ I think I got a good picture ...
    (microsoft.public.sqlserver)
  • Re: SQL beginner help
    ... statements that are embedded in the code(Sorta like how I can use SQL ... statements to the server)? ... so its up to the asp front end to manage security(I guess its better ... But I still need some client side execution;/ I think I got a good picture ...
    (microsoft.public.sqlserver)
  • Re: forum erstellen
    ... >> abhängt ob das Forum funktioniert und weniger von den ASP Kenntnissen ... SQL Abfragen. ... mal Beiträge löschen kann, ...
    (microsoft.public.de.inetserver.iis.asp)
  • Re: Newbie: VB-ADO help
    ... I Do have some SQL knowledge and because the calculations are a bit ... the first loop ... are too numerous for memory storage. ... I do not want to deal with SQL and the like. ...
    (microsoft.public.vb.general.discussion)
  • Re: Straight SQL always put perform PL/SQL?
    ... If you cannot do it in a single SQL Statement, ... end loop; ... analyze table test1 compute statistics; ...
    (comp.databases.oracle.server)
Loading