append speed

[Bob Arbuthnot]

Using an append operation and appending 1 record from a table of 650 records to a table with 65,000 records takes 4.5-5 seconds. Appending the same record from the same table to an empty table is almost instantaneous. Why is this? From my relatively simple minded viewpoint it doesn't seem like there should be any difference between the two, but that's obviously not the case.

I created the empty table by duplicating the field rules only of the master from the first example. In both cases the master and transaction tables were closed before running the operation. Is there any way to speed up the first append operation?

ba

[Tom Cone Jr]

Re: append speed

Bob, is this happening on a single machine, or are you using a workstation that's talking to a server over your LAN?

Do you normally have only one transaction record when you do the append? If so, you might be using the wrong approach. Operations are intended for batches of records. You might improve performance using a script to enter the new record directly in the master table.

--tom

[Bob Arbuthnot]

Re: append speed

Hi Tom,

I'm running this on a single stand-alone machine, and for the comparison I mentioned above I was running both operations from the control panel.

In the application I'm using the master table to archive all record changes, so anytime someone changes a record in the transaction table that record gets appended to the master table. I can appreciate that operations are intended for batches of records, but in this case I'm just appending one each time.

I looked at tbl.record_clone as a work-around, but I can't guarantee exclusive access to both tables. Is there another way of directly writing the changed record to the master table?

Be all this as it may, I still don't understand why the size of the 'receiving table' should have any bearing on the speed of an append.

ba

[G Gabriel]

Re: append speed

Bob:
One possible reason is if the operation is checking for duplicates.

Another, if you are using an expression in the appended fields.

[Tom Cone Jr]

Re: append speed

Your saved append probably has to run a query in order to find the right transaction table record to append to the master. Is LQO being used to do this?

The number and complexity of index tags on the master table could be a factor, also.

I avoid using "operations" for this kind of thing, since the operation must open new instances of both the transaction and master tables in order to do its work. Since the transaction table is already open (in the form where the edits are made), it's faster and simpler, in my opinion, to just open the master and enter the new record.

You may be interested in Dr. Wayne's audit trail approach, described in his Alpha Five book "Xbasic for Everyone".

-- tom

[JohnZaleski]

Re: append speed

The append operation is more than likely rebuilding indexes on your 65,000 record table. Try the following and see if it changes the speed. Prior to running your append open an instance of the 65,000 record table and minimize it. Then with the table still open, run your append.In this case, it shouldn't rebuild the indexes at the conclusion.See if there is a significant increase in speed.
In the news letter archives there is an article about this written by myself. I think the subject was called speed of operations.

[Bob Arbuthnot]

Re: append speed

Tom and Gabriel,

Thank you for your suggestions. I implemented as much as I could without much improvement in performance. Nevertheless, I always appreciate your thoughtful insights.

John,

I'd about given up on this until you chimed in. What a difference your suggestion makes! For anyone who's interested, the link to the article John refers to is:

http://newsletter.alphasoftware.com/0020612.htm

Interestingly enough, this newsletter article/suggestion apparently is in direct contradiction to a post from Selwyn on the V5 message board on 8/15/2002 in which he says:

RE: Slow Append
if Append can get an exclusive lock on the table it is appending to, then the operation will be fast.

if Append can NOT get an exclusive lock on the table, then append is relatively slow because a5 must append one record at a time.

you have had another instance of the table open the first time, and so the Append operation could not get an exclusive lock on the table.

For anyone who has experienced this slowing down of an append or update operation when their master table begins take on some size, this is an incredibly useful piece of information which I'm guessing most hadn't heard of or had long since forgotten.

John, Thank you again very much!

ba

[JohnZaleski]

Re: append speed

Bob,
You are very welcome, I'm glad it helped.
Since you were appending a single record to a fairly large table, my method was faster. If you were appending several thousand records at a time, Selwyn's suggestion would probably be faster.So, there is no real contradiction here.
John

[G Gabriel]

Re: append speed

Bob/John:
When I first read Bob's question, I had assumed that it, most likely, has to do with updating the indexes, but as I was not quite sure at what point of time alpha does that, I restricted my comments pending further research. Now I am a bit confused:

1-From what John says, by keeping a second instance open, alpha will append the new records and builds the indexes for the newly appended records ONLY
2-On the other hand, what Selwyn is saying, if another instance is open, the tardiness comes from the fact that alpha appends one record at a time.

Reading these two, the question is:
1-Is it better to open a second instance?
2-Is the tardiness from the append process itself? or from updating the indexes?

What I was attempting to research was to find out if there is a function that tells alpha to halt updating the indexes.

I dropped that idea, even if that function exists, simply because, you HAVE TO update the indexes and you better do that IMMEDIATELY after you append.

So, if the tardiness has to do with updating the indexes, then this is something you have to live with as a by-product of the size of your table and your indexes. If it has to do with the append process itself, then looking for optimization of the process would be the only question remaining.

Having further thoughts after reading the quote from Selwyn: if there is a function that halts the update, then you might want to try that.