How To Append Without Updating All Indexes

[nigeldude]

I have a master table "products_shapshot" with over .75 million records. Twice daily, an additional 1510 records is appended to it from "products".
When I run the operation it takes about 5 minutes to append, After searching the message board I found out that if i open the products_shapshot table, its takes about 5 seconds or less. This worked great for a while, but now, if I run the append operation with the table open, it fails and suggest that the indexes are corrupted and I should update them. Updating them does not help, I can only append with the products_shapshot table close, but its a huge inconvenience because of the time it takes and the fact that at least 3 departs have to log out of alpha to facilitate this.

Is there any pointers as to where I'm going wrong?
From what I understand, its not the append itself that's taking that long, but the updating of all .75 million records in the products_shapshot table.
Is there another method of appending the records to products_shapshot without having to wait while all .75 million records get their indexes updated? Maybe a way to only update the 1510 records that added?

[Stan Mathews]

Re: How To Append Without Updating All Indexes

When I ran into a similar issue I coded to

drop the indexes
append records
re-create the indexes

That doesn't sound like it will work for you.

It also doesn't sound like the indexes alone are your issue. More likely that others are using the table and Alpha is having to coordinate the append with other activity for the table.

[nigeldude]

Re: How To Append Without Updating All Indexes

Ur right Stan, dropping and recreating the indexes would not work for me, it would still take considerable time to recreate them.

As for others using the table, That was intentional, since having the table open considerably speed up the append process. However, now, even if I unplug the network cable to ensure that only I am using the app, it still fail to append once the table is open, it works only if the table is closed, but it takes too long.

[Tom Cone Jr]

Re: How To Append Without Updating All Indexes

Nigel, are you running saved append operations?

If so, have you tried entering the records using an xbasic script? I'm wondering if the batch_begin and batch_end methods of the table object will help?

[nigeldude]

Re: How To Append Without Updating All Indexes

Yes Tom, I am running a saved Append operation.
I looked up batch_begin in the documentation, but I don't think I quite get it as to how to go about "entering the records using an xbasic script", any particular page you cud direct me to as to how to accomplish that?

[Stan Mathews]

Re: How To Append Without Updating All Indexes

it would still take considerable time to recreate them

Possibly, but less than having the indexes update as each record is appended.

Capture the xbasic for your append, save it as a script.
Change the line similar to


a_tbl = table.open("lewismkt")

change it to

a_tbl = table.open("lewismkt",FILE_RW_EXCLUSIVE)

Run that with the network cable unplugged.

[nigeldude]

Re: How To Append Without Updating All Indexes

Hey Stan! I get the very same result when I try that. It works very slowly when the table is not open, and it fails when the table is open.

[G Gabriel]

Re: How To Append Without Updating All Indexes

There is so many implications in the question. Append without updating the indices implies you don't really need those indices for whatever tasks the users are doing, at least not at that time.
So, the question is, what are you using these indices for?
I have advocated in the past for the fewest possible indices.
Second, you are asking if you could update the incises for just those added records. Does not work that way. The entire index, all indices, have to be re-written.
Thirdly, do you need all these records in a day-to-day use?
How about achieving the old records?
Fourth: How do you know that the indices is the bottle neck?
To find out, you could do one of two things:
1-Run a profiler, or
2-Copy the entire table without the indices and run the same operation and see how long it takes.
None of the above answers your question, but if we go along with the assumption that the culprit is the indices and since you want somehow someway to suspend these indices, a quick easy way to do that is to duplicate the table with another empty one. Append to the new table and at the end of the day, append that table to the original one and empty it.

[GGandhi]

Re: How To Append Without Updating All Indexes

http://wiki.alphasoftware.com/~alpha...end+Operations
according to this page from wiki
append operation locks the master table, ( I am guessing no need for rw_exclusive), appends the table and creates the indices. this happens if the table is not open. this is good for large files. on the other hand, if the table is open then alpha appends a single record updates the index then the same all over again. so this may be the problem not just indices. the table may need to be closed during that time everywhere.

An append operation takes the records from the transaction table and adds them to the data in the master table. The append operation:

1.locks the master file


2.appends all the records from the transaction file


3.updates any indexes


This speeds up merges of large files. If any one else has the master table open, or if you open another form using the master table, the append will update the indexes after each record append.

from wiki.

[Tom Cone Jr]

Re: How To Append Without Updating All Indexes

Nigel,

entering records using an xbasic script is not that difficult and there are lots of examples here and in the helps. If you aren't familiar with it let us know. Maybe someone can coach you. Its a valuable skiill to learn.

Question: How many index tags have you defined for this table? You know of course that each index tag creates a separate sorted list of record numbers, so the more tags the slower updates will run. You may be thinking that you are updating a single index, when in fact alpha has to update all of the index tags separately. On large tables it often pays to reduce the number of index tag definitions to improve performance.

[nigeldude]

Re: How To Append Without Updating All Indexes

Ok Gabriel,
I tried the profiler in an inline action script, I got no results, the trace window showed nothing, not sure what I did wrong. I deleted all the indexes from the table and run the saved append operation with the table open, it went through at a very satisfactorily speed. So I guess it is the indexes that is the problem.

I do need the indexes in that table for multiple data retrieval daily, What I don't need daily is all of the records in the table. Usually all the work is done with the last and the 2nd to last set of 1500 records, but occasionally we do go back farther in the pat that does involve lot more records.

Yes Tom, I am not familiar with batch entering of records using xbasic, I would appreciate any assistance given.
I defined 15 tags and one got created automatically "__A5_Recor"

[G Gabriel]

Re: How To Append Without Updating All Indexes

Well, sounds to me you need to "archive" the old records in a different table.

[DVSOL005]

Re: How To Append Without Updating All Indexes

Nigel,

I had issues with the profiler too, no result in the trace window.
The only way I got a result was to write to a text file.
Another issue I found was that using the profiler in a UDF didn't work.
This is how I did it:
profiler_begin()
part of the script you want to profile
profiler_end("C:\yourfile.txt",.T.)

[Tom Cone Jr]

Re: How To Append Without Updating All Indexes

15 indexes ! This is something I would never do, especially on a large table, especially for a multi-user (networked) application.

I'm curious how many of them have tag names that are longer than 10 characters?

How many of the index tags include filters?

What version of windows is being used?

How much RAM is available on the workstations? ... on the server?

Are other large applications running on the workstations or server when Alpha Five is being used?

[nigeldude]

Re: How To Append Without Updating All Indexes

Thanks Jo, that method work perfectly.


None of my field or tag names are ever longer that 10 characters
None of the tags have a filter
My development PC is Windows 8 64bit
the server and stations are all Windows XP, 1 Gig RAM
Usually, there is no other software running, occasionally: Google Chrome, MS Excel, MS Word