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

 

[Sheco]

This is gonna throw an eror on the next to last record...

    while not recordset.eof
        if not recordset.eof then
        [red]recordset.movenext[/red]
    
        [highlight]f_e_date = recordset("entrydate")[/highlight]
        [highlight]orig_TIME = recordset("entrytime")[/highlight]
        [ ... ]

 

[jcale4]

Yes,, and I can fix that.. not so much worried about errors, since this is actually a process that i will run each night and small manual fixes are ok for now... and i know what you'll say,,, "why aren't you using a stored procedure for this"... answer: I dont know, nor do i have time to learn PL/SQL.

 

[Sheco]

Why does it have to be in ASP?

If you do it in plain VB or as a VBScript (.vbs) then you won't need to worry about the timeout.

If you are gonna run it as an overnight process it would even be easier to schedule as an exe or vbs.

 

[vongrunt]

You can make it faster - by eliminating movePrevious call (cache last record) and removing unnecessary code like:

while not recordset.eof
        [b]if not recordset.eof then[/b]

But... VB, VBS, C#, whatever. This is client-side looping and will take hours to finish. Same thing in SQL would probably finish within a few seconds.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Sheco]

True, even if you trim all the computational fat it is gonna take a bit of time to update 400,000 records one at a time from a cursor like this.

 

[jcale4]

OK, how do i go about scheduling it as an overnight process as an exe? Do i need visual studio to compile an .exe? Asside from writing a procedure, is this the best solution?

Thank you for everyone's input!

 

[Sheco]

Stored proc would execute the fastest.

If you are gonna run it in the middle of the night that might not matter so much.

You can use the built in task scheduler to fire off an exe or vbs every night.

 

[jcale4]

Can anyone provide any clues on how to write a SQL procedure for the equivalent ASP code above for DB2?

 

[DotNetGnat]

Did you mean something like this:

CREATE proc sp_Operators
@myID int 
As

UPDATE tblri22 set elapsedtime = 0 WHERE ID = @myID

and in ASP page you call the procedure...something like...

Connection.Execute "sp_Operators " & request("myid")

-DNG

 

[jcale4]

Well,, that may work, but i actually meant possibly doing the entire processing with a procedure... i will definitely try this to see if calling the procedure from ASP is faster though. It all boils down to speed,, my ASP script works perfectly,, just takes a couple HOURS to complete.

 

[DotNetGnat]

couple hours ??


-DNG

 

[DotNetGnat]

can you explain what actually you are trying to do...

may be you can write a UDF for this whole thing..

-DNG

 

[jcale4]

OK.. i think i've figured out the procedure,,, I just have one problem... i need to do some date calculations. I have:

entrydate date,
entrytime time,
sup_date date,
sup_time time

This makes calculating the difference between the entrydate/entrytime & sup_date/sup_time very difficult. I know in SQL Server you can cast a date and time to a timestamp

CAST(entrydate + entrytime as timestamp)

then use timestampdiff() to arrive at the exact Y/M/D/H/M/S, etc., but i cant seem to make that work in DB2, and cant find any documentation that supports casting a date and time into one datatype. If this isn't possible, can anyone think of another way to calculate those?

j

 

[DotNetGnat]

take a look at this link:

http://www.dbazine.com/db2/db2-mfarticles/mullins-qa

-DNG

 

[jcale4]

BEAUTIFUL!!!!!!!!

I think I can handle it from here... I may be back to ask you all more questions, but thank you EVERYONE for your help!!

 

[DotNetGnat]

Great...glad to be of help...post back if you have any other questions...

-DNG

 

[jcale4]

OK.. Not much, but here's my procedure so far (keep in mind, this is my first one):

CREATE PROCEDURE CSC_EDIT.ssp_elap_t (  )
    LANGUAGE SQL

    BEGIN
    DECLARE e_stamp timestamp;
    DECLARE e_opid char(5);
    DECLARE s_stamp timestamp;
    DECLARE v_id integer;
    DECLARE s_opid char(5);
    DECLARE at_end INT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    
    DECLARE C1 CURSOR FOR
        SELECT orig_op, id, sup_id, timestamp(substr(char(entrydate,USA),7,4) || '-' || substr(char(entrydate,USA),1,2) || '-' || substr(char(entrydate,USA),4,2) || '.' || substr(char(entrytime),1,2) || '.' || substr(char(entrytime),4,2) || '.' || substr(char(entrytime),7,2) || '.00') as orig, timestamp(substr(char(sup_date,USA),7,4) || '-' || substr(char(sup_date,USA),1,2) || '-' || substr(char(sup_date,USA),4,2) || '.' || substr(char(sup_time),1,2) || '.' || substr(char(sup_time),4,2) || '.' || substr(char(sup_time),7,2) || '.00') as sup
        FROM tblri22;
    DECLARE CONTINUE HANDLER FOR not_found 
        SET at_end = 1;

    OPEN C1;
    FETCH C1 INTO e_opid, v_id, s_opid, e_stamp, s_stamp;
    WHILE at_end = 0 DO
        
    END WHILE;
    CLOSE C1;
END

A little background:

Here is a sampling of data from my tblri22 table-

