May 13, 2011 at 2:37 am
I want to improve performance on a table
Current Structure
CREATE TABLE dbo.Process_Volumes (
pKey INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
ProcessDate SMALLDATETIME NOT NULL,
Type CHAR(2) NOT NULL,
VolumeA SMALLINT NOT NULL DEFAULT(0),
VolumeB SMALLINT NOT NULL DEFAULT(0),
CONSTRAINT [IX_Process_Volumes_RR] NONCLUSTERED
(
ProcessDate ASC
)
);
ProcessDate/Type combinations should be unique. (though a few anomolies exist in the data - but these are old and probably erroneous)
How the table is used
1) batch process enters a months ProcessDates in advance, creating blank records for all Types
2) Frequent Reports are run on date range/ single Type combinations, but usually only the previous/current and next months
3) Initially VolumeA & B are updated individually using a user interface for a Type
4) Frequent automatic processes update VolumeA and B - minimally several hundred times a day for the current & next few day's records. Fast updates here are critical.
5) automatic processes frequently query the volume data - based on date/Type. Locking can be an issue at peak times.
In all cases the update process gets the current value, performs some calculation then Updates using the pKey
The change required is to Add a new field Model, so the new data structure is
CREATE TABLE dbo.New_Process_Volumes (
--- pKey needed?
ProcessDate SMALLDATETIME NOT NULL,
Type CHAR(2) NOT NULL,
Model CHAR(2) NOT NULL,
VolumeA SMALLINT NOT NULL DEFAULT(0),
VolumeB SMALLINT NOT NULL DEFAULT(0),
--- index needed?
);
The ProcessDate/Type/Model combination is unique
Reports will now run on dateRange/ for a single Type/Model combinations
Obviously an index or key on ProcessDate/Type/Model would be advisable (or should it be Type/Model/ProcessDate - as usually a single Type/Model is queried for a range of dates
My question is this: To get the most efficient update and query:
a) Do I continue to use an INDENTITY as primary key - if so should it be clusted and create an index on Date/Type/Model?
b) or should I use the unique ProcessDate/Type/Model combination as primary key and cluster that and change the updates to use these 3 fields - (with no IDENTITY)
c) or keep the IDENTITY - but not use it as primary key or cluster and create a primary key on ProcessDate/Type/Model
or is this kind of thing impossible to predict in advance?
Also - how to I query the system tables to see if any foreign keys reference the identity column?
May 14, 2011 at 11:40 am
Why do you have a dbo.Process_Volumes and a new dbo.New_Process_Volumes Tables?
As a personal preference I would not name the PK column Pkey.
Is may be a remote chance that the combination of ProcessDate and Type may not be unique?
You may want to go with the Identity column as the Primary Key
and the Date Column as a Clustered Index and the Type Column as a non-clustered index.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply