?
Solved

ASP Script time out

Posted on 2005-04-13
17
Medium Priority
?
2,508 Views
Last Modified: 2012-05-05
Hi

I have a code that adds discount coupons to the database
this code works ok if adding some coupons but when i have 200 000 coupons to add, i get an ASP script time out

I change the time out value in IIS, but the problem has not been resolved
so i created a code that helped me adding almost 130 000 coupons but after that i get the asp time out error

here is the code
=============================================================
dim closecount, f, fwritten

Sub addcoupontodatabase
dim i, rs, dbc, sql
shopopendatabase dbc
for i = 1 to 200000
      
Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.open "coupons", dbc, adOpenKeyset, adLockOptimistic, adCmdTable
      objRS.AddNew

      objRS("couponname")= couponname
      objRS("couponpercent")= (replace(couponpercent,",",".")/100)
      objRS("couponstartdate")= datenormalize(couponstartdate)
      objRS("couponEnddate")= datenormalize(couponEnddate)
 
                objRS.Update
      objRS.movenext  
      HandleTimeout            
next
objRS.close
set objRS = nothing
shopclosedatabase dbc  
response.write "<br><br><b>Coupons have been created successfully</b>"
end sub

Sub HandleTimeout
closecount = closecount+1
if closecount >= 100 then
  closecount = 0
  response.flush
end if    
f = f + 1
IF f > 100 then
      f=0
       Response.Write("<BR>")
       fwritten = false
end if
if ((f mod 10) = 0) then
       Response.Write(".")
       Response.Flush
       fwritten = true
end if
end sub

=============================================================

does anyone have any idea how to optimize this code so that i can create millions of coupons without hassel ?
thx

0
Comment
Question by:humer2000
  • 6
  • 4
  • 3
  • +3
17 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 13775744
add <%ScriptTimeout=1800%>  at the top of the page
0
 
LVL 35

Expert Comment

by:YZlat
ID: 13775752
<% Server.ScriptTimeout =1800%>
0
 
LVL 1

Author Comment

by:humer2000
ID: 13775780
héhéhé
i have already tried this
it does not solve the problem

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Expert Comment

by:kingsfan76
ID: 13776067
try to improve your loop a little bit and see if it helps:

1. instead using the open recordset with addnew and update method, just excute an insert query should be faster.  also make it a stored procedure if your database supports (i.e. MS SQL)
2.  remove the function call for handletimeout unless it's absolutely necessary (when you are looping so many times any line can adds up substantially)

Sub addcoupontodatabase
dim i, rs, dbc, sql
shopopendatabase dbc
for i = 1 to 200000
    sql = "INSERT INTO coupons (couponname, couponpercent, conponstartdate, couponEndDate) " _
     & "VALUES ('" & couponname & "', " & (replace(couponpercent,",",".")/100) & ", '" & datenormalize(couponstartdate)  & "','" & datenormalize(couponEnddate) & "')"
      dbc.execute(sql)
next

shopclosedatabase dbc  
response.write "<br><br><b>Coupons have been created successfully</b>"
end sub
0
 
LVL 1

Author Comment

by:humer2000
ID: 13776229
no good
0
 
LVL 11

Expert Comment

by:ajaikumarr
ID: 13776291
Hai,

Alternate work around.

Pass the for loop start digit on query string.

after looping 1000 records resubmit the form and pass the loop start digit with 1000 incremented... so that the page will not get timed out.

Bye
Ajai
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 13776338
still timing out?
i don't know how long each insert statement takes, assume it take 10ms to execute, 200000x10ms = 2000 sec, that's an awful lot of time.  might want to set timeout value to a large value, like 7200.  
btw is each couple the same?  i.e. are you trying to insert 200000 line of the same record?
0
 
LVL 1

Author Comment

by:humer2000
ID: 13776416
yes kingsfan76  i am inserting the same record 2000000 times

i don't understant what you mean ajaikumarr
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 13776481
Another thing to check would be how many records are getting inserted or updated before the timeout occurs. Once you know that, adjust your script timeout value accordingly.
0
 
LVL 11

Expert Comment

by:ajaikumarr
ID: 13776488
Hai,

Try this code.

'
'Save this code as test.asp
'
<form name="frmTest" id="frmTest" method="post">
<%
dim closecount, f, fwritten

dim ivlStartingVal
ivlStartingVal = Request.querystring("StartingValue")
If Trim(ivlStartingVal) = "" Then ivlStartingVal = 1
Call addcoupontodatabase()


Sub addcoupontodatabase
      dim i, rs, dbc, sql
      shopopendatabase dbc
      for i = ivlStartingVal to ivlStartingVal + 1000
            Set objRS = Server.CreateObject("ADODB.Recordset")
                 objRS.open "coupons", dbc, adOpenKeyset, adLockOptimistic, adCmdTable
                 objRS.AddNew

                 objRS("couponname")= couponname
                 objRS("couponpercent")= (replace(couponpercent,",",".")/100)
                 objRS("couponstartdate")= datenormalize(couponstartdate)
                 objRS("couponEnddate")= datenormalize(couponEnddate)
 
                objRS.Update
                 objRS.movenext  
      next
      objRS.close
      set objRS = nothing
      shopclosedatabase dbc  

      if ivlStartingVal = 200000 Then
            response.write "<br><br><b>Coupons have been created successfully</b>"
      Else
            ivlStartingVal = ivlStartingVal + 1
            %>
            <script language=javascript>
                  document.frmTest.action = "./Test.asp?StartingValue=<%=ivlStartingVal%>"
                  document.frmTest.submit();
            </script>
            <%
      End If
end sub
%>
</form>

Bye
Ajai
0
 
LVL 11

Expert Comment

by:ajaikumarr
ID: 13776494
Hai,

on the above code replace

if ivlStartingVal = 200000 Then
With
if ivlStartingVal >= 200000 Then

Bye
Ajai
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 13776662
since you are inserting the same record, you can also try something like this:
-----------------------------------------------
Sub addcoupontodatabase
   dim i, rs, dbc, sql
   shopopendatabase dbc
   for i = 1 to 10000  'insert 10000 times
      sql = "INSERT INTO coupons (couponname, couponpercent, conponstartdate, couponEndDate) " _
       & "VALUES ('" & couponname & "', " & (replace(couponpercent,",",".")/100) & ", '" & datenormalize(couponstartdate)  & "','" & datenormalize(couponEnddate) & "')"
        dbc.execute(sql)
   next

   'now you have 190000 to go, divide by 10000 you have 19 to go
   for i = 1 to 19
       sql = "INSERT INTO coupons (couponname, couponpercent, conponstartdate, couponEndDate) SELECT TOP 10000 couponname, couponpercent, conponstartdate, couponEndDate FROM coupons " _
       & "WHERE couponname='" & couponname & "' AND couponpercent=" & replace(couponpercent,",",".")/100 & " AND conponstartdate='" & datenormalize(couponstartdate) & "' AND couponEndDate='" & datenormalize(couponEnddate) & "'"
      dbc.execute(sql)
   next
--------------------------------------

What i was doing was to use a INSERT...SELECT... statement.  I am not sure how much speed difference it makes in term of database operation, but it sure will save you many many trips to the database (which can add up).  you can also change the number, i was just using 10000 as an example.
   shopclosedatabase dbc  
   response.write "<br><br><b>Coupons have been created successfully</b>"
end sub
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 13776672
opps... i inserted my comments before the "end sub"... well...
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 13776753
you can also use refresh to prevent page from timeout, the logic is like this:

<%
startIndex = Request.QueryString("StartIndex")
if startIndex = "" then startIndex = 1

endIndex = Request.QueryString("EndIndex")
if endIndex = "" then endIndex  = 10000

for i = startIndex to endIndex
    'call insert statement
next

startIndex = endIndex + 1    'set new start index for loop
endIndex = startIndex + 9999   'set new end index for loop

if startIndex < 2000000 then   'redirect to itself
    response.redirect "thisSamePage.asp?StartIndex=" & startIndex & "&EndIndex=" & endIndex
else
    response.write("Finished")
end if
%>

----
you might need to add the values you wanted to insert into the querystring as well.
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 13776776
one modification for my previous post:

startIndex = Request.QueryString("StartIndex")
if startIndex = "" then
   startIndex = 1
else
   startIndex  = clng(startIndex)
end if

endIndex = Request.QueryString("EndIndex")
if endIndex = "" then
   endIndex  = 10000
else
   endIndex  = clng(endIndex)
end if

-----

0
 
LVL 10

Accepted Solution

by:
thefritterfatboy earned 2000 total points
ID: 13779842
Try upping your server timeout AND taking the recordset.open command out of the loop. This will stop the ASP from opening the connection for every time you add a row.

=============================================================
Server.ScriptTimeout =1800 ' or more... if you like
dim closecount, f, fwritten

Sub addcoupontodatabase
dim i, rs, dbc, sql
shopopendatabase dbc
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open "coupons", dbc, adOpenKeyset, adLockOptimistic, adCmdTable
for i = 1 to 200000
     objRS.AddNew
     objRS("couponname")= couponname
     objRS("couponpercent")= (replace(couponpercent,",",".")/100)
     objRS("couponstartdate")= datenormalize(couponstartdate)
     objRS("couponEnddate")= datenormalize(couponEnddate)
     objRS.Update
     objRS.movenext  
     HandleTimeout          
next
objRS.close
set objRS = nothing
shopclosedatabase dbc  
response.write "<br><br><b>Coupons have been created successfully</b>"
end sub

Sub HandleTimeout
closecount = closecount+1
if closecount >= 100 then
  closecount = 0
  response.flush
end if    
f = f + 1
IF f > 100 then
     f=0
       Response.Write("<BR>")
       fwritten = false
end if
if ((f mod 10) = 0) then
       Response.Write(".")
       Response.Flush
       fwritten = true
end if
end sub

=============================================================
0
 
LVL 1

Author Comment

by:humer2000
ID: 13780868
great thefritterfatboy for the tip

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question