Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    North Carolina
    Posts
    6

    refactoring tables for performance

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by AndrewKT
    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?
    The answer to that question (at least) depends on your DBMS. If it is Oracle then the answer is a resounding "no" - CHAR will just make your tables bigger, which can't help performance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •