Folks,

 

I am trying to port an application that has been using mysql to Derby, using the embedded driver.  I am having difficulty getting the performance of Derby to match (or at least get close to) mysql running on the same hardware.

 

I have a small demo program that demonstrates my problem on a pair of queries.  The program and the output from a sample run are below.  The first query is the simplest variant that I have found that exhibits a strong divergence between the derby and the mysql implementations.  The more complex query is a typical example from the application I am porting.

 

I have put a copy of the derby database at ftp://discover.nci.nih.gov/gominer10.zip.  I constructed the derby database instance using the database copy plug-in of the Squirrel SQL to copy the original mysql database.  As far as I can tell, it looks like the same indexes are present in both the mysql and derby versions of the database.

 

The tables I am querying have the following number of rows:

 

association:       5244258

dbxref:              1527522

evidence:           5579543

gene_product:    1386241

species:            53774

term:                 13321

 

I am using Derby 10.2.2.0. 

 

This query runs in a second or two when I target mysql using the same hardware.

 

The database is used in a read-only manner.  I can optimize entirely for query speed, I do not need to optimize for update speed, although keeping the size of the database instance to a manageable level is important.

 

Any suggestions about what would account for this performance difference and what I can do to close the gap would be appreciated.

 

Thanks!

 

Sincerely,

David Kane

 

=================== Demonstration Program ===================

 

import java.sql.Connection;

import java.sql.Driver;

import java.sql.DriverManager;

import java.sql.Statement;

import java.sql.ResultSet;

import java.util.Date;

 

public class DemoDerbyIssue {

    public static void main(String[] args) throws Exception {

        Driver driverImplementation = (Driver) Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();

        DriverManager.registerDriver(driverImplementation);

        Connection connection = DriverManager.getConnection("jdbc:derby:C:\\gominer10.2");

        Statement statement = connection.createStatement();

        statement.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");

        statement.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1)");

        String simplerQuery = " Select dbxref.xref_dbname, symbol, term.id, term.acc " +

                              " from gene_product, association, term, dbxref " +

                              " where symbol = 'ATM' and " +

                              " association.gene_product_id = gene_product.id and " +

                              " term.id = association.term_id and " +

                              " dbxref.id = gene_product.dbxref_id ";

        System.out.println("Before simplerQuery:" + new Date());

        ResultSet simplerQueryResults = statement.executeQuery(simplerQuery);

        System.out.println("After simplerQuery:" + new Date());

        System.out.println("===== simplerQuery Results =====");

        while (simplerQueryResults.next()) {

            System.out.println(new Date() + ":" + simplerQueryResults.getString(1) + "\t" +

                               simplerQueryResults.getString(2) + "\t" +

                               simplerQueryResults.getString(3) + "\t" +

                               simplerQueryResults.getString(4) + "\t");

        }

        System.out.println("===== simplerQuery Timing =====");

        ResultSet timingResults = statement.executeQuery("VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");

        if (timingResults.next()) {

            System.out.println(timingResults.getString(1));

        }

 

        String moreComplexQuery = " Select dbxref.xref_dbname, symbol, term.id, term.acc " +

                              " from gene_product, association, evidence, species, term, dbxref " +

                              " where symbol = 'ATM' and " +

                              " dbxref.xref_dbname IN ('UniProt','MGI') and " +

                              " species.ncbi_taxa_id IN (10090,10116) and " +

                              " term.is_obsolete = 0 and term.acc != 'GO:0000004' and term.acc != 'GO:0008372' and term.acc != 'GO:0005554' and " +

                              " evidence.code IN ('TAS','IDA') and " +

                              " species.id = gene_product.species_id and " +

                              " association.id=evidence.association_id and " +

                              " association.gene_product_id = gene_product.id and " +

                              " term.id = association.term_id and " +

                              " dbxref.id = gene_product.dbxref_id ";

        System.out.println("Before moreComplexQuery:" + new Date());

