|
First you must adjust the database design as needed, have
an eye toward current data quality and inventory/information
plans. Question your clients to see if their current database
delivers the information they need. Have the following questions
on-hand; do they need to add fields? How are their accounting
requirements and data quality? Do they need more information
for reporting, for taxation, or for handling inventory management?
Must they add validations? For some databases, to handle validations,
you will have to rely on Web app scripting or coding.
The second thing you must do is tune queries for performance.
For those with only one database, it is a good idea to remind
your clients to review queries. One may be able to improve
the speed of queries fivefold by simply tuning the way queries
are made to the database engine. If you are not an expert
DBA, hire one on a short-term basis. You will need an expert
on the database brand for reviewing the queries of several
clients at a time. Even though the queries are coded in a
Web app, modification requires intimate knowledge of the behavior
of the database engine, the database itself, and SQL. Many
database vendors provide a utility which analyzes queries
for performance, while the best of these offer automated suggestions
on how to fine-tune queries.
Sometimes, improving the performance of queries involves
restricting the number of rows that a query proceeds or reducing
the amount of sorting. Adding indexes may speed the sorting
of returned values greatly. One may also consider reconfiguring
the timeout setting on queries, if the users time out frequently.
One should also consider caching the results of frequently
requested queries. Some databases automatically do this, which
is why a user's first query may be slower than subsequent
queries.
Also, consider using stored procedures, where the query resides
in the database, resulting to more efficiency; your request
to the database would be a simple call. In Web apps, particularly
on ASP pages, the list box can take a long time to load if
the query returns too many items; as the database grows, the
items returned multiply to virtually unmanageable amounts.
In such instance, you could tailor the query to return fewer
rows, or in some databases, you can adjust the maximum number
of rows returned with a simple command. Remember, if you decide
to adjust the maximum number of rows returned; be sure that
you redesign the Web app's GUI to inform users that they won't
receive all possible rows.
The third thing you can do is optimize the database cache
settings for performance. Your necessities for these settings
may differ as your client's web site becomes more popular.
When adjusting cache size properly, web-site visitors get
sufficient memory to use the database efficiently. Some databases
have two caches: a data cache and a procedure
cache. The data cache allows succeeding
instances of a query to execute better than the first.
The data cache is not as sensitive to the number of users
as is the procedure cache. You can to tune it so that the
cache doesn't flush itself too often because it ran out of
space.
Optimizing the performance cache size can be hard to do with
a Web site, since the quantity of users may vary. With a procedure
cache, one can calculate the cache size by multiplying the
memory used by the estimated number of users which visit during
the standard peak on the Web site. Next, add a float of twenty
to thirty percent of the overall size.
The fourth thing to do is review the backup schedule and
procedures at least twice a year. Think whether or not your
clients want more frequent backups, want to use a particular
compression format, or want a mirrored or replicated database
to be always available. See what types of media your clients
work with and where is the media stored. This will help with
the backup strategy for your clients.
The fifth thing: weekly, monthly, or bimonthly, recreate
indexes. The frequency should be based on the number of updates
and deletions occurring. There are two types of indexes: clustered
and non-clustered. Sometimes, non-clustered
indexes can be inefficient; they insert new entries at the
end of the index, in the last data page of the table. The
non-cluster index arrangement can cause contention over the
last data page. Clustered indexes force data into an index-key
order, the result is no contention over the last data page.
Clustered indexes must be rebuilt as more values are inserted
and deleted. Not only must you recreate clustered indexes,
you must check the load factor on them.
The sixth task: Create database views for high performance.
Build views on a databases lets you maximize the throughput
of a database. Since the view can be sorted, it may speed
up repetitive queries that need to be sorted in a special
way. If your database engine has problems with many users
hitting the database at the same time, strategically deployed
views can help you distribute the load. A view can help performance,
and aid security. It is possible to provide a view of your
data to those who should not or need not see all of your data.
The seventh step requires one to communicate about errors
or downtime to clients and visitors. Good error messages may
be passed to the user with a local database. In many cases,
a web database must manually handle errors. As part of the
job of hosting a Web database or creating a Web app, one should
provide useful error messages instead of confusing ones that
present vague information. You should review security semiannually
during the eighth step. Be sure the client isn't using the
default DBA username/password. Recommend your clients change
passwords on a regular basis.
Discuss the need for scheduled software upgrades annually
at the minimum for the ninth step. You can choose to time
the discussion to coincide with their budget cycles.
The tenth step requires you to discuss the need for hardware
upgrades annually or semiannually to improve performance.
This means you will need to add more memory to the database
server and to upgrade to a faster server.
Copyright
© 2005 Database Design Information. Send comments here. |
