›
Welcome to the SRP Forum! Please refer to the SRP Forum FAQ post if you have any questions regarding how the forum works.

Is my table too large?

prattspetsprattspets
in General
I'm having some trouble with a Native OI table and would like some advice from those of you who have much more experience that I do. :-)

I have a table with about 1 million records. I made a sandbox version of the application and added about 300K more records to the table. Before I added the extra records, all the indexing worked great and there was great performance from the table. After adding these records the table is acting like there is no indexing whatsoever. I have rebuilt the indexes, to no avail.

Any ideas on how I can troubleshoot this and get that great performance back?

Comments

  • DonBakkeDonBakke
    Dan,

    I can assure you that your table is not too big. Revelation database tables are well known for being able to handle large numbers of records rather well.

    Your problem could be the result of many things so it will be hard to provide you a comprehensive list of items to check for. While I think you will prevail with a little bit of advice and perseverance, there might come a time when you may want to get a consultant to assist you directly.

    How is the performance when you attempt to read a record with a known Key ID? If this works reasonably well and responsively, then I would rule out that this is a problem with the OS. I might have suggested that your table is still flushing the indexes, but you said that you have rebuilt them, so that seems unlikely.

    The probable causes of your indexing issues are as follows:
    • Bad data or bad Key ID. Indexes only work well if the data they are indexing is properly defined and without unexpected system delimiters.
    • Bad dictionaries. Indexes only work well if the dictionaries are properly defined. For instance, are the defined lengths at least one character more than the longest element in any indexed column? The same rule applies to your Key ID columns (and @ID as well if you have that in your dictionary.)
    • Bad DBT file. Many problems can occur if you do not have a DBT (DataBase Template) file properly built. This happens when you perform operations that add or remove an MFS (such as removing all indexes from a table) without properly updating the Database Definition. You might want to start by creating a new DBT file (usually this can be done by copying SYSPROG.DBT and naming it as YourApp.DBT (after backing up your original DBT file first.) Then use the Database Manager to add all your tables and then save the Database Definition.

    Also, I am curious how you rebuilt the indexes. If you only used the option from the Database Manager, that might not be enough (or it may have made matters worse.) There are times when rebuilding indexes manually is a needed. This how-to article on Revelation's Knowledge Base site (no WORKS membership required) will guide you through that process.
  • prattspetsprattspets
    Don,

    Thanks so much for the post! Great stuff!

    From your synopsis of indexing issue causes I was able to find quickly that I had some fields defined with too short of lengths for the data in them. They are working great once again! :-D
  • JaredBratuJaredBratu
    Don,

    Could you elaborate on why adjusting the dictionary column length resolved the problem or where that task is documented? Obviously it worked but I can't find checking the column length as a troubleshooting step and it's tickled my curiosity.
  • DonBakkeDonBakke
    Jared,

    Honestly, I don't think this is documented anywhere. I quickly reviewed my AREV manuals but could not find any information on this. Sad to say, this is one of those arcane bits that have become common knowledge for the most veteran developers but has (apparently) never made its way into any official doc or even into the Knowledgebase (near as I can tell.)

    I don't know if this is by design or just the natural consequence of how the index logic was written. So my following comments are more speculation based on intelligent guessing than anything else. It would appear that index parsing logic relies upon the dictionary length when retrieving the values from the indexed column. I've never been certain why an extra space is required, but I can understand why a length equal to the longest data value would be necessary. I realize this seems very non-variable lengthish. This is perhaps a question better suited to Aaron Kaplan.

    There is another important detail which I omitted previously. The data column length should be based on the converted value, not the internal value. Thus, this is why we always set our date columns to be 11 characters wide. Our normal conversion pattern is D4/, thus we need 10 characters for the converted date. Then add +1 and you have our standard for date column widths.
Sign In or Register to comment.