        ResultSet moreComplexQueryResults = statement.executeQuery(moreComplexQuery);

        System.out.println("After moreComplexQuery:" + new Date());

        System.out.println("===== moreComplexQueryResults Results =====");

        while (moreComplexQueryResults.next()) {

            System.out.println(new Date() + ":" + moreComplexQueryResults.getString(1) + "\t" +

                               moreComplexQueryResults.getString(2) + "\t" +

                               moreComplexQueryResults.getString(3) + "\t" +

                               moreComplexQueryResults.getString(4) + "\t");

        }

        timingResults = statement.executeQuery("VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");

        System.out.println("===== moreComplexQueryResults Timing =====");

        if (timingResults.next()) {

            System.out.println(timingResults.getString(1));

        }

    }

 

}

 

=================== Results from Running Demonstration Program ===================

 

Before simplerQuery:Mon Apr 09 15:22:56 EDT 2007

After simplerQuery:Mon Apr 09 15:23:35 EDT 2007

===== simplerQuery Results =====

Mon Apr 09 15:23:50 EDT 2007:MGI        ATM     5865     GO:0005634     

Mon Apr 09 15:23:52 EDT 2007:MGI        ATM     1876     GO:0016301     

Mon Apr 09 15:23:54 EDT 2007:MGI        ATM     3349     GO:0016740     

Mon Apr 09 15:23:56 EDT 2007:MGI        ATM     1931     GO:0004428     

Mon Apr 09 15:23:58 EDT 2007:MGI        ATM     119       GO:0003677     

Mon Apr 09 15:24:00 EDT 2007:MGI        ATM     1987     GO:0004674     

Mon Apr 09 15:24:02 EDT 2007:MGI        ATM     7829     GO:0008151     

Mon Apr 09 15:24:04 EDT 2007:MGI        ATM     7856     GO:0007049     

Mon Apr 09 15:24:05 EDT 2007:MGI        ATM     7508     GO:0006974     

Mon Apr 09 15:24:07 EDT 2007:MGI        ATM     10847   GO:0006281     

Mon Apr 09 15:24:09 EDT 2007:MGI        ATM     7511     GO:0008630     

Mon Apr 09 15:24:17 EDT 2007:RGD       ATM     1987     GO:0004674     

Mon Apr 09 15:24:19 EDT 2007:RGD       ATM     7508     GO:0006974     

Mon Apr 09 15:24:21 EDT 2007:RGD       ATM     7511     GO:0008630     

===== simplerQuery Timing =====

Statement Name:

            null

Statement Text:

             Select dbxref.xref_dbname, symbol, term.id, term.acc  from gene_product, association, term, dbxref  where symbol = 'ATM' and  association.gene_product_id = gene_product.id and  term.id = association.term_id and  dbxref.id = gene_product.dbxref_id

Parse Time: 30

Bind Time: 50

Optimize Time: 280

Generate Time: 10

Compile Time: 370

Execute Time: 84021

Begin Compilation Timestamp : 2007-04-09 15:22:56.713

End Compilation Timestamp : 2007-04-09 15:22:57.083

Begin Execution Timestamp : 2007-04-09 15:22:57.103

End Execution Timestamp : 2007-04-09 15:24:21.184

Statement Execution Plan Text:

Project-Restrict ResultSet (9):

Number of opens = 1

Rows seen = 14

Rows filtered = 0

restriction = false

projection = true

            constructor time (milliseconds) = 0

            open time (milliseconds) = 38295

            next time (milliseconds) = 45726

            close time (milliseconds) = 0

            restriction time (milliseconds) = 0

            projection time (milliseconds) = 0

            optimizer estimated row count:            0.00

            optimizer estimated cost:            6.43

 

