Multi-user environment.. Insert into vs Append blank

[EzLogic]

vfp 9
sp2

in a heavily used multi user environment, what is best to use

each station (up to 10 stations possibly), each inserting 3000+ records each at a given time (processing orders etc..)

select myCursor
scan 
  process some stuff
  && add record in table
  insert into myTable (field1, field2) values (bla, bla)
endscan

or

select mycursor
scan
  process some stuff
  select myTable
  append blank 
  repalce field1 with bla,;
    field2 with bla in myTable
endscan

which is better from your experience for:
speed?
record locking?
etc..







Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Dan Freeman]

INSERT INTO will be faster than APPEND BLANK/REPLACE because with the append/replace approach all of the index tags are updated first with the blank, and later with the replaced values. With INSERT INTO, the index is updated only once. The performance hit comes from updating the indexes multiple times.

You have to lock, and respond to lock failures/competition, either way. So that has no bearing on performance.

(FWIW, 10 users is not heavy use.)

 

[Mike Lewis]

I agree with Dan that INSERT will be faster than APPEND BLANK / REPLACE. But the question of how and when you do the locking will be more significant.

Also, if the source data is itself in one or more cursors or tables, you should consider doing this type of INSERT:

INSERT INTO MyCursor (Field1, Field2)
VALUES
SELECT AField, AnotherField FROM SomeStuff

This will be considerably faster than looping through SomeStuff, inserting one record at a time.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Also important: Is it really just only new records or also updates? Processing all existing and new data in the table buffered and then submitting all data via Tableupdate(.t.,.t.) is even doing inserts/updates and deleteds to any amount of records.

If it's just insert you can also INSERT INTO ourtable FROM SELECT * From xyz or APPEND FROM DBF("xyz"). Both of these ad all records of xyz.

In the end, you can simply test what fit's best in your situation. The argument of double index updates is true, so your direct question can simply be answered with INSERT. I'd say you'll find this answer in about any foxpro forum many times, just as a side note.

It's not only double index updates, it's also triggers (insert trigger and multiple update triggers, if doing a replace for every field), determining standard values (at APPEND BLANK) and evaluation of field and table rules. All these are only done once, i you INSERT, so INSERT surely is to be preferred, and if you need to ask and cannot answer yourself you may have never heard of or forgotten about all those things coming into effect. Many of those things surely only did come with DBCs and Visual Foxpro for Windows, but are around for over 10 year each.

Bye, Olaf.

 

[Olaf Doschke]

PS: The only very good use case for APPEND BLANK is, if you (or your users) want to add a new record to a table and then enter it'S values. This way you can use a form with controls bound to all the fields of a table for both updating/editing existing records and adding new records.

If you would only have INSERT, you'd need one form with bound control and another version with unbound controls, because with bound controls you cannot bind to non existing records, so to let the user insert the data of a new record, you'd need unbound controls (controls not having set a controlsource) to collect the values needed for the INSERT SQL.

Bye, Olaf.