I have been spending a lot of time at a particular client of mine lately addressing stability and performance issues on a particular Web-based PowerBuilder application. After identifying many issues with the application system, I'm pleased to report an exponential gain in both the stability and, in particular, identified performance.

This started me thinking about the various projects that I have been working on over the past five years - with a subsequent amount of the same issues as the ones I have just been addressing. Many of my friends and fellow developers using similar application development tools (like Java, VB, Delphi, C#, etc.) have also encountered many of the same intrinsic problems. The interesting theme throughout many of these projects though is how they fell into the stability and performance trap.

One of the things I have noticed over the past decade in the IT industry and in particular with our educational institutions is the lack of leadership in educating new developers in the "art" of performance in their application design. It seems like the emphasis has been more on how to complete a functional piece of code than how you might go about designing the same code segment for optimum performance. When the coding assignment works, the student is rewarded even though the stability and performance is dismal. I have often heard professors (and even current IT management) say, "Oh, that's okay, we can just throw some more hardware at the problem." Ouch, that really strikes a chord with me. Maybe it's because the first language I learned was "assembler" and the machines I used originally were 16, 32, and maybe if we were lucky 64K in memory size. Disk drives, readers, monitors, and networking were very slow compared to today's speeds. This forced us to really pay attention to code simplicity and utilizing as many CPU and I/O saving tricks as we could. With today's high-end hardware this attention to coding for performance is dramatically overlooked.

However, when you develop application systems that have extremely high transaction rates, large number of users, massive databases, etc. - you need to pay attention to designing for stability, performance, and recoverability. Today, I would recommend to all IT management that they first start their new developers off coding for the PocketPC. I had a real eye-opener a few years ago when I started coding with PocketBuilder on 200Mhz CPUs with 32M of memory. Wow, was that ever a "blast from the past" and brought back memories of how I used to code for performance. Using the Assembler language, PPE (Problem Program Evaluator) Analyzer, Capex Optimizer, structured programming (Nazi-Sneiderman), etc., tools really helped us in earlier decades develop applications that would "sizzle" on low-end hardware. The PocketPC really forces the developers to code for minimal I/O, CPU efficiency, screen control (painting operations), and database access in order to build applications that handheld users would accept. Just like the Internet today, if a person starts waiting consistently for more than five seconds, most likely they will leave your site and never return.

Once you learn the art of performance in designing applications that run on minimal hardware, these techniques become instinctive and can be applied to mission-critical applications that run on corporate high-end hardware. I'm speaking about applications that support thousands of concurrent user sessions, hundreds of logical business transactions per second, or complex database queries and updates on terabytes of data. A good lesson that I witnessed recently was the new Canada Old Age pension system that was rewritten in Java using a RDBMS that I shall leave unnamed. It concentrated on functional testing and only addressed the performance testing three months before production. Performance and stress testing, however, pointed out average transaction rates of 14-30 second response times and some transactions that were taking 10-14 minutes for a dialog to respond (yes, you read that right). All the development effort of the past two years went down the drain, as management was forced to "junk" the system. The only happy ending was the third rewrite of the application in PowerBuilder with an average of less than a two second response time with 4,500 concurrent users in the peak demand each day.

Why was the PowerBuilder rewrite so successful and what type of things should an application development team look for when designing high-volume systems for maximum performance? The following are items on a short critical success list that I would use to ensure that your next production application meets its mark:

• Solicit developers who have experience in performance coding techniques.
• Design for performance, stability, and recoverability early in your development project and make it part of your application's critical success factors. Agree to a basic service level for your transactions with the user and have the developers design and code to meet that mark.
• Use the "Explain" feature of PB's database painter to locate troublesome SQL before you use it.
• Try to make sure that any business transaction stays below a maximum of five I/Os (guideline). When you multiply the number of concurrent transactions times the average I/O, you can imagine the intense workload that you are imposing on the database server.
• Optimize your database page size and table page ranges. Add indexes where needed.
• Tune your database server for things like buffer sizes, network packet size, dispatch priority (O/S), RAID disk type, etc. You can also apply this to your application server as well.
• Cache frequently used data in data stores or internal arrays. If you are using EAServer, increase your "Connection Cache" size.
• Tune your JVM if you use applets, servlets, and/or JSP.
• Grab pointers to other components and service objects once and hold them as long as you can in "instance" variables.
• Use the "Application Profiling" feature of PowerBuilder to get a roadmap of your application's CPU and I/O usage.
• Address the top 20% of your application performance bottlenecks first (law of "diminishing returns").
• Use the SQLExpert product from Quest Software and also sold by Sybase to thoroughly tune your SQL. Note: You can download a bridging tool (SQLExtract) from CodeXchange to interface your DW's PBSelect statements to SQLExpert.
• Use "hints" in your SQL statements where appropriate to your DBMS.
• Stick to DBMS vendors that support "native" database drivers. Note: Microsoft's SQLServer no longer supports this feature. My timings on ODBC and OLE-DB find them still lacking way behind, especially the new ADO.NET, which offers another layer of slowness.
• Use "binary" searching techniques instead of linear searches - even for the DataWindows' Find ( ) method. Binary searching can usually locate a given row in 3-4 operations, whereas linear searching a large buffer may take thousands of operations.