Source result set:

            Nested Loop Join ResultSet:

            Number of opens = 1

            Rows seen from the left = 14

            Rows seen from the right = 14

            Rows filtered = 0

            Rows returned = 14

                        constructor time (milliseconds) = 0

                        open time (milliseconds) = 38295

                        next time (milliseconds) = 45726

                        close time (milliseconds) = 0

                        optimizer estimated row count:            0.00

                        optimizer estimated cost:            6.43

 

            Left result set:

                        Nested Loop Join ResultSet:

                        Number of opens = 1

                        Rows seen from the left = 14

                        Rows seen from the right = 14

                        Rows filtered = 0

                        Rows returned = 14

                                    constructor time (milliseconds) = 0

                                    open time (milliseconds) = 38185

                                    next time (milliseconds) = 6439

                                    close time (milliseconds) = 0

                                    optimizer estimated row count:            0.00

                                    optimizer estimated cost:            6.43

 

                        Left result set:

                                    Nested Loop Join ResultSet:

                                    Number of opens = 1

                                    Rows seen from the left = 2

                                    Rows seen from the right = 14

                                    Rows filtered = 0

                                    Rows returned = 14

                                                constructor time (milliseconds) = 0

                                                open time (milliseconds) = 20

                                                next time (milliseconds) = 44174

                                                close time (milliseconds) = 0

                                                optimizer estimated row count:            0.00

                                                optimizer estimated cost:            6.43

 

                                    Left result set:

                                                Index Row to Base Row ResultSet for GENE_PRODUCT:

                                                Number of opens = 1

                                                Rows seen = 2

                                                Columns accessed from heap = {0, 1, 2}

                                                            constructor time (milliseconds) = 0

                                                            open time (milliseconds) = 0

                                                            next time (milliseconds) = 91

                                                            close time (milliseconds) = 20

                                                            optimizer estimated row count:            0.00

                                                            optimizer estimated cost:            6.43

 

                                                            Index Scan ResultSet for GENE_PRODUCT using index SYMBOL at read committed isolation level using instantaneous share row locking chosen by the optimizer

                                                            Number of opens = 1

                                                            Rows seen = 2

                                                            Rows filtered = 0

                                                            Fetch Size = 16

                                                                        constructor time (milliseconds) = 0

                                                                        open time (milliseconds) = 10

                                                                        next time (milliseconds) = 0

                                                                        close time (milliseconds) = 20

                                                                        next time in milliseconds/row = 0

 

                                                            scan information:

                                                                        Bit set of columns fetched={0, 2}

                                                                        Number of columns fetched=2

                                                                        Number of deleted rows visited=0

                                                                        Number of pages visited=4

                                                                        Number of rows qualified=2

                                                                        Number of rows visited=3

                                                                        Scan type=btree

                                                                        Tree height=-1

                                                                        start position:

            >= on first 1 column(s).

            Ordered null semantics on the following columns:

0

                                                                        stop position:

            > on first 1 column(s).

            Ordered null semantics on the following columns:

0

                                                                        qualifiers:

None

                                                                        optimizer estimated row count:            0.00

                                                                        optimizer estimated cost:            6.43

 

 

                                    Right result set:

                                                Table Scan ResultSet for ASSOCIATION at read committed isolation level using instantaneous share row locking chosen by the optimizer

                                                Number of opens = 2

                                                Rows seen = 14

                                                Rows filtered = 0

                                                Fetch Size = 16

                                                            constructor time (milliseconds) = 0

                                                            open time (milliseconds) = 20

                                                            next time (milliseconds) = 44073

                                                            close time (milliseconds) = 30

                                                            next time in milliseconds/row = 3148

 

                                                scan information:

                                                            Bit set of columns fetched={1, 2}

                                                            Number of columns fetched=2

                                                            Number of pages visited=55790

                                                            Number of rows qualified=14

                                                            Number of rows visited=10488516

                                                            Scan type=heap

                                                            start position:

null                                                       stop position:

null                                                       qualifiers:

Column[0][0] Id: 2

