Question Increase In Performance - Type Ii Storage?

Hi All,
We are working on a migration project from Progress 9.1E to 11.3.

I theoretically understood how a Type II storage could increase the performance of a system. Is there a way that I could see it practically? For an example write a complex query and run it on Type I and Type II storage and will I see a difference in execution time? Or do I have some other way to make sure Type II storage is making an impact on performance increase. Please advise.

Regards,
Saravanakumar B

Yes. Of course. You could certainly write queries and test it. Testing is a good thing. People should do more of it.

But there is no question at all about it. Type 2 storage areas are faster than type 1. If you have a test that you think shows otherwise please share it. In the nearly 15 years since type 2 areas became available I have yet to see such a result. I'm always excited to see something new but I won't hold my breath

FWIW -- with v9 you can get some (but not all) of the benefits of type 2 storage areas by isolating each table into its own private storage area. It won't be as good as real type 2 areas but it will be better than a consolidated type 1 area.

If you moving from 9.1E you might as well go to 11.6.2 instead of 11.3.... and make sure you are moving to a 64 bit version of OE for the database server (to match your 64 bit OS).

The short version of why Type II areas are better than Type I areas... Type II areas make sure that a single database block only holds data for a single table. This means that fewer physical reads are required to read the records for that table as well as fewer blocks used in the buffer pools to cache those blocks/records.

Type I areas end up having records from multiple tables mixed in a block. This means wasted IO and wasted buffer cache.

How much faster Type II areas are will depend on how well you set up the new areas and how your old areas were set up. Run a dbanalys and see how many different tables/indexes are in the same area. There is a lot of information available there to give you an idea of how bad things are.

This utility can give you a good idea of how many distinct blocks a table lives and how bad the intermingling of data is... the disclaimers about trying small tables first should be taken seriously.

Downloads - OpenEdge Performance Tuning

This page also has a high level overview of the major differences between Type I and Type II areas Database Tuning - OpenEdge Performance Tuning

catch.saravana said:

I theoretically understood how a Type II storage could increase the performance of a system.

Click to expand...

Type II storage areas will help with performance in several ways, but having a proper, modern structure is useful and necessary for more reasons than just performance. Read this post for more details.

Also, remember that performance is fluid; it changes with time. Measuring performance differences between one configuration and another, in a meaningful way, requires more than just running two tests and seeing which one was "faster". It's also a question of how the system will evolve (or devolve) over time. A database with data loaded into well-configured Type II areas will be much healthier and better-performing with the passage of time than a DB with data left in Type I areas. Also, some performance options, e.g. Table Partitioning, are just unavailable to you if you still have your data in Type I areas; no measurement required.

TheMadDBA said:

If you moving from 9.1E you might as well go to 11.6.2 instead of 11.3.... and make sure you are moving to a 64 bit version of OE for the database server (to match your 64 bit OS).

Click to expand...

I second that. OE 11.3 is now retired. Details:
OpenEdge Product Availability Guides and Life Cycle Guide - Wiki - OpenEdge General - Progress Community

Sorry, My bad - We are migrating to 11.6 not 11.3.

Thanks all, will read through the ppt's, docs and run some queries and post questions, if any.

@Tom - I will share the numbers with you once I test the same.