asked on

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

YZlat

add <%ScriptTimeout=1800%> at the top of the page

YZlat

<% Server.ScriptTimeout =1800%>

humer2000

ASKER

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

kingsfan76

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

humer2000

ASKER

no good

ajaikumarr

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

kingsfan76

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?

humer2000

ASKER

yes kingsfan76 i am inserting the same record 2000000 times

i don't understant what you mean ajaikumarr

SquareHead

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.

ajaikumarr

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

ajaikumarr

Hai,

on the above code replace

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

Bye
Ajai

kingsfan76

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

kingsfan76

opps... i inserted my comments before the "end sub"... well...

kingsfan76

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.

kingsfan76

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

-----

ASKER CERTIFIED SOLUTION

thefritterfatboy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

humer2000

ASKER

great thefritterfatboy for the tip