db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Øyvind Harboe <oyvind.har...@zylin.com>
Subject Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
Date Thu, 16 Apr 2015 06:03:37 GMT
Hi Mike,

here's the query plan. It's pretty lengthy, sorry.

After I fixed the worst problem the performance difference between
PostgreSQL and Derby is ca. 5x. In my table comparing PostgreSQL and Derby
before after, Derby is flat at 200ms or so for each query.

I *think* what's happening is that Derby has two large sets of records
TREATMENT and FIELD. Derby needs to scan the entire main table to check if
each main record TREATMENT is in the set of FIELD records.  Hence the
performance is roughly proportional to the # of main records and indeed
200ms seems to be like the 'standard' time to complete a query for all
these queries now.

There are 6311 TREATMENT records and 322 FIELD records.




> If you are able to post a query plan for that query I would be willing to
look
> at it to see if derby is doing a scan or if the optimizer is using the
expected
> "multi-probe" join strategy.

Thu Apr 16 07:53:10 CEST 2015 Thread[qtp1419014327-22,5,main] (XID =
11827028), (SESSIONID = 1), SELECT DISTINCT t0.TREAT_CORRECTIVEDESC,
t0.TREAT_NO, t0.TREAT_INCIDID, t0.TREAT_ENTEREDBYCONTACT, t0.TREAT_CRITID,
t0.treat_treatcontact, t0.TREAT_ENTEREDBY, t0.TREAT_ID, t0.TREAT_REFINT,
t0.TREAT_REPORTEDDATE, t0.TREAT_UPDATEDDATE, t0.TREAT_DESC,
t0.TREAT_SOURCETYPE, t0.TREAT_QUALITYTYPE, t0.causedSysUser,
t0.areaResponsible, t0.TREAT_PROID, t0.TREAT_TREATASPECID,
t0.TREAT_TREATROLE, t0.TREAT_TREATUSER, t0.TREAT_PARENTID,
t0.TREAT_SYSORGUNITID, t0.sev_id, t0.TREAT_LIMITDATE,
t0.TREAT_RESPONSIBLECONTACT, t0.TREAT_ACTIONDESC, t0.TREAT_ELCSTATUSID,
t0.TREAT_REPORTEDBYCONPER, t0.responsibleSysUser, t0.freq_id,
t0.TREAT_ENTEREDDATE, t0.TREAT_REPORTEDBYCONTACT, t0.TREAT_TREATCSPECID,
t0.treat_causedcontact FROM TREATMENT t0 JOIN ELC_STATUS t1 ON
(t0.TREAT_ELCSTATUSID = t1.ELCSTATUS_ID) JOIN fieldlink t2 ON (t0.TREAT_ID
= t2.treatment) JOIN field t3 ON (t2.field = t3.id) JOIN fieldchildren t4
ON (t3.id = t4.child) WHERE (t0.TREAT_REPORTEDDATE IS NOT NULL) AND
(t0.TREAT_NO IS NOT NULL) AND (t1.ELCSTATUS_PROGVALUE <> ?) AND
(t0.TREAT_REPORTEDDATE IS NOT NULL) AND (t0.TREAT_NO IS NOT NULL) AND
(t2.customfield = ?) AND (t4.parent = ?) ******* Sort ResultSet:
Number of opens = 1
Rows input = 1292
Rows returned = 1292
Eliminate duplicates = true
In sorted order = false
Sort information:
    Number of merge runs=1
    Number of rows input=1292
    Number of rows output=1292
    Size of merge runs=[1279]
    Sort type=external
    constructor time (milliseconds) = 0
    open time (milliseconds) = 0
    next time (milliseconds) = 0
    close time (milliseconds) = 0
    optimizer estimated row count: 11.59
    optimizer estimated cost: 1006.81
Source result set:
    Project-Restrict ResultSet (15):
    Number of opens = 1
    Rows seen = 1292
    Rows filtered = 0
    restriction = false
    projection = true
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
        restriction time (milliseconds) = 0
        projection time (milliseconds) = 0
        optimizer estimated row count: 11.59
        optimizer estimated cost: 1006.81
    Source result set:
        Nested Loop Exists Join ResultSet:
        Number of opens = 1
        Rows seen from the left = 4995
        Rows seen from the right = 1292
        Rows filtered = 0
        Rows returned = 1292
            constructor time (milliseconds) = 0
            open time (milliseconds) = 0
            next time (milliseconds) = 0
            close time (milliseconds) = 0
            optimizer estimated row count: 11.59
            optimizer estimated cost: 1006.81
        Left result set:
            Nested Loop Exists Join ResultSet:
            Number of opens = 1
            Rows seen from the left = 4995
            Rows seen from the right = 4995
            Rows filtered = 0
            Rows returned = 4995
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                optimizer estimated row count: 11.59
                optimizer estimated cost: 952.18
            Left result set:
                Nested Loop Join ResultSet:
                Number of opens = 1
                Rows seen from the left = 4995
                Rows seen from the right = 4995
                Rows filtered = 0
                Rows returned = 4995
                    constructor time (milliseconds) = 0
                    open time (milliseconds) = 0
                    next time (milliseconds) = 0
                    close time (milliseconds) = 0
                    optimizer estimated row count: 11.59
                    optimizer estimated cost: 915.75
                Left result set:
                    Nested Loop Join ResultSet:
                    Number of opens = 1
                    Rows seen from the left = 6
                    Rows seen from the right = 4995
                    Rows filtered = 0
                    Rows returned = 4995
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        optimizer estimated row count: 24.06
                        optimizer estimated cost: 397.91
                    Left result set:
                        Table Scan ResultSet for ELC_STATUS at read
committed isolation level using instantaneous share row locking chosen by
the optimizer
                        Number of opens = 1
                        Rows seen = 6
                        Rows filtered = 0
                        Fetch Size = 16
                            constructor time (milliseconds) = 0
                            open time (milliseconds) = 0
                            next time (milliseconds) = 0
                            close time (milliseconds) = 0
                            next time in milliseconds/row = 0

                        scan information:
                            Bit set of columns fetched={3, 4}
                            Number of columns fetched=2
                            Number of pages visited=1
                            Number of rows qualified=6
                            Number of rows visited=7
                            Scan type=heap
                            start position:
                                null
                            stop position:
                                null
                            qualifiers:
                                Column[0][0] Id: 4
                                Operator: =
                                Ordered nulls: false
                                Unknown return value: true
                                Negate comparison result: true
                            optimizer estimated row count: 3.96
                            optimizer estimated cost: 30.90
                    Right result set:
                        Project-Restrict ResultSet (9):
                        Number of opens = 6
                        Rows seen = 4999
                        Rows filtered = 4
                        restriction = true
                        projection = true
                            constructor time (milliseconds) = 0
                            open time (milliseconds) = 0
                            next time (milliseconds) = 0
                            close time (milliseconds) = 0
                            restriction time (milliseconds) = 0
                            projection time (milliseconds) = 0
                            optimizer estimated row count: 24.06
                            optimizer estimated cost: 367.01
                        Source result set:
                            Index Row to Base Row ResultSet for TREATMENT:
                            Number of opens = 6
                            Rows seen = 4999
                            Columns accessed from heap = {0, 1, 2, 3, 4, 5,
6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25,
26, 27, 28, 29, 30, 31, 32, 33}
                                constructor time (milliseconds) = 0
                                open time (milliseconds) = 0
                                next time (milliseconds) = 0
                                close time (milliseconds) = 0
                                optimizer estimated row count: 24.06
                                optimizer estimated cost: 367.01
                                Index Scan ResultSet for TREATMENT using
constraint SQL120518171026210 at read committed isolation level using
instantaneous share row locking chosen by the optimizer
                                Number of opens = 6
                                Rows seen = 4999
                                Rows filtered = 0
                                Fetch Size = 16
                                    constructor time (milliseconds) = 0
                                    open time (milliseconds) = 0
                                    next time (milliseconds) = 0
                                    close time (milliseconds) = 0
                                    next time in milliseconds/row = 0

                                scan information:
                                    Bit set of columns fetched=All
                                    Number of columns fetched=2
                                    Number of deleted rows visited=179
                                    Number of pages visited=102
                                    Number of rows qualified=4999
                                    Number of rows visited=5184
                                    Scan type=btree
                                    Tree height=3
                                    start position:
                                        >= on first 1 column(s).
                                        Ordered null semantics on the
following columns:
                                    stop position:
                                        > on first 1 column(s).
                                        Ordered null semantics on the
following columns:
                                    qualifiers:
                                        None
                                    optimizer estimated row count: 24.06
                                    optimizer estimated cost: 367.01


                Right result set:
                    Project-Restrict ResultSet (12):
                    Number of opens = 4995
                    Rows seen = 30629
                    Rows filtered = 25634
                    restriction = true
                    projection = true
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        restriction time (milliseconds) = 0
                        projection time (milliseconds) = 0
                        optimizer estimated row count: 11.59
                        optimizer estimated cost: 517.84
                    Source result set:
                        Index Row to Base Row ResultSet for FIELDLINK:
                        Number of opens = 4995
                        Rows seen = 30629
                        Columns accessed from heap = {1, 3, 10}
                            constructor time (milliseconds) = 0
                            open time (milliseconds) = 0
                            next time (milliseconds) = 0
                            close time (milliseconds) = 0
                            optimizer estimated row count: 11.59
                            optimizer estimated cost: 517.84
                            Index Scan ResultSet for FIELDLINK using
constraint SQL120518171122050 at read committed isolation level using
instantaneous share row locking chosen by the optimizer
                            Number of opens = 4995
                            Rows seen = 30629
                            Rows filtered = 0
                            Fetch Size = 16
                                constructor time (milliseconds) = 0
                                open time (milliseconds) = 0
                                next time (milliseconds) = 0
                                close time (milliseconds) = 0
                                next time in milliseconds/row = 0

                            scan information:
                                Bit set of columns fetched=All
                                Number of columns fetched=2
                                Number of deleted rows visited=5029
                                Number of pages visited=15904
                                Number of rows qualified=30629
                                Number of rows visited=40653
                                Scan type=btree
                                Tree height=3
                                start position:
                                    >= on first 1 column(s).
                                    Ordered null semantics on the following
columns:
                                stop position:
                                    > on first 1 column(s).
                                    Ordered null semantics on the following
columns:
                                qualifiers:
                                    None
                                optimizer estimated row count: 11.59
                                optimizer estimated cost: 517.84


            Right result set:
                Index Scan ResultSet for FIELD using constraint
SQL100212164533470 at read committed isolation level using share row
locking chosen by the optimizer
                Number of opens = 4995
                Rows seen = 4995
                Rows filtered = 0
                Fetch Size = 1
                    constructor time (milliseconds) = 0
                    open time (milliseconds) = 0
                    next time (milliseconds) = 0
                    close time (milliseconds) = 0
                    next time in milliseconds/row = 0

                scan information:
                    Bit set of columns fetched={0}
                    Number of columns fetched=1
                    Number of deleted rows visited=0
                    Number of pages visited=9990
                    Number of rows qualified=4995
                    Number of rows visited=4995
                    Scan type=btree
                    Tree height=2
                    start position:
                        >= on first 1 column(s).
                        Ordered null semantics on the following columns:
                    stop position:
                        > on first 1 column(s).
                        Ordered null semantics on the following columns:
                    qualifiers:
                        None
                    optimizer estimated row count: 11.59
                    optimizer estimated cost: 36.43

        Right result set:
            Index Scan ResultSet for FIELDCHILDREN using constraint
SQL150416002622780 at read committed isolation level using share row
locking chosen by the optimizer
            Number of opens = 4995
            Rows seen = 1292
            Rows filtered = 0
            Fetch Size = 1
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                next time in milliseconds/row = 0

            scan information:
                Bit set of columns fetched={0, 1}
                Number of columns fetched=2
                Number of deleted rows visited=0
                Number of pages visited=15082
                Number of rows qualified=1292
                Number of rows visited=4995
                Scan type=btree
                Tree height=3
                start position:
                    >= on first 2 column(s).
                    Ordered null semantics on the following columns:
                    0
                stop position:
                    > on first 2 column(s).
                    Ordered null semantics on the following columns:
                    0
                qualifiers:
                    Column[0][0] Id: 0
                    Operator: =
                    Ordered nulls: false
                    Unknown return value: false
                    Negate comparison result: false
                optimizer estimated row count: 11.59
                optimizer estimated cost: 54.64

-- 
Øyvind Harboe - Can Zylin Consulting help on your project?
http://www.zylin.com/

Mime
View raw message