ASP Script time out

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

LVL 1
humer2000Asked:
Who is Participating?
 
thefritterfatboyConnect With a Mentor Commented:
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
 
YZlatCommented:
add <%ScriptTimeout=1800%>  at the top of the page
0
 
YZlatCommented:
<% Server.ScriptTimeout =1800%>
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
humer2000Author Commented:
héhéhé
i have already tried this
it does not solve the problem

0
 
kingsfan76Commented:
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
 
humer2000Author Commented:
no good
0
 
ajaikumarrCommented:
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
 
kingsfan76Commented:
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
 
humer2000Author Commented:
yes kingsfan76  i am inserting the same record 2000000 times

i don't understant what you mean ajaikumarr
0
 
SquareHeadCommented:
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
 
ajaikumarrCommented:
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
 
ajaikumarrCommented:
Hai,

on the above code replace

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

Bye
Ajai
0
 
kingsfan76Commented:
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
 
kingsfan76Commented:
opps... i inserted my comments before the "end sub"... well...
0
 
kingsfan76Commented:
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
 
kingsfan76Commented:
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
 
humer2000Author Commented:
great thefritterfatboy for the tip

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.