Several interesting database-development lessons surfaced recently as I helped prototype applications for a client. First, prototyping is essential to building any application. Building a working model of the system helps you learn how the technology works and how your application can use it. Prototyping also lets you get user feedback on sample versions of an application and work out potential performance problems early on.
For example, the prototyping team I was working with talked about using in-process versus out-of-process versus Distributed COM (DCOM) components and how each would affect application performance. (In-process components run in the calling application's memory space, while out-of-process components run in their own memory space.) One developer's testing proved that in-process is fast, DCOM is slow, and the other component technologies fall somewhere in between. What do these performance results have to do with SQL Server? When any part of an application drags, people typically point their fingers at the database and ask whether you've correctly tuned it and all of its stored procedures and such. Understanding an application's overall architecture, instead of just the database parts, can help you design and implement the best database for the application as well as pinpoint problem areas—whether in the database or not.
As we fiddled with our prototype, one of the team members used SQL Server 7.0's SQL Profiler to monitor the server. Profiler showed what SQL Server executed for the SQL statements we sent it and how long the processing took. Profiler also showed the calling application, which can give you valuable information. For example, let's say you switch a Microsoft Transaction Server (MTS) package from a server to a library package. Then, when an application executes a method on an object in that package from within Active Server Pages (ASP), Profiler shows you that the calling process is IIS. Such information can help you find out how an application is really working and how to optimize performance. These insights are just a sample of what Profiler can tell you about your systems.
The prototyping project also raised a question about whether to use stored procedures, COM objects, or both to hold business rules. Is it time to move all business logic into COM classes and forget stored procedures? Of course not. Both stored procedures and COM can yield important benefits, such as better application performance, easier development, and easier maintenance. We need to look at both technologies and apply each where it makes the most sense.
