Which is faster - Append or script

[Tom Patten]

I have to append about 1200 records to a table. I'm currently doing it using the append operation.

Would it run faster if I opened one table and added them to another using code like the following.

Tbl = table.open("Meds.dbf")
query.order = "" 
query.filter = "IF(var->v_Location='All Locations',.T.,Location=var->v_Location).and.NC='Med'.and.clientid<>''.and.(Date_dc={}.or.date_dc>var->v_BeginDate)" 
query.options = "I"
tbl.query_create()

tbl.fetch_first()

t2 = table.open("Med_Adm.dbf")

tbl.batch_begin()
t2.batch_begin()

While .not. tbl.fetch_eof()
	
	t2.enter_begin()
	    t2.medid = tbl.medid
	    t2.time = tbl.time
	    t2.pteid = tbl.clientid
            ... about 20 more fields	
        t2.enter_end(.T.)
		 
tbl.fetch_next() 

end while 
t2.batch_end()
tbl.batch_end()

t2.close()
tbl.close(

I know I could try it and see, but I hate to begin adding 30+ fields without some advice. Maybe someone will save me some time, which would be much appreciated.

Thanks,

Tom

[Stan Mathews]

Re: Which is faster - Append or script

Someone will probably have a definitive answer but in my experience the xbasic for an operation is faster than coding as you have done. I would suggest you capture the xbasic of the append operation and modify the table.open() command to include the read/write exclusive parameter.

[Raymond Lyons]

Re: Which is faster - Append or script

I second what Stan said. And 1200 records should go very fast no matter what, i.e., that's not a lot of records.

Raymond Lyons

[Stan Mathews]

Re: Which is faster - Append or script

You don't mention if you have any indexes created for the fields used in the filter expression. If not, adding them and optimizing the expression should speed things up. I would think indexes on clientid and NC would help. Not certain of how the if() responds to LQO but I think I would move the others forward in the expression


query.filter = "NC='Med' .and. clientid<>'' .and. IF(var->v_Location='All Locations',.T.,Location=var->v_Location) .and. (Date_dc={} .or. date_dc>var->v_BeginDate)"

[Tom Patten]

Re: Which is faster - Append or script

Thanks for the suggestions.

Stan - are you saying I should open the table in exclusive mode? e.g. table.Open("med_adm",FILE_RW_EXCLUSIVE) Might this speed it up?

I did find and eliminate a field rule that filled using a calc expression and Lookup function.

I'll work harder getting the current operation to work more quickly.

Thanks,

Tom

[Stan Mathews]

Re: Which is faster - Append or script

are you saying I should open the table in exclusive mode?

If you are running the xbasic of an append operation and it will not interfere with another's use of the table you should use table.Open("med_adm",FILE_RW_EXCLUSIVE).

If the FILE_RW_EXCLUSIVE mode is obtained Alpha doesn't waste any time checking to see if another process wants to access the table.

[Tom Patten]

Re: Which is faster - Append or script

WOW - I just completed an append operation that took 20 minutes last month in less than 15 seconds!!!

Thanks Again,

Tom

[Stan Mathews]

Re: Which is faster - Append or script

So call your wife and tell her you're taking her out to dinner to celebrate.

[Mike Wilson]

Re: Which is faster - Append or script

You don't have to write out 20 fields. AHHHHH, the beauty of evaluate_template()!!!

' get the table fields into a list for entry
	dim vc_table_fields as C
	vc_table_fields=table.external_field_name_get("Med_Adm","N")

'open the source and target tables, filtering the source table
	dim tt as p
	dim ts as p
	tt = table.open("Med_Adm.dbf")

	dim ts_filter as C
	ts_filter="IF(var->v_Location='All Locations',.T.,Location=var->v_Location).and.NC='Med'.and.clientid<>''.and.(Date_dc={}.or.date_dc>var->v_BeginDate)" 

	ts = table.open("Meds.dbf")
	xs=ts.order("",ts_filter)
	ts.batch_begin()
	tbl.fetch_first()
	while .not. ts.fetch_next()
		for each foo in vc_table_fields
			tt.enter_begin()
			evaluate_template("tt."+alltrim(foo)+"="+"ts."+alltrim(foo))
			tt.enter_end(.t.)
		next
	ts.fetch_next() 
	end while 
	ts.batch_end()
	ts.close()
	tt.close()

[Tom Patten]

Re: Which is faster - Append or script

Mike,

Jeeeeeeeeeeeez, but that's clever. I'll have to study it closer.

Thanks Much,

Tom

[Stan Mathews]

Re: Which is faster - Append or script

You can do the same with the xbasic of the append script. The really nice part is if you alter the structure of the tables involved, so long as you change both of them to match, the script rewrites itself. You don't need to edit it.

[Tom Cone Jr]

Re: Which is faster - Append or script

Hey Mike,

Is the following code right?

tbl.fetch_first()
	while .not. ts.fetch_next()
		for each foo in vc_table_fields
			tt.enter_begin()
			evaluate_template("tt."+alltrim(foo)+"="+"ts."+alltrim(foo))
			tt.enter_end(.t.)
		next

1) the "tbl" pointer is defined where ?

