I think I have an answer to those of you that have asked why some appends take so long (this was prompted by a post by xxxxxx). I've also seen slow appends, but not for every table. So, I ran a test, using a saved operation and as a script (there was no time difference in those two methods).
My computer is a P3-500 with 64MB RAM, Win98, using v4.03 build 221 for the test.
I used a customer's table SP (which I had noticed was slow to append) with about 35000 records. The table has 12 indexes (some of which are rather complex), 30 fields, and LOTS of field rules, some of which are lookups or complex calculations. So, I made a copy of 500 records of the table, and called it SPX. Then I appended SPX to SP.
It took about 150 seconds for the append portion of the operation, and another 20 seconds for the index rebuild portion (which is about the same time it takes to update indexes).
Then I eliminated all the indexes. The append portion took the same amount of time, but there were no indexes to rebuild, so the overall operation was faster.
Next, I eliminated all the field rules. BAM, it was done!! (The index rebuild still took 20 seconds). However, eliminating field rules isn't necessarily the best option. So I looked at the rules, to see which ones were causing the slowdown. I found two that used LOOKUPN. I took those out, and ran the append. It was very nearly as fast.
This makes sense, since LOOKUPN (and LOOKUPC and LOOKUPD) are going to take a lot longer to execute that simpler rules.
These results are consistent with my previous experience with other customers, in that some appends are really fast (appending to tables with few field rules), and some are REALLY slow. This is because the append evaluates the field rules (to make sure records that violate the rules are NOT appended, but end up in VIOLATIONS.DBF instead *). As a result, even a small table could take a long time to append if the field rules are sufficiently complex.
Now that the problem is defined, how about a solution? Something that seems to work quite well is to rename the dictionary files (SP.DDD, SP.DDM, SP.DDX) to something like SP.XDD, SP.XDM, etc., so the field rules are NOT enforced, and then run the append from a script (hint: after creating a saved append, run it with the script recorder on. Then copy the results to a global script. You'll have to change the first line, "table=table.current()" to "table=table.open("sp")"). After running the append, rename the dictionary files back to their original names, to restore the field rules.
One possible disadvantage is that you might append data that violates the field rules (which would NOT append if the field rules are enforce, but end up in VIOLATIONS.DBF instead). My advice: make sure the data is valid BEFORE the append.
My computer is a P3-500 with 64MB RAM, Win98, using v4.03 build 221 for the test.
I used a customer's table SP (which I had noticed was slow to append) with about 35000 records. The table has 12 indexes (some of which are rather complex), 30 fields, and LOTS of field rules, some of which are lookups or complex calculations. So, I made a copy of 500 records of the table, and called it SPX. Then I appended SPX to SP.
It took about 150 seconds for the append portion of the operation, and another 20 seconds for the index rebuild portion (which is about the same time it takes to update indexes).
Then I eliminated all the indexes. The append portion took the same amount of time, but there were no indexes to rebuild, so the overall operation was faster.
Next, I eliminated all the field rules. BAM, it was done!! (The index rebuild still took 20 seconds). However, eliminating field rules isn't necessarily the best option. So I looked at the rules, to see which ones were causing the slowdown. I found two that used LOOKUPN. I took those out, and ran the append. It was very nearly as fast.
This makes sense, since LOOKUPN (and LOOKUPC and LOOKUPD) are going to take a lot longer to execute that simpler rules.
These results are consistent with my previous experience with other customers, in that some appends are really fast (appending to tables with few field rules), and some are REALLY slow. This is because the append evaluates the field rules (to make sure records that violate the rules are NOT appended, but end up in VIOLATIONS.DBF instead *). As a result, even a small table could take a long time to append if the field rules are sufficiently complex.
Now that the problem is defined, how about a solution? Something that seems to work quite well is to rename the dictionary files (SP.DDD, SP.DDM, SP.DDX) to something like SP.XDD, SP.XDM, etc., so the field rules are NOT enforced, and then run the append from a script (hint: after creating a saved append, run it with the script recorder on. Then copy the results to a global script. You'll have to change the first line, "table=table.current()" to "table=table.open("sp")"). After running the append, rename the dictionary files back to their original names, to restore the field rules.
One possible disadvantage is that you might append data that violates the field rules (which would NOT append if the field rules are enforce, but end up in VIOLATIONS.DBF instead). My advice: make sure the data is valid BEFORE the append.
Comment