Arrays vs Temp Table

[G Gabriel]

In terms of speed, efficiency..etc, which is better?

Yes..yes..yes.. I know, an array is not a table. We do not need to get too technical. In the end and in terms of functionality, an array will act as a temp table. So, is there any difference or advantages of one over the other?

[Steve Workings]

Re: Arrays vs Temp Table

A good deal of the answer depends on how you intend to use the data.

In one of my apps I keep an entire GL in a global array because I need to get to it very often, and very quick, sometimes upwards of 30,000 times in an hour. Keeping and using it in its original table caused a slowdown that was simply unacceptable.

So, how is the data to be used?

[G Gabriel]

Re: Arrays vs Temp Table

So, how is the data to be used?

Actually Steve this is more of comparison of the two to determine the pros/cons of each if any. I've thought of this issue previously and recently it came up in another thread. No such comparison that I know of is to be found in the help file.

I agree with your assertion that array is probably faster since I envision an array as a "table on the fly". As to repetitive use of an array vs a temp table, I can not offer any opinion here not knowing how alpha handles each.

[Tom Cone Jr]

Re: Arrays vs Temp Table

Steve, I'm curious. How do you maintain or verify the integrity of the array ? -- tom

[JohnZaleski]

Re: Arrays vs Temp Table

Gabe,
I would assume it depends on the volatility of the table involved.In Steve's example, there is basically a static nature to a chart of accounts.The speed difference between acessing elements of an array from memory should be exponentially faster than accessing records from a table on a hard drive.
However if the table is volatile, and the array has to be constantly refreshed,
the speed advantages of an array could be offset.
The biggest advantage of the table is that Alpha's field rules work with tables, and working with an array will involve a lot more coding.

[Steve Workings]

Re: Arrays vs Temp Table

I'm not sure what you're asking Tom. I suppose, though, that the answer is that I don't do much at all. I load it in the autoexec, it's worked for all the users for years, so I don't worry about it.

[G Gabriel]

Re: Arrays vs Temp Table

John:
Your comments are well taken, however; when alpha opens a table, where does it open it? Does it remain resident in the RAM negating the need for hard drive access? what if the table is too big? same goes for an array: if you initialize an array with 100,000 records, where does alpha keep these records? Can't remember if there is a limit to an array though.

[JohnZaleski]

Re: Arrays vs Temp Table

Gabe,
I'm sure with a little testing you could satisfy yourself on your questions
Open a table with at least 20,000 records with an xbasic program and do about 50 random acesses to it.Time it and look at your hard drive light while the program runs.Then populate an array the same amount of records and do the same.My bet is that the table loses by quite a bit and that the hard drive light is very active during the table's random accesses.
John

[DaveM]

Re: Arrays vs Temp Table

Hey all,

Good points here.

In Clipper and VB, we never accessed the table diirectly onto a form. We made variables(later arrays), and placed them on a form. Once data entry was complete, we wrote the contents to the table. It was considered faster than directly opening a table. I always believed(and still do) that Alpha handled this for us to a great degree.

When we needed to edit a record, it was read into variables/arrays and again written back to the table(s) at edit end.

I don't know if this helps, but it was/is pretty standard.



Dave Mason

[Marcel Kollenaar]

Re: Arrays vs Temp Table

Strange question.... It is very simple. When I have to do math or planning stuff, approximately 2000 records, I read the table into an array. With the array I'm going to manipulate the data. Very fast. Just pure power.

[G Gabriel]

Re: Arrays vs Temp Table

...and if you are reading 20,000 records? or 100,000?

[Marcel Kollenaar]

Re: Arrays vs Temp Table

...and if you are reading 20,000 records? or 100,000?

As I said approximately 2000 records. But if you have enough memory where is the limit. I know databases that only exist in memory when they are up and running or database system that uses a fair amount of memory at moment it has to do some data mining and you don't even know when it claim your total memory.

I think it depends on the data manipulation you want to do. Slow processes, simple data keeping, archiving is not worth to keep it in memory. Fast real time information handling is best handled in memory.

Another take. Suppose you want to check if the contents of a text field in two identical databases DB1 is different from the same field in DB2. You can add a CRC field and calculate the CRC checksum of that field every time you change it. Do it for both db's. When you want to check for differences you only have to load the fields key and CRC of database DB1 and DB2 in an array, sort it on the key and start checking both CRC fields. If it is different you have a hit for further investigation. Very fast in memory.

McObject
KXSystems

[DaveM]

Re: Arrays vs Temp Table

I believe your are now talking "Dynamic Array".

Dave

[Marcel Kollenaar]

Re: Arrays vs Temp Table

<array>.initialize_from_table()