Mailing-List: contact dev-help@jackrabbit.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@jackrabbit.apache.org
Date: Wed, 20 Mar 2013 21:31:15 +0000 (UTC)
From: "Todd Pagni (JIRA)" <jira@apache.org>
To: dev@jackrabbit.apache.org
Message-ID: <JIRA.12638092.1363814902991.22604.1363815075753@arcas>
In-Reply-To: <JIRA.12638092.1363814902991@arcas>
References: <JIRA.12638092.1363814902991@arcas>
Subject: [jira] [Updated] (JCR-3537) Large number of SQL queries when adding
 nodes with version history
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/JCR-3537?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Todd Pagni updated JCR-3537:
----------------------------

    Attachment: jackrabbit-core-2.5.0-version-history.patch

Patch for the version history issue
                
> Large number of SQL queries when adding nodes with version history
> ------------------------------------------------------------------
>
>                 Key: JCR-3537
>                 URL: https://issues.apache.org/jira/browse/JCR-3537
>             Project: Jackrabbit Content Repository
>          Issue Type: Bug
>          Components: versioning
>    Affects Versions: 2.5
>         Environment: Windows 2008, tomcat application server, SQL Server 2008 database server
>            Reporter: Todd Pagni
>              Labels: newbie, patch
>         Attachments: jackrabbit-core-2.5.0-version-history.patch
>
>
> We are adding a large number of documents to a jackrabbit 2.5 database repository.  We are using the bundle.MSSqlPersistenceManager and we are seeing a large number of SQL queries (300+) when adding a single folder, file, and file content. This appears to create a significant performance bottleneck when adding documents when the repository size is over 300k documents/nodes. The repository structure is a hierarchy with less than 1000k child nodes per parent.   The following is an example structure of the repo with the (New child folder) representing the new content being added. 
> -- Root node  
> -- Parent node  
>                 --New child folder (mix:versionable,mix:lockable)
>                                 --new file (mix:versionable,mix:lockable)
>                    --new document content
> --Existing Child Folder
> -- Parent node                                  
> The vast majority of the 200-300+ queries that execute when adding a node look like the following: 
> exec sp_execute 2,0x5740D9A36F2E4032BFF0BA652D89FFB8
> exec sp_execute 2,0xBBFE059BF7E44947A8B0858F3CE33DB8
> exec sp_execute 2,0xC2AD22DBE1DB43A083BCA1B2C94E07CC
> The majority of the queries that are executed appear to be related to versioning.  When a node is added the version history for the node stored/saved, the parent node is saved, which ultimately cascades and saves all children of the parent, so adding a child node saves the parent and all other children.  
> We have created a patch for jackrabbit-core 2.5.0 to prevent the cascade to store all other child nodes when saving/storing the version history of a new node.  This cuts the number of queries that are executed in half.  Does anyone see a problem with this technique?   All unit tests are still passing.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira