db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kane, David" <David_K...@sra.com>
Subject Performance Tuning Problem
Date Mon, 09 Apr 2007 19:41:42 GMT
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

 

 


Mime
View raw message