Alpha 5V4 becoming an an unviable product

[Graham Wickens]

I've re-installed Alpha5 V4, I've added more memory, I've deleted all *.mpx files, I've rebuilt indices about once an hour, I've deleted queries, *BUT* I cannot get away from "Heap Lock Failure" and "Unable to add key to tag".
This only seems to happen when I am rationlising the records in my Tables by deleting some and updating others. As the file has over 389,000 records it is not an option to attach the files. Anyone got any more suggestions before Alpha V5 is sent to the great recycling bin in the sky?

[Steve Workings]

RE: Alpha 5V4 becoming an an unviable product

There's quite a long thread on this just a couple weeks ago you might view.

The culprit is probably an index or two (I think it's in the index filters). Perhaps you could post your index order/expressions and filters for us to see. There might be something obvious.

- Steve

[JerryBrightbill]

RE: Alpha 5V4 becoming an an unviable product

Another issue you may want to search on the forum is indexes in general. The errors you are getting are probably due to index problems. I have an app that regularly had the same problems. I was adding and deleting child records from a form for a set. I found that if I closed the form and then reopened it, the indexes are not corrupted and the problem goes away for the most part.

On the Onsave event I open a message form and close the main form. Using the OnTimer event for the form I reopen the main form. On the OnInit event for the main form I close the message form. The operation is automatic and fast and the user has no problem with it.

Also filters on indexes have been a problem, particularly on set linking indexes. I now try to avoid them if possible.

Jerry

[Elliott Lester]

RE: Alpha 5V4 becoming an an unviable product

Jerry:

What do you utilize if you do not use indexes or filtered
indexes?

Regards

[Selwyn Rabins]

RE: Alpha 5V4 becoming an an unviable product

the concept of filtered indexes does not even exists in most database products (to my knowledge). it certainly does not exist in sql databases.

it is just something that exists in the .dbf world (to the best of my knowledge), and was introduced a long time ago before products had query optimization features.

however, since a5 has query optimization, you are generally much better off keeping the number of indexes to a minimum, and having just the necceray indexes that a5 will use for its Lightening Query Optimization. as long as the appropriate index exists, a5 should be able to perform a query for a particular subset of records very quickly.

[Stan Mathews]

RE: Alpha 5V4 becoming an an unviable product

I agree and disagree. I think Alpha is a wonderful product with features and support beyond compare. But features that work "most" of the time are troublesome.

If you don't want ensure that filtered indexes work in 99.99% of all cases, take them out of Alpha. Rewrite the manual to reflect the change or publish an addendum giving examples and explaining the benefits of the alternate method. If you don't like ask_user variables, remove them as well.

Possibly , as a few other posts recently have noted, the problem is that there are too many means of accomplishing the same end and not all perform equally well.

[Finian Lennon]

RE: Alpha 5V4 becoming an an unviable product

[Anyone got any more suggestions before Alpha V5 is sent to the great recycling bin in the sky?]

In addition to what Selwyn has suggested (keeping indexes to the minimum required for LQO) I would offer two additional suggestions.

1. It seems to me that most, if not all, of the critical problems with "Heap Lock Failures" revolve around deletions. So my way of dealing with the occasional heap lock error a couple of years ago was to change the way we did deletions. We stopped trying to do deletions with referential integrity (Cascade deletes) turned on and instead of allowing deletions in the main data entry form, we required that users pop up a "Deletions Form".

Deletions, whether of a child detail line(s) or an entire parent/children set of records, are all done via buttons with Xbasic using a t.change of the zeroth field as described by Peter Wayne in his book. I have no idea if or why this seems to be more stable but we don't have heap lock problems any more.

2. A more radical approach (which I have used successfully with one client) would be to disallow deletes entirely in the problem tables by putting a cancel() in the CanDeleteRecord event for the table. Add a .NOT. MARKED() filter to all indexes and then tell users that from now on they must hit Ctrl-M to delete (mark) instead of Ctrl-D which will now do nothing. Or maybe do something with the OnKey event so that a Ctrl-D really does a Ctrl-M. In any event, with a Ctrl-M and the index filtered, the record will disappear from the screen, which is all most users care about anyway. The marked records can be removed periodically via an operation.

Obviously this would limit other processes that one may have set up to use with marked records. It also becomes necessary to edit all reports to filter out the marked records, but it's a one-time hit. However, if it eliminates the heap lock errors - as I am certain it would - there is a benefit. And there are other ways to mark a record.

Finian

[Elliott Lester]

RE: Alpha 5V4 becoming an an unviable product

Well then, what does A5 consider a reasonable amount of time
to wait for each and every query to be performed? And
does not each query create a temporary index for its use?

[Elliott Lester]

RE: Alpha 5V4 becoming an an unviable product

Finian:

We have daily trouble--just got done spending 25 minutes
with all of my staff playing cards on the computer while the
indexes were rebuilt--that has nothing whatsoever to do
with deletes. All that we are doing is taking the results of an inspection done at a property --20,000 of them--and "key punching" them so to speak into the database.

On a daily basis using A5v4, but not A5v5 we experience
heap lock errors and index problems.

As far as deletes, we have found that if you delete more than 5 or 6 records at a time--from a form or from a browse
you get a heap lock error. And we do not use referential integrity to do it.

[Elliott Lester]

RE: Alpha 5V4 becoming an an unviable product

Creating an Index
After the design has been determined, indexes can be created on your tables in the database.

Microsoft� SQL Server� automatically creates unique indexes to enforce the uniqueness requirements of PRIMARY KEY and UNIQUE constraints. Unless a clustered index already exists on the table or a nonclustered index is explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.

If you need to create an index that is independent of a constraint, you can use the CREATE INDEX statement. By default, a nonclustered index is created if the clustering option is not specified.

Additional considerations:

Only the owner of the table can create indexes on the same table.
Only one clustered index can be created per table.
The maximum number of nonclustered indexes that can be created per table is 249 (including any indexes created by PRIMARY KEY or UNIQUE constraints).
The maximum length of all the columns that comprise the index is 900 bytes. For example, a single index could not be created on three columns defined as char(300), char(300), and char (301) because the total width exceeds 900 bytes.
The maximum number of columns that can comprise the same index is 16.
When you create indexes with the CREATE INDEX statement, you must specify the name of the index, table, and columns to which the index applies. New indexes created as part of a PRIMARY KEY or UNIQUE constraint or using SQL Server Enterprise Manager are automatically given system-defined names based on the database table name. If you create multiple indexes on a table, the index names are appended with _1, _2, and so on. The index can be renamed if necessary.


--------------------------------------------------------------------------------

Note It is not possible to create an index in the current database while the current database is being backed up.


--------------------------------------------------------------------------------

Clustered Indexes
When you create a clustered index, the table is copied, the data in the table is sorted, and then the original table is deleted. Therefore, enough empty space must exist in the database to hold a copy of the data.

By default, the data in the table is sorted when the index is created. However, if the data is already sorted because the clustered index already exists and is being re-created using the same name and columns, the sort operation can be automatically skipped by rebuilding the index rather than creating the index again from the beginning. The rebuild operation checks that the rows are sorted while building the index. If any rows are not correctly sorted, the operations cancels and the index is not created.

Unique Indexes
Creating a unique index ensures that any attempt to duplicate key values fails. If a single query is created that causes duplicate and nonduplicate key values to be added, SQL Server rejects all rows, including the nonduplicate key values. For example, if a single insert statement retrieves 20 rows from table A and inserts them into table B, and 10 of those rows contain duplicate key values, by default all 20 rows are rejected. However, the IGNORE_DUP_KEY clause can be specified when creating the index that causes only the duplicate key values to be rejected; the nonduplicate key values are added. In the previous example, only the 10 duplicate key values would be rejected; the other 10 nonduplicate key values would be inserted into table B.

A unique index cannot be created if there are any duplicate key values. For example, if you want to create a unique, composite index on columns a and b, but there are two rows in the table that contain the values 1 and 2 for a and b respectively, the unique index cannot be created.


--------------------------------------------------------------------------------

Note You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes.


--------------------------------------------------------------------------------

To create an index when creating a table



To create an index on an existing table



You can also create an index using the Create Index Wizard in SQL Server Enterprise Manager.

To create an index using the Create Index Wizard



See Also
Full-text Indexes index create memory Option
DBCC SHOWCONTIG Placing Indexes on Filegroups
Primary Key Constraints UNIQUE Constraints
Fill Factor

[Finian Lennon]

RE: Alpha 5V4 becoming an an unviable product

Just curious, can the key punch users delete records?

Finian

[Jose de Leon]

RE: Alpha 5V4 becoming an an unviable product

Hello Graham,

I think Finian's suggestion is a good one. We have had similar heap lock errors in which we resolved by deleting in a different way. It seems to be related with deletion of records in embedded browse on forms.

1) What we do is "Mark" records for deletion. We make sure that marked records are not displayed or involved in calculation in forms, reports, etc.