2) will a new record be started in the target table for each field in the source ?

3) will the "fetch_next()" method of the table object return a logical value that can be used to terminate the while / end while loop ?

I must be missing something.

[Stan Mathews]

Re: Which is faster - Append or script

Here's the way I do it. Script predicated on matching fields in both tables in the same order as seen in the table structure editor (type, width, and decimals also matching). The field names in either table can be changed and the append still works. Add a field(s) of any name to both tables with matching data specifications and the append still works.

Sample tables attached.

mast_flds = table.external_field_name_get("appendto","N")
mast_count = line_count(mast_flds)
trans_flds = table.external_field_name_get("appendfrom","N")
trans_count = line_count(trans_flds)
if mast_count <> trans_count
	ui_msg_box("Warning","Field counts are not the same.")
	end
end if

DIM Append as P
a_tbl = table.open("appendto",FILE_RW_EXCLUSIVE)
append.t_db = "appendfrom"
ON ERROR GOTO ERROR3105201314440284
append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = ""
append.type = "All"
append.m_count = mast_count
for qx = 1 to mast_count
eval("append.m_field"+alltrim(str(qx))) = word(mast_flds,qx)
eval("append.m_exp"+alltrim(str(qx))) = "@APPENDFROM->"+word(trans_flds,qx)
next qx
append.t_count = 0
a_tbl.append()

GOTO CONTINUE3105201314440284
ERROR3105201314440284:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Append Operation"+crlf()+error_text_get())
END
CONTINUE3105201314440284:
a_tbl.close()

fromandto.zip

[Ray in Capetown]

Re: Which is faster - Append or script

If they have matching structure, nothing comes close to the speed of .Copy_Records_To(
especially as opposed to append
15000 large records (70 fields) in 2.8 secs on a not very fast notebook. Indices intact.

[Mike Wilson]

Re: Which is faster - Append or script

Tom,
of course you are correct. A couple of lines out of place and typo. For completeness..

' get the table fields into a list for entry
	dim vc_table_fields as C
	vc_table_fields=table.external_field_name_get("Med_Adm","N")

'open the source and target tables, filtering the source table
	dim tt as p
	dim ts as p
	tt = table.open("Med_Adm.dbf")

	dim ts_filter as C
	ts_filter="IF(var->v_Location='All Locations',.T.,Location=var->v_Location).and.NC='Med'.and.clientid<>''.and.(Date_dc={}.or.date_dc>var->v_BeginDate)" 

	[COLOR="#0000FF"]'open the source table filtered[/COLOR]
	ts = table.open("Meds.dbf")
	xs=ts.order("",ts_filter)
	ts.batch_begin()
	[COLOR="#FF0000"]ts[/COLOR].fetch_first()

	[COLOR="#0000FF"]'loop through the source table records to copy to target table[/COLOR]
	while .not. ts.fetch_[COLOR="#FF0000"]eof[/COLOR]()

		'[COLOR="#0000FF"]begin a new record in the target table[/COLOR]
		tt.enter_begin()
	
			[COLOR="#0000FF"]'loop thru each field and make an entry from source table field to target table field[/COLOR]
			for each foo in vc_table_fields
				evaluate_template("tt."+alltrim(foo)+"="+"ts."+alltrim(foo))
			next

		[COLOR="#0000FF"]'save the target record[/COLOR]
		tt.enter_end(.t.)

	[COLOR="#0000FF"]'fetch a new source table record to copy to target table[/COLOR]
	ts.fetch_next() 
	end while 
	ts.batch_end()
	ts.close()
	tt.close()