Dr Peter Wayne vs Heap Locks; 1-0

[Stephen Williams]

I just checked Dr Waynes site (www.learn alpha.com) and found a VERY helpful explanation on heap lock failures.

This man deserves a LARGE stock option in Alpha Software for services rendered...

Stephen Williams

[csda1]

RE: Dr Peter Wayne vs Heap Locks; 1-0

Stephen,

In a set of e-mails that Peter and I had, he conceded that programming errors (the user's of Alpha Five) can also cause Heap Lock errors. But since I never use filtered indexes (but I definitely use the equivalent of filtered indexes -- see my comments at the bottom of the article on Heap Lock errors) I am positive that I have received heap lock errors for other circumstances after the development and debugging of an A5 application. However, I'm not sure I can pinpoint a specific instance of cause and effect. Perhaps others can relate some instances.

The point is just because one cause of an error has been identified (and in fact has a very workable solution per my comments), does not mean that all cases, or even most cases, have been accounted for.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Stephen Williams]

RE: Dr Peter Wayne vs Heap Locks; 1-0

I said 1-0, not KO...I realize it is only one battle, but it meshes with my experience. I had a terrible time for a while last year, and I deleted a bunch of indexes and have had less problems since.

And you should also get stock options! I revamped my filtered indexes as you suggested in the article, so I do not actually have any filters. It is still early days but I just ran my closing out of monthly invoices without a hitch on the newly defined indexes.

Thank you for digging into this one and sharing it!

Stephen Williams

[Jim Rowlen]

RE: Dr Peter Wayne vs Heap Locks; 1-0

Being the developer who supplied the CD with the application Dr Wayne mentioned in his article, perhaps a short explanation of why we have so many indexes adn why filtering IS essential.

In a live transaction processing environment, where any of 20 K to 50 K transactions are open for processing and/or processing at a moments notice, unless you want to wait for the blue bar of death and other probably network issues which could arise by transferring all the data/info for a query, you need filters.

Example. A clerk is sending out open work reports to contractors. Another clerk is entering results received in the mail that morning. If the first clerk runs a query against the shortened 365,000 records transaction header, and the second clerk enters the results for a "moments earlier" open piece (now no longer open), the contractor will recieve that piece as open on his report. Unless the query is run each and every time, or we have everyone else in the office sit on their hand while the open reports are being faxed.

The same situation applies to nearly every piece of work on the system, since we can INSTANTLY provide our clients any info on any transaction they've ordered. We also create a number of reports, at a moment's notice, to report various situations a client wants pertaining to various aspects of the mortgage serviceing industry. If the client is on the phone and needs a report of properties reported first time vacant (as opposed to vacant) between 3/10/00 and today, which are not secured, but should be on a list to automatically generate a monthly grass cut for it, they don't want to wait 30 minutes to up to over 1 hour, just to have the report ready to fax. And, of course, 5 minutes in to the NON-STOPPABLE query run (slowing down your network), they decide they want to change the info they need. Since they needed the report 15 minutes ago and it's 30 minutes before they leave for the night, they aren't pleased. Being able to generate/obtain info immediately is one way we've beaten all of our competition and gotten their clients. If you can't do it, you lose clients. And, byt the way, you'd have to stop the clerks from processing during the blue bar of death so only valid data gets added to the report.

Example. You're talking with a client who has their lawyer on the phone and he needs some info for a case while he's in a 5 minute recess but you query takes 30 minutes to run guess what? You've just lost a client.

His reference to being able to remove 10 indexes without losing functionality only applies because we were forced to create entirely new subsystems which removed the need for them, and added a master table for other processing. Sure, if you want to do major rewrites WHEN YOU SHOULDN"T HAVE TO except for the shortcomings in a language, you can fix almost anything. Most of the other filtered indexes were troubleshooting, which are now also replicated in the other subsyetems. Hence, they weren't removed, just moved to other tables where they don't impact the main set, except when they are being updated across sets to the new tables, one of which has fewer records so the indexing engine can recover more reapidly. You know, it wasn't so hard to do in Quick Basic and DOS. You just write a good index management engine. Took me all of 3 days to do it there. And it was faster than anything available or created in C. Otherwise I would have learned C.

If we WANTED to blue bar of death reports all day instead of looking at browses or filtering records for a form, then we could eliminate the rest of them. And lose about half the functionality/80% of the speed of our application.

And then our clients.

