collecting data from a child table in a set

[goldberg2727]

Payroll report takes forever to calculate. There has to be a better way. I have 40 calculated fields that use the following custom function.

'Date Created: 13-May-2009 04:39:00 PM
'Last Updated: 13-May-2009 07:31:44 PM
'Created By  : Lee Goldberg
'Updated By  : Lee Goldberg
FUNCTION get_hrs AS N (vDateWrk AS D, vEmp AS C, vDOW AS C, vType AS C, vOT as l)
	DIM tblwk as p 'worksheet
	DIM tblwse as p 'ws_employee
	DIM tblah as p 'additional hrs
	DIM qrywk as p
	DIM qryws as p
	DIM qryah as p
	DIM vRecCount as n
	DIM vRecEmp as n
	DIM vWKNum as n
	DIM vEndDate as d	
	
	vDateWrk = vDateWrk + val(vDOW)
	get_hrs = 0 
	
	
	if vtype = "Reg" .or. vtype = "Units" then
		tblwse = table.open("tblws_emp")
		tblwk = table.open("tblwksht")
			if vType = "Units" then 
				qFilter = "date = ctod('"+vDateWrk+"') .and. val(left('"+tblwk.Bill_type+"',1)) = 3"
			else if vOT = .t. .and. vType = "Reg" then
				qFilter = "date = ctod('"+vDateWrk+"') .and. OT = .t. .and. val(left('"+tblwk.Bill_type+"',1)) < 2"
			else if vOT = .f. .and. vType = "Reg" then
				qFilter = "date = ctod('"+vDateWrk+"') .and. OT = .f. .and. val(left('"+tblwk.Bill_type+"',1)) < 2"
			end if
			
			qrywk = tblwk.query_create("",qFilter,"")
			
			vRecCount = tblwk.records_get()
			
			if vRecCount > 0
				tblwk.fetch_first()
				
				while .not. tblwk.fetch_eof()
		
					vWKNum = tblwk.Wksht_num
					
					qryFilter = "emp_code = alltrim('"+vEmp+"') .and. wksht_num = val('"+vWknum+"')"   			
					qryws = tblwse.query_create("",qryFilter,"")
					vRecEmp = tblwse.records_get()
				
					if vRecEmp > 0 then
						tblwse.fetch_first()
						
						while .not. tblwse.fetch_eof()
							if vType = "reg" then
								get_hrs = get_hrs + tblwse.Qty
							else if vType = "Units"
								get_hrs = get_hrs + (tblwse.TOTAL_UNITS_PAID * (tblwse.Percentage / 100))
							end if
							tblwse.fetch_next()
						end while	    
				
					end if
				tblwk.fetch_next()	
				end while		
			end if
			
		tblwk.close()
		tblwse.close()
	else 
		'vDateWrk = vDateWrk + val(vDOW)
		vEndDate = vDateWrk + 6
		'ui_msg_box("",ctod(vDateWrk) + "   " + ctod(vEndDate))
		tblah = table.open("tbladditional_hrs_ln")
			qFilter = "date_hrs >= ctod('"+vDateWrk+"') .and. date_hrs <= ctod('"+vEndDate+"') .and. alltrim('"+vtype+"')$type_work .and. '"+vEmp+"'$emp_code"
			qryah = tblah.query_create("",qFilter,"")
			vRecCount = tblah.records_get()
			if vRecCount > 0 then
				tblah.fetch_first()
				while .not. tblah.fetch_eof()
					get_hrs = get_hrs + tblah.Hrs
					tblah.fetch_next()
				end while
			end if
		tblah.close()
	end if
END FUNCTION

I have the following set.

tblwksht
     |----------->(1:M) tblws_emp

In order to run payroll I must first filter the tblwksht table by date. So I now have all the worksheets that were created for that day. The script runs through every worksheet and then filters the tblws_emp table by worksheet number and the desired employee. It totals the hours for that employee and then moves on to the next worksheet for that day.

This happens 40 times over. I know it is slow because it is a detailed task but how can I collect the data more efficiently to reduce the load time on this report?

[Mike Wilson]

Re: collecting data from a child table in a set

Lee,
Why don't you have a payroll summary table that get's posted to on a one-by-one basis as the employee worksheet record is created/edited? Then have payroll run from the summary table instead of generating a summary at the time of payroll?

[goldberg2727]

Re: collecting data from a child table in a set

I thought about this approach but doesn't this defy the laws of database normalization since the data is already stored in the database. I was always taught to never duplicate data. Was I wrong to enforce this so rigidly?

[Tom Cone Jr]

Re: collecting data from a child table in a set

"Never" is a long time.

There are good reasons to occasionally "break the rules". This may be one of them.

If you don't want to keep running totals while the worksheets are being created, here's another idea for you:

Your code spends a lot of time opening, querying, and closing tables. You can speed this up if you rearrange things.

1) abandon the use of a function. Instead think in terms of a single script that processes all the employees and worksheets in one pass, storing the calculated results in a temporary table. This table would hold employee id's and computed results for that employee.

2) modify the set supporting the report layout to link the temp table one to one using the employee id

3) add the computed result from the temp table wherever needed in the report layout

4) to populate the temp table reverse the order of your two table set and use a one to one link. Filter the set for the relevant worksheets, while sorting them by employee id. Your script would step through the filtered records one time, accumulating totals for each employee. When the script hits a record that has a different employee id, it stops and adds a new record to the temp table... the new record contains the employee id and the computed result for the employee for whom the accumulations have been occurring. Start the accumulations again for the next employee and loop through them until the employee id continues. Keep going until you hit eof

5) the new sequence would be something like: (a) zap the temp table (b) run the script to populate the temp table (c) run the report.