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

[Martin Putz (JIRA)]

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?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira