[jboss-jira] [JBoss JIRA] Commented: (JBPORTAL-2444) Add primary keys to JBP_PORTAL_MODE and JBP_PORTAL_WINDOW_STATE tables

Add primary keys to JBP_PORTAL_MODE and JBP_PORTAL_WINDOW_STATE tables ---------------------------------------------------------------------- Key: JBPORTAL-2444 URL: https://jira.jboss.org/jira/browse/JBPORTAL-2444 Project: JBoss Portal Issue Type: Task Security Level: Public(Everyone can see) Affects Versions: 2.7.2 Final Environment: Testing JBoss Portal with a large user base. The database (MS SQL Server) has 100.000 users with dashboards. And the following records in the "Top tables": 2.500.100 JBP_PORTAL_OBJECT_PROPS 500.031 JBP_OBJECT_NODE 300.018 JBP_WINDOW 500.015 JBP_PORTAL_MODE 100.002 jbp_users 500.031 JBP_PORTAL_OBJECT 300.009 JBP_PORTAL_WINDOW_STATE 200.035 JBP_PORTAL_OBJECT_DNAMES 100.002 jbp_role_membership 100.008 JBP_PAGE 100.003 JBP_PORTAL Reporter: Martin Putz Assignee: Chris Laprun We have found the most "heavy/cost" query during the login, which produces a continue 100% use of the CPU during 20 minutes in our database server, is the following one: select portalobje0_.PK as PK64_0_, portalobje0_.LISTENER as LISTENER64_0_, portalobje0_4_.INSTANCE_REF as INSTANCE2_72_0_, case when portalobje0_1_.PK is not null then 1 when portalobje0_2_.PK is not null then 2 when portalobje0_3_.PK is not null then 3 when portalobje0_4_.PK is not null then 4 when portalobje0_.PK is not null then 0 end as clazz_0_, declaredpr1_.OBJECT_KEY as OBJECT1_2_, declaredpr1_.jbp_VALUE as jbp2_2_, declaredpr1_.NAME as NAME2_, modes2_.PK as PK3_, modes2_.name as name3_, windowstat3_.PK as PK4_, windowstat3_.name as name4_ from JBP_PORTAL_OBJECT portalobje0_ left outer join JBP_CONTEXT portalobje0_1_ on portalobje0_.PK=portalobje0_1_.PK left outer join JBP_PORTAL portalobje0_2_ on portalobje0_.PK=portalobje0_2_.PK left outer join JBP_PAGE portalobje0_3_ on portalobje0_.PK=portalobje0_3_.PK left outer join JBP_WINDOW portalobje0_4_ on portalobje0_.PK=portalobje0_4_.PK left outer join JBP_PORTAL_OBJECT_PROPS declaredpr1_ on portalobje0_.PK=declaredpr1_.OBJECT_KEY left outer join JBP_PORTAL_MODE modes2_ on portalobje0_.PK=modes2_.PK left outer join JBP_PORTAL_WINDOW_STATE windowstat3_ on portalobje0_.PK=windowstat3_.PK where portalobje0_.PK= 8 8 is the general dashboard object Our DBA has analized the query and tables related and found in the Execution Plan two table scans. One in JBP_PORTAL_MODE (half million records) and the second one in JBP_PORTAL_WINDOW_STATE (300 K records). The problem with the table scan is that the DBMS is not using indexes. JBP_PORTAL_MODE has 5 record per user in the databases all with names 'help', 'admin', 'edit', 'edit_defaults', 'view' and your only index is a FK (Foreign key) but there is no Primary Key. JBP_PORTAL_WINDOW_STATE has 3 record per user in the databases all with names 'maximized', 'minimized', 'normal' and again your only index is a FK (Foreign key) but there is no Primary Key. Our DBA creates those two new Primary Keys ALTER TABLE [dbo].[JBP_PORTAL_MODE] ADD CONSTRAINT [PK_JBP_PORTAL_MODE] PRIMARY KEY CLUSTERED ( [PK] ASC, [name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ALTER TABLE [dbo].[JBP_PORTAL_WINDOW_STATE] ADD CONSTRAINT [PK_JBP_PORTAL_WINDOW_STATE] PRIMARY KEY CLUSTERED ( [PK] ASC, [name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] With these two new indexes the query uses the indexes and the performance is much better. Even more, the use of the CPU reach the 100% but only for less than a minute and in average is used 55% Could you please include this PK in your Portal DB creation?