Mark records in a Query without touching its table?

[G Gabriel]

Re: Mark records in a Query without touching its table?

Adding many indexes where they are not used regularly

Ira:
Please note, I said and you quoted me:

repetitively

You can't use variables in index expressions directly

Yes you can.

[Finian Lennon]

Re: Mark records in a Query without touching its table?

Adding ANY index to a heavily used table in a multi-user environment adds incredible overhead and is to be avoided unless absolutely necessary. One should agonize over every index in such an environment. Think "I've only got $100 in the world. Do I really need this index for $50?" Well maybe that's a little extreme, but not by much.

I'm a big user of temp tables and, in the right circumstances thay are an ideal solution to problems such as Brett has described. Where I do this, each user may be adding between 4 and 40 records to sundry temp tables. However, if I read things correctly, his users need to be able to process many thousands of records in the query. Whether using a temp table or an array, I think that multiple users simultaneously moving that many records to any new location will pose a significant burden on the system and will not deliver a high level of performance speed.

This is one instance where I would look at adding "Mark" fields to the main table to designate the record as being marked (rather than marking the record itself). I'm not sure sure where to go from there but, intuitively I feel that in this case optimized queries on the main table will be less burdensome to the system and give better performance overall.

One more thing, it's not clear to me if the records being queried are being changed or modified in any way. If they are, then using a temp table means that more processes are required to update the main table and these would further slow things down.

[csda1]

Re: Mark records in a Query without touching its table?

Hi Finian,

Obviously I agree just about 100% with everything you reiterated, but want to address 1 issue.

Whether using a temp table or an array, I think that multiple users simultaneously moving that many records to any new location will pose a significant burden on the system and will not deliver a high level of performance speed.

A query with a filter (pretty much everything) put's as much load on a system as copying those same records. The reason is that a query needs to read a record's data to evaluate the filter, and applies the same amount of record locking as the copy records does. There is some workstation overhead associated with taking the record and processing the data for the copy, but this put's no additional overhead on the shared network resource.

Thus, from a network standpoint a copy is as good as a query and has the side benefit of having created a temp table available for your use.

[G Gabriel]

Re: Mark records in a Query without touching its table?

Gentlemen..please don't loose your heads in the fog of war!

An index, is a table.

What is the difference in terms of overhead?
Only difference is: alpha has to update the index any time a change takes place. So the question is: Which causes more overhead: updating an existing index? or creating a temp table denovo? and updating it too..

Finian:

Adding ANY index to a heavily used table in a multi-user environment adds incredible overhead and is to be avoided unless absolutely necessary

Whether using a temp table or an array, I think that multiple users simultaneously moving that many records to any new location will pose a significant burden on the system and will not deliver a high level of performance speed.

Of these two devils, which one would you rather choose, in a high traffic area?

It seems that you started out 180 degrees from me, favoring temp tables, then with further analysis made 180 degree turn! and since Ira agrees with you 100%, I suppose we are all in agreement.

[csda1]

Re: Mark records in a Query without touching its table?

An index, is a table.

An index is a sorted list of record keys, in DBase compatible files, a B-Tree sort, it is definitely not a table!

What is the difference in terms of overhead?
Only difference is: alpha has to update the index any time a change takes place. So the question is: Which causes more overhead: updating an existing index? or creating a temp table denovo? and updating it too..

Changes/Enters to the table, which generally also write to the indexes, create write locks, a very high overhead compared to a read lock as more has to be done. A query/copy merely reads/copies the data which invokes read locks. Any operation including updating to a temporary table on a non-shared resource (In other words, a local drive) has zero impact on the network or server.

A query and a copy result in exactly the same network overhead. Even if you used temporary arrays/lists, you still have to query the data, which means that you might as well have copied it to a local temp table. The difference is that now any additional processing has no network overhead and can use all of Alpha tools, be it arrays, lists, tables, reports, etc.

There is no reference to Brett having to update/change the original table, so there is no required posting back of any data. Nevertheless, if one did have to post, I probably would leave the original table as is, and use a set to link to a "mark" table that has those items tagged as duplicates. This minimizes the shared resource impact on the original table, and simplifies the posting methods.

[G Gabriel]

