SQL Server Performance

General Config / Definition table

Discussion in 'T-SQL Performance Tuning for Developers' started by fatp, Oct 16, 2006.

  1. fatp New Member

    I am trying to build a series of table who will accept various Config / defintions.

    So far I was willing to go with the following structure.
    One relation table with one PK and multiple FK and a Active Column.

    I was planing to do some load on demand with my structure over collections of object build at run-time.


    quote:
    CREATE TABLE [dbo].[Tbl_Config_Relation] (
    [PK_ConfigRelation] [int] IDENTITY (1, 1) NOT NULL ,
    [FK_ConfigType] [int] NOT NULL ,
    [FK_ConfigTarget] [int] NOT NULL ,
    [FK_ConfigResult] [int] NOT NULL ,
    [Active] [bit] NOT NULL
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[Tbl_Config_Relation] WITH NOCHECK ADD
    CONSTRAINT [PK_Tbl_Config_Relation] PRIMARY KEY CLUSTERED
    (
    [PK_ConfigRelation]
    ) ON [PRIMARY]
    GO

    The other tables are build on the following logic. (Repalce PK with fk on relation table)

    quote:
    CREATE TABLE [dbo].[Tbl_Config_Target] (
    [PK_Configtarget] [int] IDENTITY (1, 1) NOT NULL ,
    [FK_ConfigTargetType] [int] NOT NULL ,
    [ConfigName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Active] [bit] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Tbl_Config_Target] WITH NOCHECK ADD
    CONSTRAINT [PK_Tbl_Config_Target] PRIMARY KEY CLUSTERED
    (
    [PK_Configtarget]
    ) ON [PRIMARY]
    GO

    My dba is telling me that the structure will get too heavy and there will be lose of performance over time.

    He would go on a different structure with multiple table depending on deferent ConfigType. He would spit all the layout definition, all the default config and other table like that. He think it will be more efficient and there will be less performance issues over time.

    Now my question, is there a guide on best structure that I should be using for that kind of table?

    Btw, it is for my own knowledge, I want to know what should be the best practice.

    Thanks.

    If I am missing something, I will post it asap!

Share This Page