jan_sc


hi,

i encountered a very slow insert/updating process to a table through network (map drive). the .exe and table are all in the same network drive and same folder. the table is shared access and it could be use by another person(s) during the insert/update takes place. when this happens, it became very slow. there are about 2000 records and it took 10-15mins to complete compared to 15sec when no one is using the table. how can i improve it advice is much appreciated.


Code Snippet

use xls_open_po in 0 shared
use open_po in 0 shared

select xls_open_po
go top
scan
scatter memvar memo

select open_po
go top
locate for cust_no = m.cust_no and pur_order = m.pur_order and sales_doc = m.sales_doc and doc_item = m.doc_item and sl_no = m.sl_no

if eof()
insert into open_po from memvar
else
gather memvar memo
endif
endscan

thanks.




Re: slow insert/update process

Alex Feldstein


What version of VFP

OS Network speed

Are the tables properly indexed and Rushmore optimized

One way would be to use a compound index (assumming all fields are of char type) and use SEEK() instead of Locate. Locate would work in a properly optimized table with decent speed too.

Code Snippet

use xls_open_po in 0 shared
use open_po in 0 shared

select xls_open_po
scan
scatter memvar memo

select open_po
if seek(m.cust_no+m.pur_order+m.sales_doc+m.doc_item+m.sl_no)

gather memvar memo
else
insert into open_po from memvar
endif
endscan

Note the use of SEEK() and that you do not need Go Top with Scan.

To check your Rushmore optimization level see here: SYS(3054)

http://msdn2.microsoft.com/en-us/library/2b1f1az0(VS.80).aspx






Re: slow insert/update process

Naomi Nosonovsky

1. Alex is absolutely right that it would be much quicker with seek.

2. The whole code could be re-written this way:

Code Snippet

select xl.*, iif(isnull(op.cust_no),'Insert','Update') as Action ;

from Open_Po op right join xls_open_po xl on ;

op.cust_no = xl.cust_no and op.pur_order =xl.pur_order and op.sales_doc = xl.Sales_Doc and ;

op.doc_item = xl.doc_item and op.sl_no = xl.sl_no into cursor curChanges

update OP set Field1 = xl.Field1, Field2 = xl.Field2 from Open_Po inner join ;

curChanges xl on ;

op.cust_no = xl.cust_no and op.pur_order =xl.pur_order and op.sales_doc = xl.Sales_Doc and ;

op.doc_item = xl.doc_item and op.sl_no = xl.sl_no where x.Action = 'Update'

insert into Open_Po select * from curChanges where Action = 'Insert'

Not sure if this would be more efficient.

Also it may help to have table buffered, do updates/inserts and then tableupdate.






Re: slow insert/update process

jan_sc

hi,

i'm using vfp9.0, windows xp pro with network speed 100mbps. i had created a .cdx and using SEEK instead of LOCATE, but the time taken for the process is not significant. is this normal in a multi-user environment





Re: slow insert/update process

Olaf Doschke

Seems like all you need is an Update of existing records plus an Insert of new records. Do that with SQL.

update table1 set table1.field2=table2.field2 from table2 where table1.field1=table2.field2

insert into table1 from (select * from table2 where table2.field1 not in (select field1 from table1))

Even simpler,if all you really want is edit a table, change existing records and add new ones, make use of a view or cursoradapter, the right choice of buffering and locking and then the data update simply is a tableupdate().