Re: Mark records in a Query without touching its table?

Ira:

An index is a sorted list of record keys, in DBase compatible files, a B-Tree sort, it is definitely not a table!

This is rather a very twisted way of describing a table! and the fact that it's B-tree gives more reason for using an index.

There is no reference to Brett having to update/change the original table, so there is no required posting back of any data.

So, where does the locking problem come to play?

[Finian Lennon]

Re: Mark records in a Query without touching its table?

Meaning no offense, but you seem to want some idealized, one size fits all, answer/solution to every question/problem.

My personal mantra is "horses for courses". Most lovers of the turf agree that Arkle was among the greatest steeplechasers of all time. None of those people would dispute the statement that "if he ran in the Kentucky Derby, Arkle would finish in last place."

That is, you take all circumstances into consideration before testing a possible solution of which there may be several suitable for consideration and possible implementation. I use global arrays as lookups but not everywhere. I make extensive use of temp tables for report generation. One decision does not replace the other.

Indexes are not "free". Ira has already pointed out that each index entails a certain amount of overhead. I know, from my own experience, that one of the most important steps I took to eliminate a variety of persistent problems in our application was the removal of unnecessary indexes in our high volume tables. The most serious problem and most satisfying result was with our "audit trail". Every line transaction in our system is audited. That is, every new entry, save and deletion in that table is recorded, by user, in the audit table. To be able to track this we have an autoincrement field to uniquely identify every line in the transaction table.

In the "old days" some of our busiest customers were subject to occasional failures of the autoincrement field. These failures would go unnoticed, sometimes for days, and were a nightmare to fix. Some of these sites add records to the audit table at the rate of 10 to 40 thousand per month.

It seemed that the record saves in the audit table could not keep up with the rate at which the transaction records were being saved. One of the reasons for the failures, I felt certain, was the number of indexes we needed on the audit table to allow the users to view the audit trail records. Eventually I took the step of disallowing access to the table directly by the user. Instead, when they want to view the audit trail the records are appended to a temp table that has numerous indexes to facilitate queries and sorts. By doing this I was able to remove ALL indexes from the audit table itself.

In any event, in the four or five years since I made the changes, we have not had a single problem with either the audit table indexes (duh!) or the autoincrement field in the transaction table. The total record count in the audit table at those sites now ranges from 1.5 to several million.

It's not that I don't have tables with lots of indexes. I have some reporting tables with 9 or 10 (which seems like a lot to me) very complex indexes. But I would never, and I mean never, make a table for a high production environment (think line items) with more than four or five, simple, non-filtered, well-constructed indexes.

[G Gabriel]

Re: Mark records in a Query without touching its table?

Ok..
So, we have on the menu here: arrays, temp tables and indexes. Use the more appropriate one for your circumcetances.

[Mike Wilson]

Re: Mark records in a Query without touching its table?

I think I might have to go with the temporary table concept rather than an array, mainly because my query result is likely to be at least 20 thousand and sometimes up to 100 thousand customer table records.

I find the academic discussion of this thread absolutely informative and highly interesting. BUT, the discussion on system capacity and overhead in the context of the original premise (user marking duplicate records) a little off the mark.

I remain flogged that everyone but me has fully neglected the context that the number of records Brett has presented as being delivered through Query to a user (20K to 100K), whether it be through temp tables or arrays, for a users to inspect and "mark" as duplicate, is so far beyond the capacity of a person to visually inspect and gauge the condition of being a duplicate within a time period that would seem conflict an issue, that discussion of the speed and overhead on the system doesn't make any sense to me.

It's seems a little like discussing a truck's capacity to carry the 20,000 lb/kg item that Bob will personally be lifting onto the truck, doesn't it?

A later process then processes the records of the query in a different order to that in which the query has been composed, omitting those records previously identified as duplicates.

Brett made this statement under the circumstances where only original tables are in effect. Under this circumstance, this says to me that the record must be changed in some way for this second process to function.

Under the discussion of temp tables produced, Ira said this:

There is no reference to Brett having to update/change the original table, so there is no required posting back of any data.

This would relieve the locking because each user would own their own table, but the 'second process' Brett described would then be required to operate from the temp table. Is this second process a nesessary change needed in the original table?

