Simple Dynamic Update Loop

[jcale4]

Hello!

I have an ASP page that selects a recordset of dates, times, operators, etc. from a DB2 DB. This recordset (on average) consists of about 400,000 records. I have to loop through this recordset, perform some checks against the data, and while looping through, run an update on each row of data, setting a field to 0 or calculating an elapsed time and inserting that particular time. (the actual data doesnt really matter). I had to set the server.scripttimeout to 3600, but still cannot get it to complete the processing. I commented out the UPDATE execution and instead just wrote out the data that WOULD be written to the DB, and this took only seconds to process, so the bottleneck is the execution of the UPDATE statements. Is it possible for me to somehow open an "UPDATE SET" and insert data rather than sending a statement to the DB to be compiled and executed each time? Here is the code:

if elapsedUPDATE = "yes" then

 recUPD = "SELECT entrydate, entrytime, sup_date, sup_time, orig_op, sup_id, id from tblri22 order by entrydate, entrytime, orig_op"
	
	Set recordset = Server.CreateObject("ADODB.Recordset")
	recordset.CursorLocation = adUseClient
	recordset.Open recUPD, rtl


	while not recordset.eof
		if not recordset.eof then
		recordset.movenext
	
		f_e_date = recordset("entrydate")
		orig_TIME = recordset("entrytime")
		f_e_time = trim(right(orig_TIME,11))
		f_opid = trim(recordset("orig_op"))
		f_s_date = recordset("sup_date")
		f_s_time = trim(right(recordset("sup_time"),11))
		id = trim(recordset("ID"))
		sup_id = trim(recordset("sup_id"))
		
		
		p = f_s_date & " " & f_s_time
		end if
	
		recordset.moveprevious
	
		if f_e_date = recordset("entrydate") and f_e_time = trim(right(recordset("entrytime"),11)) and f_opid = trim(recordset("orig_op")) and sup_id <> recordset("sup_id") then
			v = recordset("sup_date")
			y = trim(right(recordset("sup_time"),11))
			
			t = v & " " & y
			
			elapsed = DateDiff("s", t, p)
			
			if not recordset.eof then
				recordset.movenext
			end if	
			
			rtl.execute("UPDATE tblri22 set elapsedtime = " & elapsed & " WHERE ID = " & id)
			response.Write("would update " & id & " elapsed to " & elapsed)
		else
			response.Write("would update " & id & " to 0 ")
			rtl.execute("UPDATE tblri22 set elapsedtime = 0 WHERE ID = " & id)
			
			if not recordset.eof then
				recordset.movenext
			end if
		
		end if
	
	wend
		response.Write("COMPLETE")
	recordset.close
	set recordset = nothing
	
end if

 

[jcale4]

Anyone out there?