SQL Server Performance

Urgent Help on Table Design

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by mrunal56, Jan 27, 2006.

  1. mrunal56 New Member

    Experts,



    I have a query for all of you. I am using Dotnetnuke(Open Source CMS software) to create a CMS Web Applications. In my Application I am using a process which creates a child portal using dotnetnuke#%92s existing process. In this process the “Tabs” table is affected the most, where tabs are selected,created,updated each time I create a child portal. Now the problem is that my process works fine till around 50,000 records in this table, but the process starts giving an error ("SQL Timeout Expired") above 50,000 records. Now can anyone tell me is this table properly designed to handle records in lakhs with the help of table structure given below? Is there something that can be done ?



    I would highly appreciate replies from any of you experts out there.





    Thanks & Regards,



    Mrunal Nagrecha





    -------------------------------------------------------------------------------------------------------------Table Structure starts here------------------------------------------------------------------------------------------------







    Name Owner Type Created_datetime

    -------- -------- ------- --------------------------

    Tabs dbo user table 2006-01-24 23:13:24.007





    Column_Name Type comp Length Prec Scale Nullable Trim FixLen Collation

    --------------------- ------- -------- --------- ------- ------- ----------- Trailing Null ------------

    ---------- -------

    TabID int no 4 10 0 no (n/a) (n/a) NULL

    TabOrder int no 4 10 0 no (n/a) (n/a) NULL

    PortalID I nt no 4 10 0 yes (n/a) (n/a) NULL

    TabName nvarchar no 100 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

    IsVisible bit no 1 no (n/a) (n/a) NULL

    ParentId int no 4 10 0 yes (n/a) (n/a) NULL

    Level int no 4 10 0 no (n/a) (n/a) NULL

    IconFile nvarchar no 200 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

    DisableLink bit no 1 no (n/a) (n/a) NULL

    Title nvarchar no 400 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

    Description nvarchar no 1000 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

    KeyWords nvarchar no 1000 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

    IsDeleted bit no 1 no (n/a) (n/a) NULL

    Url nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

    SkinSrc nvarchar no 400 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

    ContainerSrc nvarchar no 400 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

    TabPath nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

    StartDate datetime no 8 yes (n/a) (n/a) NULL

    EndDate datetime no 8 yes (n/a) (n/a) NULL

    RefreshInterval int no 4 10 0 yes (n/a) (n/a) NULL

    PageHeadText nvarchar no 1000 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS





    Identity Seed Increment Not For Replication

    --------- ------ ------------- --------------------------

    TabID 0 1 0







    RowGuidCol

    ------------------

    No rowguidcol column defined.



    Data_Located_On_FileGroup

    ------------------------------------------

    PRIMARY







    Index_name index_description index_keys

    ----------------- ------------------------- -----------------

    IX_Tabs_1 nonclustered located on PRIMARY PortalID

    IX_Tabs_2 nonclustered located on PRIMARY ParentId

    PK_Tabs nonclustered, unique, primary key located on PRIMARY TabID









    Constraint_type constraint_name delete_ update_ status_ status_ Constraint_

    ---------------------- ------------------------ action action enabled for_rep Keys

    ------- ------- ---------- ----------- ---------------

    DEFAULT on column DisableLink DF_Tabs_DisableLink (n/a) (n/a) (n/a) (n/a) (0)

    DEFAULT on column IsDeleted DF_Tabs_IsDeleted (n/a) (n/a) (n/a) (n/a) (0)

    DEFAULT on column IsVisible DF_Tabs_IsVisible (n/a) (n/a) (n/a) (n/a) (1)

    DEFAULT on column Level DF_Tabs_Level (n/a) (n/a) (n/a) (n/a) (0)

    DEFAULT on column TabOrder DF_Tabs_TabOrder (n/a) (n/a) (n/a) (n/a) (0)

    FOREIGN KEY FK_Tabs_Portals Cascade No Action Enabled Not_For_Replication PortalID

    REFERENCES DNN3.2.dbo.Portals (PortalID)

    FOREIGN KEY FK_Tabs_Tabs No Action No Action Enabled Not_For_Replication ParentId

    REFERENCES DNN3.2.dbo.Tabs (TabID)

    PRIMARY KEY (non-clustered) PK_Tabs (n/a) (n/a) (n/a) (n/a) TabID







    Table is Referenced by Foreign key

    ---------------------------------------------------

    DNN3.2.dbo.TabModules: FK_TabModules_Tabs

    DNN3.2.dbo.TabPermission: FK_TabPermission_Tabs

    DNN3.2.dbo.Tabs: FK_Tabs_Tabs







    -------------------------------------------------------------------------------------------------------------Table Structure ends here------------------------------------------------------------------------------------------------




    Mrunal Nagrecha
  2. Madhivanan Moderator

  3. FrankKalis Moderator

Share This Page