Mike W

[G Gabriel]

Re: Mark records in a Query without touching its table?

Mike:
I didn't assume that Bob will personally be lifting onto the truck. I assumed and believes that Brett will mark duplicates with an operation, xbasic..whatever, not manually.

What I think Brett is trying to do is:
A user might run a query on a million-record table to find for example all sales orders from Illinois (while another user on another machine is looking for the ones from Florida), the operation for the first user will take the selected records that might have multiple entries, for example several orders belonging to the same salesperson, but the user now wants to sort it by the salesperson. So the second process will remove the "duplicates" and show the salespersons. Something like that.

Since both users and maybe others are accessing the same table at the server machine, what is the best way to run these queries? That is the question.

Maybe the whole thing can be done with one step operation, maybe not. Don't have all the details. But the dominant question remains, how to extract selected records from a table by multiple users for further processing.

Brett, If I am off, please let me know.

[brett s]

Re: Mark records in a Query without touching its table?

Mike:
What I think Brett is trying to do is:
A user might run a query on a million-record table to find for example all sales orders from Illinois (while another user on another machine is looking for the ones from Florida), the operation for the first user will take the selected records that might have multiple entries, for example several orders belonging to the same salesperson, but the user now wants to sort it by the salesperson. So the second process will remove the "duplicates" and show the salespersons. Something like that.

Gabriel, That's a pretty good analogy. Throw in a sales type and a couple of product classification codes (of which each product can have at least 1 and up to 5 or 6) and the picture is about complete.

[brett s]

Re: Mark records in a Query without touching its table?

Hi Guys. Let me thank you all so much for all the useful feedback and comments. There's a mountain of really good suggestions here and I am still working through them all before I decide a workable solution.

Gabriel's analogy is use full here because if we can distill the problem to an something which we can all have experience of then we can talk more specifically about ideas and suggestions. I would not like to describe my actual application here because it is rather unique (as you have probably already gathered!) and would take a lot of words to describe (which would put us *all* to sleep!).

It's easy to get stuck in a mindset, and I am guilty here I have to admit. I have a "thing" about using marking to achieve the outcome I am seeking, mainly because it has worked for me before really well, but perhaps only because I resorted then to the use of a temporary table with one per user. In this case I wanted to use one per user because I could locate each on the user's local drive and get the benefit of improved performance and also not have to worry about the performance overhead of a single table containing several users' query results, each with 100 thousand results to be processed.

So I know I can make it work, albeit at the overhead of the additional reading from and writing to it imposes. Unfortunately A5 does not provide much help for temp files so a lot of the housekeeping has to be hand coded (e.g. functions such as '=create_temp_table(tablename as C, folderlocation as C, opentype as C, addtocontrolpanel as L)' would help a lot). [So there's a thought, why don't I write one myself?].

Another option that I am considering is to turn the table against which I want to run the queries into a temp table itself and locate it on the users' local drives. I can do this because the data in it contains largely static information (using Gabriel's analogy it contains sales figures for the previous month). It is 170 MB in size currently and with the network links at 1 Gbps the once-per-month overhead of copying this table to each user's local drive is probably acceptable.

That means with the temp table now local I can now have each user run their own queries against it and mark records without tripping up other users. I think this should work OK.

[G Gabriel]

Re: Mark records in a Query without touching its table?

..entry withdrawn..

[Mike Wilson]

Re: Mark records in a Query without touching its table?

Brett,

It's easy to get stuck in a mindset, and I am guilty here I have to admit.

Apparently not as stuck as me.

I want to be able to allow a user of this application to compose and run a query [ via Xbasic using <tbl>.query_create() ] against this table and then mark duplicates based on the query order.

I was stuck on the user composing a script and then the user physically marking records, 100,000 of them, no way!

Mike W

[brett s]

Re: Mark records in a Query without touching its table?

Brett,
I was stuck on the user composing a script and then the user physically marking records, 100,000 of them, no way!
Mike W

Mike, good point. But I don't mean for the user to do the marking manually. I can do this automatically once I know the type of query they are running. Its just a matter of selecting one of several fields and then marking the duplicates based on the user's selection. So it's not as daunting as I perhaps had made it out to be. :D