Operator: =

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

 

                                                            optimizer estimated row count:            0.00

                                                            optimizer estimated cost:            0.00

 

 

                        Right result set:

                                    Table Scan ResultSet for TERM at read committed isolation level using instantaneous share row locking chosen by the optimizer

                                    Number of opens = 14

                                    Rows seen = 14

                                    Rows filtered = 0

                                    Fetch Size = 16

                                                constructor time (milliseconds) = 0

                                                open time (milliseconds) = 38185

                                                next time (milliseconds) = 430

                                                close time (milliseconds) = 0

                                                next time in milliseconds/row = 30

 

                                    scan information:

                                                Bit set of columns fetched={0, 3}

                                                Number of columns fetched=2

                                                Number of pages visited=400

                                                Number of rows qualified=14

                                                Number of rows visited=186494

                                                Scan type=heap

                                                start position:

null                                           stop position:

null                                           qualifiers:

Column[0][0] Id: 0

Operator: =

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

 

                                                optimizer estimated row count:            0.00

                                                optimizer estimated cost:            0.00

 

 

            Right result set:

                        Table Scan ResultSet for DBXREF at read committed isolation level using instantaneous share row locking chosen by the optimizer

                        Number of opens = 14

                        Rows seen = 14

                        Rows filtered = 0

                        Fetch Size = 16

                                    constructor time (milliseconds) = 0

                                    open time (milliseconds) = 38295

                                    next time (milliseconds) = 39397

                                    close time (milliseconds) = 0

                                    next time in milliseconds/row = 2814

 

                        scan information:

                                    Bit set of columns fetched={0, 3}

                                    Number of columns fetched=2

                                    Number of pages visited=19077

                                    Number of rows qualified=14

                                    Number of rows visited=21385308

                                    Scan type=heap

                                    start position:

null                               stop position:

null                               qualifiers:

Column[0][0] Id: 0

Operator: =

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

 

                                    optimizer estimated row count:            0.00

                                    optimizer estimated cost:            0.00

 

 

Before moreComplexQuery:Mon Apr 09 15:24:21 EDT 2007

After moreComplexQuery:Mon Apr 09 15:27:57 EDT 2007

===== moreComplexQueryResults Results =====

Mon Apr 09 15:27:59 EDT 2007:MGI        ATM     1987     GO:0004674     

Mon Apr 09 15:28:22 EDT 2007:MGI        ATM     7508     GO:0006974     

===== moreComplexQueryResults Timing =====

Statement Name:

            null

Statement Text:

             Select dbxref.xref_dbname, symbol, term.id, term.acc  from gene_product, association, evidence, species, term, dbxref  where symbol = 'ATM' and  dbxref.xref_dbname IN ('UniProt','MGI') and  species.ncbi_taxa_id IN (10090,10116) and  term.is_obsolete = 0 and term.acc != 'GO:0000004' and term.acc != 'GO:0008372' and term.acc != 'GO:0005554' and  evidence.code IN ('TAS','IDA') and  species.id = gene_product.species_id and  association.id=evidence.association_id and  association.gene_product_id = gene_product.id and  term.id = association.term_id and  dbxref.id = gene_product.dbxref_id

Parse Time: 10

Bind Time: 10

Optimize Time: 1262

Generate Time: 10

Compile Time: 1292

Execute Time: 280764

Begin Compilation Timestamp : 2007-04-09 15:24:21.274

End Compilation Timestamp : 2007-04-09 15:24:22.566

Begin Execution Timestamp : 2007-04-09 15:24:22.576

End Execution Timestamp : 2007-04-09 15:29:03.34

Statement Execution Plan Text:

Project-Restrict ResultSet (16):

Number of opens = 1

Rows seen = 2

Rows filtered = 0

restriction = false

projection = true

            constructor time (milliseconds) = 0

            open time (milliseconds) = 215270

            next time (milliseconds) = 65494

            close time (milliseconds) = 0

            restriction time (milliseconds) = 0

            projection time (milliseconds) = 0

            optimizer estimated row count:            0.00

            optimizer estimated cost:            6.43

 