[Steve Dentler]

RE: Dr Peter Wayne vs Heap Locks; 1-0

Maye so, Jim, but with 27 indexes, I'b less concerned about heap lock failures than seeing a mushroom cloud over the server when the DB was opened.
-Balto Cpa

[Jim Rowlen]

RE: Dr Peter Wayne vs Heap Locks; 1-0

Balto,
Believe me. I fought against almost ever index I had to put in there (1 less of anything is 1 less to get broken and/or slow you down). Ask Elliott. Sometimes I became (hmmm, "family forum") "somewhat belligerent."

A5 is quite stable and powerful, except for a few indiosyncrousies. These might not even exist for other users (over the last 30 years I've tended to push development software to its limits, which is why I've been on so many beta teams. Unfortunately I've been too busy to really get to play around with v5 - yet). I still have strong hopes for being able to "almost automatically" convert v5 forms to web pages (with a script or two).

The only time any mushroom clouds have been noticed in our office was when "discussing" adding indexes and certain features with Elliott. It's nice to know he has a high stress level. (And that I, apparently, have a high termination threshold.)

[whanigsberg]

RE: Dr Peter Wayne vs Heap Locks; 1-0

Hi Jim:

Does the approach suggested by Ira Perlow have any use in your type of application?

Thanks,
Bill

[csda1]

RE: Dr Peter Wayne vs Heap Locks; 1-0

Because of the way LQO (Lightning Query Optimization) works, you must use the method I outline in one of two ways.

1st way is to use ranges. According to Peter Wayne, he says that Alpha 5 will eventually discontinue this method as it is incompatible with SQL. Assuming that this is so, then the 2nd method applies.

2nd way is to create a calculated field in the database (we'll call it INDEXKEY) that has the equivalent expression of the index. Now use this in in expression in a query expression similar to;

BETWEEN(INDEXKEY,"0","0~")

This will create a LQO and thus be equivalent to the filtered index without using the range method. The drawback here is that you are wasting some extra disk space storing the INDEXKEY values in the database as well as in the index file.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[csda1]

RE: Dr Peter Wayne vs Heap Locks; 1-0

Steve,

While I agree less indexes is more, In A5 with a combined index file, the locking/unlocking issues are much less that A4 was with even 2 or 3 indexes. I would doubt that there is a significant speed degradation in Alpha 5's operational use of indexes no matter how many there are. Obviously, reindexes have more to do, and a save has to update all indexes who use fields that have changed (or all for an enter). But for reports, finds, queries I doubt there is much speed difference between 1 index and 50 indexes.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Jim Rowlen]

RE: Dr Peter Wayne vs Heap Locks; 1-0

Ira,

I agree with you to a point. In a few tables we have a field called ReportKey which is a concatenation of fields used in a majority of reports, defined as a calculated field. One reason this was done was that we noticed that reports required groupings which were frequently the same (which resulted in the creation of ReportKey). Even with indexes exactly matching the report, we noticed that groups resulted in dramatic speed losses. This is understandable and to be expected. Still, the blue bar of death gets to be annoying when you spend 2 to 5 hours a day watching it process. The concatentation speed generation of these reports to nearly instataneous. Where they slow down is when reports are created for faxing to each client where you obviously wouldn't want to include client A's info on client B's report. We use the "between" paramter in the report.print/preview statment to further define the data, which is where the slow down currently occurs. Comparing extra drive space requirements to overall speed and efficiency makes this a no-brainer. Especially when considering that Maxtor sells a 7200 RPM 60 G drive for $300 (my early single-sided 8 inch floppy drive and paper tape reader both cost more than that).

As far as adding a field to indicate which would eliminate the filter a few issues come to mind. The most obvious is that real-world applications are living "things" (sometimes acting like a petulant little child, constantly evolving to meet the needs of the busines as dictated by clients' requirements. While in small tables this isn't too bad, it was disasterous in v1.02 with tables of over 500K records. Restructuring a table could literally take all night (systems were a bit slower then as well). While restructuring is almost instantaneous now, it still takes a while in larger tables to add the extra space (at least these days you don't have to pre-allocate space on drives for datatables any more to get acceptable speed). While v4.03 apparently doesn't go through the "check and modify all sets" stage, adding a field to (currently, in our case) a smaller 365K record table, it still takes a while.

For smaller datasets, and/or where an "on the fly" report isn't immediately required (example: tornado hits area, client needs report of all addresses inspected over the last 60 days matching the zip codes and other client-defined info, for immediate resolution of whatever they need it for), then I agree with you. In fact, my database of DVD and VHS movies uses fields to indicate various "things" which eliminates the need for filters. When one is loaned out, a flag is set.

I will, however, look the method you suggest in more detail. While it might not work in some of our situation, if it eliminates even one more filtered index would make it an attractive alternative. Unfortunately, at this time, the only tables I can think of where it would work for us have fewer than 5K records each with the fields the indexes are filtered on rarely, if ever, being changed.

All I have to do is figure out how to make the process apply to browses without having to type the LQO definition in each time.

[csda1]

RE: Dr Peter Wayne vs Heap Locks; 1-0

Jim

"In a few tables we have a field called ReportKey which is a concatenation of fields used in a majority of reports, defined as a calculated field. One reason this was done was that we noticed that reports required groupings which were frequently the same (which resulted in the creation of ReportKey). Even with indexes exactly matching the report, we noticed that groups resulted in dramatic speed losses. This is understandable and to be expected. Still, the blue bar of death gets to be annoying when you spend 2 to 5 hours a day watching it process."

You may be using a range (because this is what a genie creates for a button most of the time) in combination with a filter that will not work together for LQO. Use 1 or the other, but not both. If you do, the speed may go up significantly.

"The concatentation speed generation of these reports to nearly instataneous."

I don't understand what you mean by "concatentation speed generation".

"Maxtor sells a 7200 RPM 60 G drive for $300"

I far as I know, they only have a 60gig 5400 rpm drive. IBM makes a 60 Gig 7200 RPM drive for about $600. Alternatively take 2 Maxtor 40 gig, 5400 rpm drives and raid them using a promise (http://www.promise.com) raid controller (or modify their cheaper Ultra 66 controller per http://sysdoc.pair.com/ (Tom's Hardware Guide)) for about $700 total. P.S. My first disk storage system was a 5.25" North Star Floppy system (Only about $1200) holding a wopping 72k per floppy. WOW!!!

"Restructuring a table could literally take all night (systems were a bit slower then as well)."

If it's a one time thing, who cares? The idea is to create a calculated field holding the index key for every index that currently has a filter (unless you use ranges, which don't need this at all). At most the width of the field needs to be 100 bytes (I beleive that is the maximum key length) or smaller if the key is smaller.

"All I have to do is figure out how to make the process apply to browses without having to type the LQO definition in each time."

I don't understand what the issue above is. In general, a user should NEVER actually enter any expressions in my applications. It always has them fill in start/end dates etc. These are then converted to an expression that matches an appropriate index.

Also, one other methodology that can speed up reports is to duplicate a table that serves as a template to the private A5 directory (on the local drive for speed), then copy the desired records to this local copy preserving the data dictionary that has the appropriate reports in it. Now run the report from there. You will find in almost all cases, the report will run faster than watching the multiple passes though the records on the network.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Jim Rowlen]

RE: Dr Peter Wayne vs Heap Locks; 1-0

We discovered in September (started converting to v4 in late August) that ranges don't work from a script even though they are present in the genie (which we used that time to see how v4 did it). We don't use genies for much of anything.

The "concatenation speed" line was a typo. What I meant was that by using this concatenated field the report generation was almost instantaneous.

I could easily be wrong about the $300 Maxtor 60G only being 5400 i/o 7200 rpm. I just glanced at it at Microcenter without interest since my home system has 2 Maxtor 7200/40 G drives and 3 Quantum Atlas SCSI drives. Having well over 100 G I don't really need to add another quite yet. (In about 6 months I'll be upgrading to SCSI drives running 160, if the controller price is closer to my target by then).

Let's see. Up to 100 spaces, x @ 20 fields, x over 365K records. Guess it won't work in a couple cases. For the smaller tables it could. If I'm lucky I might get to look at it in a week or two.

Speaking of old systems, didn't you love write wrapping all those damn little RAM pins? It wouldn't be so bad now, with the chip changes over the last 5 or 10 years. Wire wrapping an S-100 4 K RAM card used to take forever.

[Jim Rowlen]

RE: Dr Peter Wayne vs Heap Locks; 1-0

Ooops, another typo. I keep typing "write" instead of "wire" and "type" instead of "typo." Must be getting close to bed time (been at it since Tuesday morning).