2) The second thing is that we do operations that require marking. What we do to avoid the conflict of meaning for "Marked" records and, we have separate indexed table (to keep size small) with three fields: Unique REC ID, "MarkOne", and "MarkTwo" (add more if you need). Rather than using A5's builtin feature for marking records, we simply mark a flag either "MarkOne" or "MarkTwo" marking records for operations for which we can link to master record via the REC ID. I think this can be a little slow if you run batch processing and mark thousands of recs at a time, but it appears to work for us fine, but we don't mark more than a few records a day.

3) Periodically, when nobody is logged in, we run an operation that exports all Marked records to a comma delimited file, we compress it, and store it in Archive folder. We do this in case we didn't really want to delete the records and they need to be brought back.

Naturally, you don't have to do step 3 if the deleted records are not that important.

I hope this helps.

Jose

[Jose de Leon]

RE: Alpha 5V4 becoming an an unviable product

I should add that in item 3), after the marked records are exported, they are then removed from the database.

Jose

[Peter.Wayne]

RE: Alpha 5V4 becoming an an unviable product

Does this happen to you when you open the tables separately and do
the deletions/updating, or only when you work in your set structure?

Does this happen in the default browse or form, or only in
your custom browse or form?

This is an error that I've never seen. I don't have any tables
with 300,000 records but I have several with over 100,000
records. I avoid deletions, however.

[Dan Blank]

RE: Alpha 5V4 becoming an an unviable product

I get heap lock errors when I delete records from forms. So I started opening up the tables from the control panel, and deleted records from the browse of the table itself. I can delete a lot more records at a single instance, however, the dreaded heap lock error will show up. I close the table rebuild the indexes for that table, and then pack the table. I am then able to go back into the table and continue with my deletions.

I only delete records monthly, and consider this part of database management. When a record does have to be deleted to satisfy another user, I mark the record, and wait til the end of the month.

Deletions are the only way heap lock errors show up with me, and I havn't figured out what to do to stop them. I'm just ready for v5.