Proper database design can eliminate later debugging

Whenever you create or modify a Web or other application, problems can creep into the application code or the database. Knowing where to start looking for a problem and solving the problem at its root takes skill and diligence. As a follow-up to my article "Debugging with Visual InterDev" (Web Dev, August 2000), I’ll describe some problems I encountered with Web applications and how I resolved them. I’ll also look at how you can prevent problems in the first place with proper application testing.

First Problem


A customer recently called me about a database application performance problem, reporting that processing on inserts took 10 to 15 minutes each. The customer thought that SQL Server triggers were the source of the problem. This comment led me to ask the customer about the database architecture.

I asked, "What does the data structure look like?" My contact described two tables. Next, I asked, "How does your application use the tables when the problem occurs?" The customer replied that the SQL Server system fired a trigger upon an insert into one table that looked up the inserted value in another table. Then the SQL Server system modified the existing value in the lookup row if found. The trigger then completed the insert into the first table. I asked, "What are the foreign keys and indexes on both tables?" My contact said, "What is a foreign key?" The customer had no primary keys or indexes on either table, much less a foreign key linking the tables. Adding primary keys and indexes was the first step in solving this customer’s performance problems. Adding foreign keys in the secondary table completed the solution.

The database schema is the logical starting point when you’re investigating database problems. But in this case, because I didn’t have access to the customer’s database schema, I could diagnose the problem only by asking the customer questions about the schema. You can use the visual database tools in SQL Server or Visual Studio to change a database application’s structure by adding primary and foreign keys and indexes. Figure 1 shows a simple table with a primary key (the column name with the lock symbol) in the Table Designer in Visual Studio. I opened the Table Designer by right-clicking the Tables folder in Data View, which Figure 1 shows in the bottom right corner, and selecting New Table from the shortcut menu. I set the primary key by clicking the column I wanted for the key, then clicking the key symbol in the small toolbar in the top of the figure.

SQL Server Books Online (BOL) defines a primary key’s constraint actions this way: "When you specify a PRIMARY KEY constraint for a table, Microsoft SQL Server enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries." The primary key uniquely identifies a particular row and provides an index into the table. A primary key uses a primary key constraint to enforce entity integrity. You can use the index to look up rows in the table later. To reference a table, you usually create a foreign key in the secondary table that links back to the primary table. Figure 2 shows the foreign key in the example table; CustomerID is the foreign key in a table named CustomerStuff. You can add a foreign key by clicking in the border to the left of the name of the column that you’ve designated as the primary key. Then, press Ctrl+C. Next, click in the Column Name column of the secondary table (the one with the foreign key) and press Ctrl+V to paste the column name into the table. This completes the foreign key setup but doesn’t put any constraints or triggers in place to force the use of the foreign key.

Each time a user enters information into CustomerStuff, the CustomerID value is checked against the Customers table; if the value doesn’t exist in Customers, the insert will fail. If you anticipate that many queries will access the secondary table and do lookups by CustomerID, you need to add an index on CustomerID to speed up the searches.

Next Problem Set


The next problem concerned the way in which Internet Information Server (IIS) handles Active Server Pages (ASP) applications. My client reported poor performance for a high-volume, Web-based business application that used SQL Server for the back end. The Web application’s pages loaded inconsistently. For example, a page might load in 1 to 2 seconds one time, and 10 minutes later, the page might take 20 to 30 seconds to load. This inconsistent performance led me to question whether the problem was in the database, the ASP code, or the configuration of IIS.

By executing the application, I discovered that the source of the problem was the way in which IIS was caching ASP code. I executed the application on one PC, then immediately brought up the same page on another PC. Performance was fast. However, if I then used several other pages, the caching algorithm would eventually kick out the original page from the cache, and the page would take a long time to load again. By looking at the page’s ASP code, I found that each page was pulling in 3,000 to 4,000 lines of ASP code from include files. The process of loading and compiling that much code was slow.

Resolving this problem involved migrating much of the script code to Visual Basic (VB) COM classes. This setup removed the ASP compilation issues and the application then performed well consistently because the fewer lines of ASP code either stayed in the cache or, when reloaded, the fewer lines of code took less time to load. The code in the COM classes doesn’t require compiling each time the page loads because VB compiled the code into an executable file. The COM classes also provided objects that were easy to reuse, effectively reducing a maintenance problem that hadn’t occurred yet but would in the future.

Planning


When you’re troubleshooting Web application problems, examine the application’s basic elements to ensure that the most obvious—but often overlooked—design mistakes aren’t a contributing factor.

First, carefully design your database, making sure that it includes the proper primary and foreign keys, indexes, and constraints and triggers. I prefer designs that are based on entity relationship diagrams in which the entities are objects modeled on business objects. Overlooking the design step or taking shortcuts in good database design leads to problems such as the first one I described in this article. For more information about database design issues, check out Ron Soukup and Kalen Delaney, Inside Microsoft SQL Server 7.0 (Microsoft Press, 1999); Michael Otey and Paul Conte, SQL Server 7 Developer’s Guide (Osborne/McGraw-Hill, 1999); and Ken Spencer, et al., Programming Visual InterDev 6.0 (Microsoft Press, 1999).

Second, test your application. You should use tools such as SQL Sever Query Analyzer to test your SQL code. Then use tools such as SQL Profiler to monitor how the SQL code is performing. SQL Server and ADO behave differently depending on the type of queries you send to the database. For instance, SQL Profiler will show that executing a stored procedure with the Exec statement is a different process from executing the same stored procedure with the ADO Command object’s Execute method with the command type parameter set to indicate a stored procedure. Using Exec sends the stored procedure to SQL Server as a dynamic SQL statement, whereas using ADO’s Execute method invokes a remote procedure call (RPC).

Test the application early in the prototype stage and at various points in the development process so that you can find code or setup problems and fix them before you base an entire application on a bad design. You can solve application problems more quickly and less expensively early in the development process than after the application is in production.

Third, use a tool (such as the Web Application Stress Tool from Microsoft, which you can find at http://homer.rte.microsoft.com) that runs your application under load for a certain time period so that you can monitor the server and watch for performance problems associated with IIS, COM+, or Microsoft Transaction Server (MTS) and SQL Server. Some systems such as SQL Server require running with a load to let the system conduct self-tuning. After the Web site has run for some time and the automatic tuning process is finished, you can tune each of the IIS, COM+, and MTS components to improve performance. After you’ve finished tuning, you can put your application into production.

Cover the Basics


Although many of the tips in this article are common-sense practices, I’m surprised at how often people overlook them. For example, prototype testing is a well-known technique for finding problems early in the application development cycle, but many people don’t use it. The first step in any successful project, including Web application design, is ensuring that the basic elements are covered.