Most important - design the application so that it saves the user time.
If you save a user 50% of his effort to do a job (start to finish - not
necessarily an individual task), it *may* not matter that a certain
operation in the application takes a minute to complete (of course, you may
want to shield the user from having to wait for extended operations to
complete - you don't want him or her staring at the hourglass).
Next most important - design your database and database interaction so
that it performs reasonably:
On the database side there are four items of PRIMARY importance:
1: Design your database "properly" - a normalized database design is
usually a good starting point. Occasionally (but not as a rule) you may
have to denormalize something - but only do so as a result of testing
showing the need - and AFTER optimizing the database in the normalized
design.
2: Create indexes for columns that are frequently used as important
selection criteria, sort criteria, and/or used in joins. If you're not an
expert on such optimization, SQL Server includes an Index Tuning Wizard that
is usually fairly good. It bases its recommendations on a trace containing
those queries you need to optimize for or a representative sample of your
application's operation (collect data for a day or two of testing or
production use).
3: Optimize your queries. While specifying the column list as opposed to
SELECT * is a best practice, the performance impact is relatively small.
But constructing a query that returns as few rows as possible and makes the
best use of existing indexes, etc. will make the biggest difference.
4: Make the database do the work - construct SQL queries (and/or stored
procedures) that deliver data as close to your "final product" as
possible. This means that you should minimize the number of round trips -
for instance, by minimizing the number of queries to obtain a set of data.
These four items alone usually mean the difference between a snappy database
application and a sluggish one. Making mistakes here can mean that your
application, which could have finished a task in a second or two, may take tens
of seconds up to several hours (I have seen days in some extreme cases). So
your impact here can mean several magnitudes in terms of application
performance.
Additional tweaking such as avoiding SELECT *, using stored procedures
instead of dynamic SQL, etc. are best practices that in some cases will
deliver noticeable performance impacts, and in some cases won't. They do
tend to impact scaleability, which in turn may impact cost.
Using the "right kind of field" is more of a maintainability issue. Sure,
an index on a numerical column type can provide noticeably better
performance than one using a (typically much longer) character type. Also,
indexing on strings needs to consider case, collating order, etc., while
numerical values are much simpler.
I would never store, for instance, a date value or a numeric value in a
string. Not because of the performance, but because it violates every
aspect of typing variables and is likely to create a problem at some point
(someone is bound to get an invalid character or format into the data
somehow). The performance is a side effect as far as I'm concerned. All
data should be stored in the "proper type". Period.
Don't make a decision on whether a column should be smallint or int based on
its storage size or performance. It is the "value domain" that determines
what type it should be. If it represents a value that has a reasonably well
defined range, that is what determines its type. Examples of this would be
a value referring to individual bones in a human body. The range for a
"bone number index" is well defined, and there would definitely be no reason
to use anything larger than a smallint. But although an adult has only 206
bones, a newborn has over 300 (some of them fuse as we grow... information
from http://yucky.kids.discovery.com/noflash/body/pg000124.html and NOT
DOUBLE CHECKED), so a tinyint would not work. Even if your application
started out by dealing only with adults, consider the potential for future
enhancements to include a value outside the allowed range.
Third most important for performance:
Your application algorithm may have a profound impact on performance. You
can calculate the fibonacci numbers by brute force - or you can use a math
formula. The math formula is magintudes faster. The principle applies to
computer software. A nested loop is basically an exponential performance
curve - as the loop sizes grow, the performance degrades with the product of
the two loop indexes. And it goes beyond loops - the way you design your
application will have an impact on its performance.
Fourth most important for performance:
This is where you look at performance impact of individual statements and/or
sections of code. In this category, you may find things like using
disconnected recordsets and/or GetRows(). You establish your best practices
to avoid the major performance hits.
The final step in optimizing application performance is actually measuring
the performance and determining if there are any areas that provide
insufficient performance. Once bottlenecks are identified, determine how to
best improve performance in that area (or if you can avoid it altogether).
Measuring/testing the performance is important, and neccessary regardless of
what else you've done to achieve good performance. If you don't measure
it - your customer(s) will... :->
Finally, my favorite story about optimizing a database/application. This is
a true story - I did this.
A company I worked for supported a third party application using a SQL
Server database. The application had been in use for several years and
performance had always been quite good. In the early part of 2000, the
application suddenly started taking forever and frequently experienced SQL
Server timeouts. Our application specialist and another developer had been
debugging the problem for days, and I was called in. After being explained
what they had found out, and investigating a few different scenarios, I
determined that when given a specific set of values to search for (in this
case a date string starting with "00-"), the SQL Server optimizer decided
that the selectivity on the index for that column appeared higher than the
column that had the most productive index for the query, it would use the
(less productive) index, and the query would take forever.
Since the particular index was useful in other queries, we could not delete
it. Since we did not have control over the app, we couldn't modify the
query or include any index hints. We had tried to rebuild the index, but
that did not matter. So it appeared that we were stuck.
But - since the problem was deemed due to the perceived selectivity of the
index - we solved the problem by inserting 40,000 dummy rows into the
table - with the same initial value ("00-"). After that, performance
returned to normal!
It should be added that neither Microsoft nor our company was able to
reproduce the issue - so apparently there was some discrepancy between our
customer's environment and ours and MS's test environment. Although it was
claimed that the customer had the latest SQL Server (6.5) SP at the time,
that may not have been the case - I could never get that verified.
Tore Bostrup is a software developer for Team IA, Inc., in Columbia, South Carolina. Born in Oslo, Norway, he has a master's degree in computer science from the University of Oslo. He has twenty-five years of experience in software support, management, consulting, and development. Tore can be reached at tbostrup@telocity.com.
XCache combines dynamic content caching technology with content delivery network (CDN) support options, file compression and a whole lot of manageability features to help e-businesses deliver superior web site performance and reliability. You'll appreciate the administrative ease, your
visitors will appreciate increased page delivery speed.
XCompress works by compressing outgoing text between the Web server and the client. Page response times may improve by a factor of three or more while overall bandwidth use can drop by two thirds or more.
XCompress runs on Windows 2000 and Windows NT 4.0 and is tightly integrated with Microsoft Internet Information Server (IIS) with MMC and COM interfaces.
XTune 2.0 is the most powerful tuning application for IIS 4 or IIS 5 ever
conceived. Indispensable to the enterprise and straightforward, this web
tuning tool allows you to configure hidden operating system, network, Active
Server Pages and Internet Information Server settings for better
performance, without any additional hardware or software.
This version scans your system more deeply, offering more
performance-enhancing recommendations and greater insight into your web architecture. The Performance Wizard guides and teaches you throughout the complete tuning process, so you can learn while making your box run better than ever.
Performance monitoring helps organizations identify performance bottlenecks. The problem is that with so many performance numbers available, how do you know which ones to watch? This article helps you identify which are the critical performance counters in a SharePoint Portal Server environment and explains how to monitor them. By monitoring performance regularly, organizations can recognize performance trends as they develop and prevent problems before they get out of hand. [Read This Article][Top]
There is broad-reaching debate about remoting, Web services, Enterprise Services, and DCOM. In short, it is a debate about the best technology to use when implementing client/server communication in .NET. Rocky Lhotka shares his thoughts on the issue while offering clear explanations of basic application architecture terminology. [Read This Article][Top]
This article examines some of the new and exciting caching features in ASP.NET 2.0 and shows how to implement them in Web applications. [Read This Article][Top]
When it came time to find a technology for its massive upgrade, Match.com chose .NET. Has the online dating service's partnership with Microsoft been as successful as the relationships it has established for many of its millions of members? Read on ... [Read This Article][Top]
Narayan Veeramani shows how ASP.NET developers can improve application
performance by caching data stored in an Oracle database and keeping
the cached data in sync with the data in the Oracle database. [Read This Article][Top]
Ever developed a Web application that requires extensive processing? Ever had long running Web pages that often time out in the browser? Greg Huber reveals a simple technique that uses Microsoft Message Queuing (MSMQ) and the System.Messaging framework to handle long running Web processes. [Read This Article][Top]
As IT professionals try to reduce the cost of operating their Web sites, they should consider reducing the amount of bandwidth usage. Learn how to successfully compress your HTML output and save money on your monthly bandwidth. [Read This Article][Top]
Maintaining a large Web farm is both costly and unnecessary. Learn how to reduce your Web farm to just two servers in this controversial article by Wayne Berry. [Read This Article][Top]
Members of the 15 Seconds discussion list put together a couple of scripts to benchmark methods for removing duplicate items in a string list. [Read This Article][Top]
Mailing List Want to receive email when the next article is published? Just Click Here to sign up.
