SQL Server Performance Forum – Threads Archive

Urgent Help on Table Design

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

Duplicate post
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12600 Madhivanan Failing to plan is Planning to fail

Please avoid posting your question to more than one forum here! —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

]]>