1) Unless your databases are enormous (10s of millions of rows) I don't
think you'd benefit all that much from splitting your DB up. Give your SQL
server lots of memory (be sure to teach it how much memory it is allowed to
use) and fast fast disks and in general I bet it will be fine.
2) Views are not much better than a simple query from a performance point of
view. A stored procedure, on the other hand, can help a lot because the
access plan is cached. For slow-changing data you could consider making
denormalized read-only tables that are refreshed by SP on a scheduled basis.
3) Locking queries or blocking queries? Locking is ok, it's when queries
are consistently blocked that you would want to examine more deeply.
Examine the queries that are causing the blocks; I would expect them to
mostly be updates, inserts, and deletes, because they require writing new
data and index pages. Could updates be rewritten to update fewer records at
a time? Do you have a delete + insert that could be rewritten as an update
- maybe flag a record as inactive instead of deleting it on the spot? Do
you have long transactions that could be shortened by collecting data before
starting the transaction? Could you use SPs for complex transactions and
benefit from their generally faster processing? If you have a "hot spot" of
inserts for a table where the next record's PK is "max(id) + 1", could you
instead use a random number for the PK (of course you have to catch the
occasional duplicate key error, generate a new random ID, and re-insert)?
-----Original Message-----
From: Lincoln Manning [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 12, 2001 11:48 AM
To: CF-Server
Subject: Best Practices Databases
I am a part of the speed team at a fast growing internet startup. Our team
is basically focused on speeding up user access to our system. We are able
to handle our current load, but we would like to develop a speed growth
plan. My specific questions are..
1. We have hundreds of car dealerships across the country that use our
system based around a few large databases. What would be the benefit of
creating a database for each dealership? Basically splitting everything
into separate databases. Is this a management headache waiting to happen or
would the smaller databases and speed gains be worth it?
2. I know of a few large CF based companies that use large table views
instead of joins for frequently requested information to get information to
customers quickly. Has anyone tried this and what are the implications? Is
there any software to automate this strategy?
3. Finally, I have been using SQL Profiler to track locking queries, and a
few tables and few queries seem to be the most common. Are there any
straight forward remedies. I have been using the (No Lock) option when I
can. Are there other ways to minimize this?
By the way, we are using SQLServer 7.
Lincoln Manning
AVV
[EMAIL PROTECTED]
Ph. 614-340-5139
Fax 614-487-1955
http://www.avv.com/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
------------------------------------------------------------------------------
To unsubscribe, send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body or visit the list page at www.houseoffusion.com
