Forming PC based DBMS performance expectations

Date: Archived
Product/Release: LANSA for Windows
Abstract: Forming PC based DBMS performance expectations
Submitted By: LANSA Technical Support

Generally AS/400 developers form their perceptions about the speed and methods by which they can access a data base table from their AS/400 experiences. Unfortunately this may be the worst possible place to gain these perceptions.

The AS/400 is a multi-user data base machine. Its data base access is very fast and relatively little attention has to be paid to the impact of DBMS requests on the system or other users.

However, as the market moves more and more towards PC, Desktop and LAN based DBMSs, developers may have to alter their perceptions and change their habits accordingly.

This is a subjective area, and PC DBMS vendors may dispute this, but development experiences so far have indicated:

  • Rarely will a PC based DBMS perform as well as an AS/400 DBMS.
  • Some PC based DBMSs are really only good for the home user, even though they may be sold as being suitable for high volume, multi- user commercial use.
  • Most developers test their applications with very small data sets and will not identify a DBMS performance problem area until after the application gets into the hands of production users.

So, in any context when using a PC based DBMS, please think about the following before implementing an application:

  • There are many hardware considerations. For example, a 486 PC using an old hard drive may have "useable" disk access times 10 times slower than a modern Pentium PC with a newer drive / controller / cache. SCSI drives are generally quite a lot faster than IDE drives. PC hard disk drive performance covers a very wide spectrum and if setting up a system that will have to support a lot of DBMS access then look towards getting a system that has very fast disk(s) and appropriate cache storage.
  • Avoid SX computers. When decimal numbers and high volumes are involved make sure that you are using a DX computer that has a floating point co-processor. Cases we have seen indicate that in numerically intensive high volume data base access a DX computer may be 4 to 6 times faster than an equivalent SX.
  • Think about loads and volumes during the design phase.
  • The most likely area of problems is to do with tables that contain large numbers of rows and SELECT commands. Slow SELECTs are the most common cause of PC DBMS problems. Look especially closely at the use of OPTION(*STARTKEY) and GENERIC(*YES) with no keys in use at execution time as potential severe performance bottlenecks on SELECT commands.
  • Be CAREFUL when using SELECT on a table that you know will have a large number of rows. If there is no index to support your SELECT, then the DBMS will read the whole table. If there are 100,000 rows, then this operation WILL take a long time. Even if there appears to be an index, the DBMS may decide to read the whole table anyway.

However, if you are testing with realistic volumes then you can identify and remove the problem area before it gets to production users.

  • Be RESPONSIBLE. It is no good designing and implementing an application without any thought for load, volumes and viability and THEN finding it is too slow to be viable, and then complaining to LANSA or to the DBMS vendor. AVOID the situation in the first place.
  • SELECT_SQL is the fastest type of DBMS access. It creates imbedded SQL in the generated C code. This is the fastest type of access that can be done. If it is impossible to achieve a viable result using SELECT_SQL then an alternative solution to the problem will have to be found.
  • When very large tables are involved, consider placing them on an AS/400 server and then using "LANSA/Super Server or Remote Function calls to access them, possibly only returning the summarized result(s) in a working list.
  • Prototype and test under realistic loads and volumes any table that is expected to have more than 1000 rows in it. This way, any problem areas can be found and removed before they become critical.

The 1000 figure is totally arbitrary. From experiences with some PC based DBMSs the user may choose to lower this figure to as low as 100 for some PC DBMSs, and for others, raise it up to 5000.

The performance of PC based DBMSs covers a very broad spectrum. After one has been chosen, develop a perception of what its capabilities are and thus at what point it will be necessary to start being careful.

  • Become familiar with the selected DBMS. Read the sections in any guides / manuals that it provides related to performance and tuning. Learn to optimize its performance.