ORIG_OP ENTRYDATE ENTRYTIME  SUP_ID SUP_DATE   SUP_TIME
------- --------- ---------- ------ --------   ---------
L5T     05/10/2005 11:26:35  L5T    05/10/2005 11:26:35
L5T     05/11/2005 07:54:01  L5T    05/11/2005 07:54:01
D4B     06/03/2005 10:15:35  D4B    06/03/2005 10:15:35
D4B     06/03/2005 10:15:35  F90    06/06/2005 13:36:53
VT1     04/21/2005 10:50:39  VT1    04/21/2005 10:50:39
VT1     04/21/2005 10:50:39  F90    04/21/2005 15:05:04
VT1     04/21/2005 10:50:39  VT1    04/25/2005 13:43:48
D4B     04/25/2005 08:51:20  D4B    04/25/2005 08:51:20
D4B     04/25/2005 08:51:20  F90    04/27/2005 08:21:15
D4B     04/25/2005 08:51:20  F90    04/27/2005 08:21:15
D4B     04/25/2005 08:51:20  D4B    05/13/2005 06:39:38
D4B     04/26/2005 09:06:53  D4B    04/26/2005 09:06:53
D4B     04/26/2005 09:06:53  F90    04/27/2005 08:21:54
D4B     04/26/2005 09:06:53  F90    04/27/2005 08:21:54
D4B     04/26/2005 09:30:23  D4B    04/26/2005 09:30:23
D4B     04/26/2005 09:30:23  F90    04/27/2005 08:22:12
D4B     04/26/2005 09:30:23  F90    04/27/2005 08:22:12

Each of these records are entries into a mainframe that also include comments. The comments can obviously be submitted by different operators. Now, an operator can type several lines of comments and only until they hit "ENTER" does the date/time get posted. So, there could be the same date/time/operator on multiple lines. These records all tie back to individual rows in another table. So, there could be 200 records in the tblri22 table that key back to tblri21 by 3 keys: entrydate, entrytime, orig_op. the sup_date, sup_time, sup_id are the additional entries to the original track. What i need to do is calculate the elapsed time for each operator, grouped by the track (entrydate, entrytime, orig_op). There may be only 1 line also. So, in my procedure, within the while, i need to say move to the next record, store the sup_date, sup_time, sup_id then move back a record and check to see if it is part of the same original trac (entrydate + entrytime + orig_op). If so, then run a timestampdiff between the sup_date + suptime of the current record and the stored sup_date and sup_time, then update the elapsed_time field to the timestampdiff. If its not part of the same record, then make elapsed time = 0.

Sorry for the long post, just not sure how to move back and forth between the records and not sure how to clear out the variables for the next record.... can you guys help?

Thanks!!

 

[jcale4]

OK, now dont laugh at my poor excuse for a solution. Here is what I have so far, when i run it,, it just processes for hours,, probably an infinite loop somewhere?:

BEGIN
    
    DECLARE orig timestamp;
    DECLARE sup timestamp;
    DECLARE chkop char(5);
    DECLARE chkstamp timestamp;
    DECLARE s_stamp timestamp;
    DECLARE e_opid char(5);
    DECLARE v_id integer;
    DECLARE s_opid char(5);
    DECLARE at_end INT DEFAULT 0;
    DECLARE new_diff integer;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
        
    DECLARE C1 CURSOR FOR
        SELECT orig_op, timestamp(substr(char(entrydate,USA),7,4) || '-' || substr(char(entrydate,USA),1,2) || '-' || substr(char(entrydate,USA),4,2) || '-' || substr(char(entrytime),1,2) || '.' || substr(char(entrytime),4,2) || '.' || substr(char(entrytime),7,2) || '.00') as orig
        FROM tblri22;
    
    OPEN C1;
    FETCH C1 INTO chkop, chkstamp;   


    FOR v_row AS SELECT orig_op, id, sup_id, timestamp(substr(char(entrydate,USA),7,4) || '-' || substr(char(entrydate,USA),1,2) || '-' || substr(char(entrydate,USA),4,2) || '-' || substr(char(entrytime),1,2) || '.' || substr(char(entrytime),4,2) || '.' || substr(char(entrytime),7,2) || '.00') as orig, timestamp(substr(char(sup_date,USA),7,4) || '-' || substr(char(sup_date,USA),1,2) || '-' || substr(char(sup_date,USA),4,2) || '-' || substr(char(sup_time),1,2) || '.' || substr(char(sup_time),4,2) || '.' || substr(char(sup_time),7,2) || '.00') as sup
                 FROM tblri22

    DO
       SET s_stamp = v_row.sup;
       SET v_id = v_row.id; 

        IF (chkop = v_row.orig_op) AND (chkstamp = v_row.orig) THEN

                set new_diff = TIMESTAMPDIFF(2, CHAR(chkstamp - s_stamp));

                UPDATE tblri22 
                SET elapsedtime = 55
                WHERE id = (v_id);
        
        ELSE

           UPDATE tblri22 
           SET elapsedtime = 0
           WHERE id = (v_id);
 
        END IF;

        SET chkop = v_row.orig_op;
        SET chkstamp = v_row.orig;
     
     END FOR;
     CLOSE C1;
END

 

[jcale4]

(In the above code,, i was just testing the update.. i was simply setting elapsedtime to an integer value for testing purposes)