Source result set:

            Nested Loop Join ResultSet:

            Number of opens = 1

            Rows seen from the left = 2

            Rows seen from the right = 2

            Rows filtered = 0

            Rows returned = 2

                        constructor time (milliseconds) = 0

                        open time (milliseconds) = 215270

                        next time (milliseconds) = 65494

                        close time (milliseconds) = 0

                        optimizer estimated row count:            0.00

                        optimizer estimated cost:            6.43

 

            Left result set:

                        Nested Loop Join ResultSet:

                        Number of opens = 1

                        Rows seen from the left = 2

                        Rows seen from the right = 2

                        Rows filtered = 0

                        Rows returned = 2

                                    constructor time (milliseconds) = 0

                                    open time (milliseconds) = 215169

                                    next time (milliseconds) = 61739

                                    close time (milliseconds) = 0

                                    optimizer estimated row count:            0.00

                                    optimizer estimated cost:            6.43

 

                        Left result set:

                                    Nested Loop Join ResultSet:

                                    Number of opens = 1

                                    Rows seen from the left = 2

                                    Rows seen from the right = 2

                                    Rows filtered = 0

                                    Rows returned = 2

                                                constructor time (milliseconds) = 0

                                                open time (milliseconds) = 214438

                                                next time (milliseconds) = 62329

                                                close time (milliseconds) = 0

                                                optimizer estimated row count:            0.00

                                                optimizer estimated cost:            6.43

 

                                    Left result set:

                                                Nested Loop Join ResultSet:

                                                Number of opens = 1

                                                Rows seen from the left = 14

                                                Rows seen from the right = 2

                                                Rows filtered = 0

                                                Rows returned = 2

                                                            constructor time (milliseconds) = 0

                                                            open time (milliseconds) = 6029

                                                            next time (milliseconds) = 269887

                                                            close time (milliseconds) = 0

                                                            optimizer estimated row count:            0.00

                                                            optimizer estimated cost:            6.43

 

                                                Left result set:

                                                            Nested Loop Join ResultSet:

                                                            Number of opens = 1

                                                            Rows seen from the left = 2

                                                            Rows seen from the right = 14

                                                            Rows filtered = 0

                                                            Rows returned = 14

                                                                        constructor time (milliseconds) = 0

                                                                        open time (milliseconds) = 0

                                                                        next time (milliseconds) = 12408

                                                                        close time (milliseconds) = 0

                                                                        optimizer estimated row count:            0.00

                                                                        optimizer estimated cost:            6.43

 

                                                            Left result set:

                                                                        Index Row to Base Row ResultSet for GENE_PRODUCT:

                                                                        Number of opens = 1

                                                                        Rows seen = 2

                                                                        Columns accessed from heap = {0, 1, 2, 3}

                                                                                    constructor time (milliseconds) = 0

                                                                                    open time (milliseconds) = 0

                                                                                    next time (milliseconds) = 0

                                                                                    close time (milliseconds) = 0

                                                                                    optimizer estimated row count:            0.00

                                                                                    optimizer estimated cost:            6.43

 

                                                                                    Index Scan ResultSet for GENE_PRODUCT using index SYMBOL at read committed isolation level using instantaneous share row locking chosen by the optimizer

                                                                                    Number of opens = 1

                                                                                    Rows seen = 2

                                                                                    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={0, 2}

                                                                                                Number of columns fetched=2

                                                                                                Number of deleted rows visited=0

                                                                                                Number of pages visited=4

                                                                                                Number of rows qualified=2

                                                                                                Number of rows visited=3

                                                                                                Scan type=btree

                                                                                                Tree height=-1

                                                                                                start position:

            >= on first 1 column(s).

            Ordered null semantics on the following columns:

0

                                                                                                stop position:

            > on first 1 column(s).

            Ordered null semantics on the following columns:

0

                                                                                                qualifiers:

None

                                                                                                optimizer estimated row count:            0.00

                                                                                                optimizer estimated cost:            6.43

 

 

                                                            Right result set:

                                                                        Table Scan ResultSet for ASSOCIATION at read committed isolation level using instantaneous share row locking chosen by the optimizer

                                                                        Number of opens = 2

                                                                        Rows seen = 14

                                                                        Rows filtered = 0

                                                                        Fetch Size = 16

                                                                                    constructor time (milliseconds) = 0

                                                                                    open time (milliseconds) = 0

                                                                                    next time (milliseconds) = 12408

                                                                                    close time (milliseconds) = 0

                                                                                    next time in milliseconds/row = 886

 

                                                                        scan information:

                                                                                    Bit set of columns fetched={0, 1, 2}

                                                                                    Number of columns fetched=3

                                                                                    Number of pages visited=55790

                                                                                    Number of rows qualified=14

                                                                                    Number of rows visited=10488516

                                                                                    Scan type=heap

                                                                                    start position:

null                                                                               stop position:

null                                                                               qualifiers:

Column[0][0] Id: 2

Operator: =

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

 

                                                                                    optimizer estimated row count:            0.00

                                                                                    optimizer estimated cost:            0.00

 

 

                                                Right result set:

                                                            Project-Restrict ResultSet (10):

                                                            Number of opens = 14

                                                            Rows seen = 14

                                                            Rows filtered = 12

                                                            restriction = true

                                                            projection = false

                                                                        constructor time (milliseconds) = 0

                                                                        open time (milliseconds) = 0

                                                                        next time (milliseconds) = 263508

                                                                        close time (milliseconds) = 0

                                                                        restriction time (milliseconds) = 0

                                                                        projection time (milliseconds) = 0

                                                                        optimizer estimated row count:            0.00

                                                                        optimizer estimated cost:            0.00

 

                                                            Source result set:

                                                                        Table Scan ResultSet for EVIDENCE at read committed isolation level using instantaneous share row locking chosen by the optimizer

                                                                        Number of opens = 14

                                                                        Rows seen = 14

                                                                        Rows filtered = 0

                                                                        Fetch Size = 16

                                                                                    constructor time (milliseconds) = 0

                                                                                    open time (milliseconds) = 6029

                                                                                    next time (milliseconds) = 263508

                                                                                    close time (milliseconds) = 0

                                                                                    next time in milliseconds/row = 18822

 

                                                                        scan information:

                                                                                    Bit set of columns fetched={1, 2}

                                                                                    Number of columns fetched=2

                                                                                    Number of pages visited=68345

                                                                                    Number of rows qualified=14

                                                                                    Number of rows visited=78113602

                                                                                    Scan type=heap

                                                                                    start position:

null                                                                               stop position:

null                                                                               qualifiers:

Column[0][0] Id: 2

Operator: =

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

Column[0][1] Id: 1

Operator: <=

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

Column[0][2] Id: 1

Operator: <

Ordered nulls: false

Unknown return value: true

Negate comparison result: true

 

                                                                                    optimizer estimated row count:            0.00

                                                                                    optimizer estimated cost:            0.00

 

 

                                    Right result set:

                                                Project-Restrict ResultSet (12):

                                                Number of opens = 2

                                                Rows seen = 2

                                                Rows filtered = 0

                                                restriction = true

                                                projection = false

                                                            constructor time (milliseconds) = 0

                                                            open time (milliseconds) = 0

                                                            next time (milliseconds) = 851

                                                            close time (milliseconds) = 0

                                                            restriction time (milliseconds) = 0

                                                            projection time (milliseconds) = 0

                                                            optimizer estimated row count:            0.00

                                                            optimizer estimated cost:            0.00

 

                                                Source result set:

                                                            Table Scan ResultSet for SPECIES at read committed isolation level using instantaneous share row locking chosen by the optimizer

                                                            Number of opens = 2

                                                            Rows seen = 2

                                                            Rows filtered = 0

                                                            Fetch Size = 16

                                                                        constructor time (milliseconds) = 0

                                                                        open time (milliseconds) = 214438

                                                                        next time (milliseconds) = 851

                                                                        close time (milliseconds) = 0

                                                                        next time in milliseconds/row = 425

 

                                                            scan information:

                                                                        Bit set of columns fetched={0, 1}

                                                                        Number of columns fetched=2

                                                                        Number of pages visited=62

                                                                        Number of rows qualified=2

                                                                        Number of rows visited=107548

                                                                        Scan type=heap

                                                                        start position:

