[seam-dev] Website performance - help offer

[Sebastian Hennebrueder]

Sebastian Hennebrueder schrieb:
> Hello,
>
> I would like to offer my help to improve the website performance. Do 
> you use the "standard" version of the wiki or should I checkout the 
> code from somewhere else?
>
> Best Regards / Viele Grüße
>
> Sebastian
> _______________________________________________
> seam-dev mailing list
> seam-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/seam-dev
>
Hello,

I had a first look at the wiki and resulting queries. Is it possible 
that you are using MySQL for your forum?

The queries for the forum results in subqueries -> ForumQuery class.

MySQL (currently) will resolve subqueries as nested queries. So if the 
result has 500 entries, we will have 500 calls to the subquery. The more 
data we have the slower the wiki will become. There is no real solution 
to this problem but replacing the database. I would recommend to use 
PostgreSQL instead.

I had this problem half a year ago when doing a performance review on MySQL.

If we are not using MySQL, than let me know.

Best Regards

Sebastian


Here an example for a query:

 select
        wikidocume0_.NODE_ID as col_0_0_,
        count(wikicommen1_.NODE_ID) as col_1_0_,
        wikicommen2_.NODE_ID as col_2_0_,
        user3_.USER_ID as USER1_110_3_,
        wikicommen2_.NODE_ID as NODE1_102_0_,
        user3_.USER_ID as USER1_110_1_,
        wikicommen2_1_.AREA_NR as AREA2_102_0_,
        wikicommen2_1_.CREATED_BY_USER_ID as CREATED11_102_0_,
        wikicommen2_1_.CREATED_ON as CREATED3_102_0_,
        wikicommen2_1_.LAST_MODIFIED_BY_USER_ID as LAST12_102_0_,
        wikicommen2_1_.LAST_MODIFIED_ON as LAST4_102_0_,
        wikicommen2_1_.NAME as NAME102_0_,
        wikicommen2_1_.PARENT_NODE_ID as PARENT13_102_0_,
        wikicommen2_1_.READ_ACCESS_LEVEL as READ6_102_0_,
        wikicommen2_1_.OBJ_VERSION as OBJ7_102_0_,
        wikicommen2_1_.WIKINAME as WIKINAME102_0_,
        wikicommen2_1_.WRITE_ACCESS_LEVEL as WRITE9_102_0_,
        wikicommen2_1_.WRITE_PROTECTED as WRITE10_102_0_,
        wikicommen2_.FROM_USER_EMAIL as FROM3_105_0_,
        wikicommen2_.FROM_USER_HOMEPAGE as FROM4_105_0_,
        wikicommen2_.FROM_USER_NAME as FROM5_105_0_,
        wikicommen2_.NS_LEFT as NS6_105_0_,
        wikicommen2_.NS_RIGHT as NS7_105_0_,
        wikicommen2_.NS_THREAD as NS8_105_0_,
        wikicommen2_.SUBJECT as SUBJECT105_0_,
        wikicommen2_.USE_WIKI_TEXT as USE10_105_0_,
        user3_.ACTIVATED as ACTIVATED110_1_,
        user3_.ACTIVATION_CODE as ACTIVATION3_110_1_,
        user3_.CREATED_ON as CREATED4_110_1_,
        user3_.EMAIL as EMAIL110_1_,
        user3_.FIRSTNAME as FIRSTNAME110_1_,
        user3_.LAST_LOGIN_ON as LAST7_110_1_,
        user3_.LASTNAME as LASTNAME110_1_,
        user3_.MEMBER_HOME_WIKI_DIRECTORY_ID as MEMBER12_110_1_,
        user3_.PASSWORDHASH as PASSWORD9_110_1_,
        user3_.USER_PROFILE_ID as USER13_110_1_,
        user3_.USERNAME as USERNAME110_1_,
        user3_.OBJ_VERSION as OBJ11_110_1_
    from
        WIKI_DOCUMENT wikidocume0_
    inner join
        WIKI_FILE wikidocume0_1_
            on wikidocume0_.NODE_ID=wikidocume0_1_.NODE_ID
    inner join
        WIKI_NODE wikidocume0_2_
            on wikidocume0_.NODE_ID=wikidocume0_2_.NODE_ID,
        WIKI_COMMENT wikicommen1_
    inner join
        WIKI_NODE wikicommen1_1_
            on wikicommen1_.NODE_ID=wikicommen1_1_.NODE_ID,
        WIKI_COMMENT wikicommen2_
    inner join
        WIKI_NODE wikicommen2_1_
            on wikicommen2_.NODE_ID=wikicommen2_1_.NODE_ID
    inner join
        USERS user3_
            on wikicommen2_1_.CREATED_BY_USER_ID=user3_.USER_ID
    where
        wikidocume0_2_.READ_ACCESS_LEVEL <= ?
        and wikicommen1_1_.READ_ACCESS_LEVEL <= ?
        and wikicommen2_1_.READ_ACCESS_LEVEL <= ?
        and (
            wikidocume0_.NODE_ID in (
                ? , ?
            )
        )
        and (
            wikicommen1_.NS_THREAD in (
                select
                    wikicommen4_.NS_THREAD
                from
                    WIKI_COMMENT wikicommen4_
                inner join
                    WIKI_NODE wikicommen4_1_
                        on wikicommen4_.NODE_ID=wikicommen4_1_.NODE_ID
                where
                    wikicommen4_1_.PARENT_NODE_ID=wikidocume0_.NODE_ID
            )
        )
        and wikicommen2_1_.CREATED_ON=(
            select
                max(wikicommen5_1_.CREATED_ON)
            from
                WIKI_COMMENT wikicommen5_
            inner join
                WIKI_NODE wikicommen5_1_
                    on wikicommen5_.NODE_ID=wikicommen5_1_.NODE_ID
            where
                wikicommen5_.NS_THREAD in (
                    select
                        wikicommen6_.NS_THREAD
                    from
                        WIKI_COMMENT wikicommen6_
                    inner join
                        WIKI_NODE wikicommen6_1_
                            on wikicommen6_.NODE_ID=wikicommen6_1_.NODE_ID
                    where
                        wikicommen6_1_.PARENT_NODE_ID=wikidocume0_.NODE_ID
                )
            )
        group by
            wikidocume0_.NODE_ID ,
            wikicommen2_.NODE_ID ,
            wikicommen2_.NS_LEFT ,
            wikicommen2_.NS_RIGHT ,
            wikicommen2_.NS_THREAD ,
            wikicommen2_1_.OBJ_VERSION ,
            wikicommen2_1_.PARENT_NODE_ID ,
            wikicommen2_1_.AREA_NR ,
            wikicommen2_1_.NAME ,
            wikicommen2_1_.WIKINAME ,
            wikicommen2_1_.CREATED_BY_USER_ID ,
            wikicommen2_1_.CREATED_ON ,
            wikicommen2_1_.LAST_MODIFIED_BY_USER_ID ,
            wikicommen2_1_.LAST_MODIFIED_ON ,
            wikicommen2_1_.READ_ACCESS_LEVEL ,
            wikicommen2_1_.WRITE_ACCESS_LEVEL ,
            wikicommen2_1_.WRITE_PROTECTED ,
            wikicommen2_.SUBJECT ,
            wikicommen2_.FROM_USER_NAME ,
            wikicommen2_.FROM_USER_EMAIL ,
            wikicommen2_.FROM_USER_HOMEPAGE ,
            wikicommen2_.USE_WIKI_TEXT ,
            user3_.USER_ID ,
            user3_.OBJ_VERSION ,
            user3_.FIRSTNAME ,
            user3_.LASTNAME ,
            user3_.USERNAME ,
            user3_.PASSWORDHASH ,
            user3_.EMAIL ,
            user3_.ACTIVATED ,
            user3_.ACTIVATION_CODE ,
            user3_.CREATED_ON ,
            user3_.LAST_LOGIN_ON ,
            user3_.MEMBER_HOME_WIKI_DIRECTORY_ID ,
            user3_.USER_PROFILE_ID