Bug 1029555 - MS Sql Server 2008 R2 SP2 performance issue and strange behavior
MS Sql Server 2008 R2 SP2 performance issue and strange behavior
Status: ASSIGNED
Product: JBoss Enterprise Portal Platform 6
Classification: JBoss
Component: Portal (Show other bugs)
6.1.0
Unspecified Unspecified
unspecified Severity unspecified
: ER01
: 6.1.1
Assigned To: mmurray
Tomas Kyjovsky
Ext
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2013-11-12 10:32 EST by Gary Hu
Modified: 2015-08-09 21:28 EDT (History)
5 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
custom extension (268.45 KB, application/zip)
2013-11-12 10:39 EST, Gary Hu
no flags Details

  None (edit)
Description Gary Hu 2013-11-12 10:32:39 EST
We've encountered the following issues using MS Sql server 2008 R2 SP2 with a custom extension with about 2000 pages. 

1) slowness
If setting MS Sql server collation as case insensitive(which is the default), for example Latin1_General_CI_AS, the custom extension can be loaded in about 30 mins. The same extension could be loaded in 2, 4 and 10 mins if using H2, Postgres and mysql respectively. 
If setting MS Sql server collation as case sensitive,for example Latin1_General_CS_AS, which is required by eXo JCR in http://docs.jboss.org/exojcr/1.15.1-GA/developer/en-US/html_single/ in section 1.18.1.4. MS SQL configuration, the JPP 6.0/6.1 can never be started up. It hangs there forever.

2) incompletely load data
If setting MS Sql server collation as case insensitive(which is the default), for example Latin1_General_CI_AS, the custom extension can be loaded in about 30 mins. However, the data is not completely loaded. 
The query "select count(*) from dbo.JCR_IPSYSTEM;" returns about 138413(this number varies each time) records in MSSQL server, and same query on postgress and mysql returns 201334 records.
As a result, after the JPP is fully started up when accessing the portal http://host:port/portal, it gives 404 error.

eXo has tested MS Sql server 2008 RTM version(no SP). They said if setting the collation as case sensitive they can load the extension without issue. 
********************************************************************************
We redo the test in our local environment:
Environment description:
jboss-jpp-6.0
JCR-1.15.1-CP01-redhat-1
MSSQL RTM Enterprise Edition (64-bit) 10.0.1600.22	
sqljdbc-3.0.1301.101.jar
JPP instance and MSSQL are located on different boxes.
We use a case sensitive collation Latin1_General_CS_AS for JCR schema
Startup time:
First startup 1140134ms(around 20 min)
Second startup 40300ms(around 0.6 min)
I can access to access http://localhost:8080/portal/classic
Portal is showing up.
select count from dbo.JCR_IPSYSTEM;" returned 200148
********************************************************************************

We have reproduced the issue using MS Sql server 2008 R2 SP2 instance in QE lab. We need to run another test on a MS Sql server 2008 RTM instance to confirm that there's no such issue in that sql server version.
Comment 1 Gary Hu 2013-11-12 10:39:47 EST
Created attachment 823025 [details]
custom extension
Comment 5 Boleslaw Dawidowicz 2013-12-17 03:58:56 EST
Jared, 

This turns out to be solvable with just configuration changes - so we think it should be resolved by just documenting in installation guide. I think Gary can help regarding all details.
Comment 6 Jared MORGAN 2013-12-17 17:42:15 EST
(In reply to Boleslaw Dawidowicz from comment #5)
> Jared, 
> 
> This turns out to be solvable with just configuration changes - so we think
> it should be resolved by just documenting in installation guide. I think
> Gary can help regarding all details.

OK, Bolek. Sounds good. Will await guidance from Gary on this.

Note You need to log in before you can comment on or make changes to this bug.