null                                                                   stop position:

null                                                                   qualifiers:

Column[0][0] Id: 0

Operator: =

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

Column[0][1] Id: 1

Operator: <=

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

Column[0][2] Id: 1

Operator: <

Ordered nulls: false

Unknown return value: true

Negate comparison result: true

 

                                                                        optimizer estimated row count:            0.00

                                                                        optimizer estimated cost:            0.00

 

 

                        Right result set:

                                    Table Scan ResultSet for TERM at read committed isolation level using instantaneous share row locking chosen by the optimizer

                                    Number of opens = 2

                                    Rows seen = 2

                                    Rows filtered = 0

                                    Fetch Size = 16

                                                constructor time (milliseconds) = 0

                                                open time (milliseconds) = 215169

                                                next time (milliseconds) = 141

                                                close time (milliseconds) = 0

                                                next time in milliseconds/row = 70

 

                                    scan information:

                                                Bit set of columns fetched={0, 3, 4}

                                                Number of columns fetched=3

                                                Number of pages visited=400

                                                Number of rows qualified=2

                                                Number of rows visited=26642

                                                Scan type=heap

                                                start position:

null                                           stop position:

null                                           qualifiers:

Column[0][0] Id: 4

Operator: =

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

Column[0][1] Id: 0

Operator: =

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

Column[0][2] Id: 3

Operator: =

Ordered nulls: false

Unknown return value: true

Negate comparison result: true

Column[0][3] Id: 3

Operator: =

Ordered nulls: false

Unknown return value: true

Negate comparison result: true

Column[0][4] Id: 3

Operator: =

Ordered nulls: false

Unknown return value: true

Negate comparison result: true

 

                                                optimizer estimated row count:            0.00

                                                optimizer estimated cost:            0.00

 

 

            Right result set:

                        Project-Restrict ResultSet (15):

                        Number of opens = 2

                        Rows seen = 2

                        Rows filtered = 0

                        restriction = true

                        projection = false

                                    constructor time (milliseconds) = 0

                                    open time (milliseconds) = 0

                                    next time (milliseconds) = 3856

                                    close time (milliseconds) = 0

                                    restriction time (milliseconds) = 0

                                    projection time (milliseconds) = 0

                                    optimizer estimated row count:            0.00

                                    optimizer estimated cost:            0.00

 

                        Source result set:

                                    Table Scan ResultSet for DBXREF at read committed isolation level using instantaneous share row locking chosen by the optimizer

                                    Number of opens = 2

                                    Rows seen = 2

                                    Rows filtered = 0

                                    Fetch Size = 16

                                                constructor time (milliseconds) = 0

                                                open time (milliseconds) = 215270

                                                next time (milliseconds) = 3856

                                                close time (milliseconds) = 0

                                                next time in milliseconds/row = 1928

 

                                    scan information:

                                                Bit set of columns fetched={0, 3}

                                                Number of columns fetched=2

                                                Number of pages visited=19077

                                                Number of rows qualified=2

                                                Number of rows visited=3055044

                                                Scan type=heap

                                                start position:

null                                           stop position:

null                                           qualifiers:

Column[0][0] Id: 0

Operator: =

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

Column[0][1] Id: 3

Operator: <=

Ordered nulls: false

Unknown return value: false

Negate comparison result: false

Column[0][2] Id: 3

Operator: <

Ordered nulls: false

Unknown return value: true

Negate comparison result: true

 

                                                optimizer estimated row count:            0.00

                                                optimizer estimated cost:            0.00