I have a database that I'm trying to optimize to boost performance. Nearly every data (not state/system) table has fields equal or very similar to the below:
Code:
[ClientID] [int] NOT NULL ,
[Invdte] [datetime] NOT NULL ,
[MTN] [varchar] (16) NOT NULL ,
[BTN] [varchar] (16) NOT NULL ,
[WTN] [varchar] (50),
[CarrierID] [int] NOT NULL ,
[CDGuid] [varchar] (50) NOT NULL,
[ReportingAccount] [uniqueidentifier] NULL,
[InvGUID] [uniqueidentifier] NULL
ClientID and InvDte are the most common search fields in most of the tables. 99% of the time, ClientId is included in the search(the data is almost useless without it). InvDte is usually queried by month, meaning that for most queries, we don't care if it's 01/01/04 or 01/31/04 as long as it's in January of 2004.
I'd like to make several of these fields go away, if I can show quantitatively that doing so will improve performance. I have recordsets up to 9 million rows to "show my work" with.
Should I be concerned about varchar's in these ~120 tables? Would there be any benefits to converting them to char's, or key-ing them out of the data tables?
Here's a brief walkthrough of what the data generally looks like:
Every customer of ours has a ClientID. On every CD that we load, we pull at least one MTN(sometimes up to 5 or 10, but only occasionally). For each MTN, there are at least one BTN. The number of BTNs varies by the size of the Client, from 1 to 30 to 200 on the outside. Under each BTN lies between 1 and 100 WTNs usually. CarrierID and CDguid are control fields, the first uniquely identifies the company that provided the CD, the second uniquely Identifies the CD by client/carrier/mtn/invdte. We use these fields primarily for removing data when we're informed that there is an error on a CD.
Please point me in a good direction for improving this structure, make comments, or drop hints of specific things I can research to enlighten myself.
Thanks.
dBforums
>
refactoring tables for performance
09-13-04, 09:40
Linear Mode
