|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2005-05-04 : 06:17:15
|
Morning allHope you have all been enjoying the glorious summer weather (although it is actually pi55ing it down outside at the moment). Back to business, for a change, I am in a team to develop an online indexing system for the retrieval of documents for a large customer base (so far, everything I have done has been small inhouse systems). The database will sit infront of a retriever system, that contains all the documents, and contains enough information about the documents for retrieval. Unlike the other systems, this will need to support 1000's of requests per second. I have absolutely no idea what requirements/considerations I have to take into account in order to produce a database that can cope with this volume. Can anyone direct me to some articles/information that can assist me? At the moment, the only thing I can think of is some serious hardware, and a good indexing system for speedy retrieval. With regards to indexing, I have used an auto increment identity key for the primary keys, however, would it make more sense to use semantic PK's for the clustered index, as this is what the user will be searching on? For example:CREATE TABLE dbo.Customer ( CustomerID int IDENTITY (1, 1) NOT NULL , CustomerName varchar (35) NOT NULL , AccountNumber varchar (10) NOT NULL )CREATE TABLE dbo.Document ( DocumentID int IDENTITY (1, 1) NOT NULL , DocumentNumber varchar (20) NOT NULL , DocumentDate smalldatetime]NOT NULL , CustomerID int NOT NULL , StartPgNum smallint NOT NULL , NumOfPages smallint NOT NULL , JobID smallint NOT NULL , PostCode varchar (8) NOT NULL )CREATE TABLE dbo.Handset ( HandsetID int IDENTITY (1, 1) NOT NULL , HandsetNumber varchar (12) NOT NULL , DocumentID int NOT NULL )CREATE TABLE dbo.Job ( JobID smallint IDENTITY (1, 1) NOT NULL , JobNumber varchar (20) NOT NULL , JobDate smalldatetime NOT NULL ) The user will initially search on Account Number, then Account Number and CustomerName, then Account Number, CustomerName and DocumentDate, then AccNum, CustName, DocDate, and DocumentNumber, then finally, all the previous and MobileNumber.Therefore, would the primary keys be more suited as a combination of, for example, on the Customer table, 'CustomerName and Accountnumber'?Access to the data source is provided through a stored procedure. Is this the best method for data access? What other methods should we consider for performance (such as embedded sql, views, etc), but also for security? Sorry for the long winded post! Thanks in advance!Hearty head pats |
|
Performance considerations for online db system