db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tony Brusseau (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-6045) Can't bulk load terms by primary key efficiently on tables >256 rows
Date Fri, 18 Jan 2013 19:00:13 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6045?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13557475#comment-13557475
] 

Tony Brusseau edited comment on DERBY-6045 at 1/18/13 6:59 PM:
---------------------------------------------------------------

I did some more testing and it looks like if I create a table with 128 rows, then b-tree searches
are performed in every case. However, if I increase the table to 256 rows, then I start getting
heap searches when querying for multiple rows via primary key. Here is some code that will
generate the SQL for a table that demonstrates the problem:

public static void main(String[] args) {
    final Logger logger = Logger.getLogger(DerbyTester.class.toString());
    try {
      StringBuilder buf = new StringBuilder(1024*1024);
      buf.append("DROP TABLE VARIABLE_TERM;\n");
      buf.append("\n");
      buf.append("CREATE TABLE VARIABLE_TERM\n");
      buf.append("(\n");
      buf.append("        term_id                  BIGINT NOT NULL,\n");
      buf.append("        var_name              VARCHAR(1024) NOT NULL,\n");
      buf.append("        var_type                SMALLINT NOT NULL,     \n");
      buf.append("        kb_status              INTEGER NOT NULL        \n");
      buf.append(");\n");
      buf.append("\n");
      buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY
KEY (term_id);\n");
      buf.append("ALTER TABLE variable_term ADD CONSTRAINT variable_term_id_check CHECK ((TERM_ID
>= 1688849860263936) AND (TERM_ID <= 1970324836974591));\n");
      buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique
UNIQUE (var_name, var_type);\n");
      buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_var_type_enum_check
CHECK ((var_type >= 0) AND (var_type <= 4));\n");
      buf.append("\n");
      int count = 256; // @note count should be at least 40
      long idStart = 1688849860263936L;
      for (int i = 0; i < count; i++) {
        buf.append("INSERT INTO VARIABLE_TERM VALUES (").append(idStart++).append(", \'?var").append(i).append("\',
").append(((i %2) == 0) ? 1 : 4).append(", 1);\n");
      }
      buf.append("\n");
      buf.append("SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 1688849860263966;\n");
      buf.append("SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1688849860263937) OR (TERM_ID
=1688849860263975) OR (TERM_ID = 1688849860263960);\n");
      buf.append("SELECT * FROM VARIABLE_TERM WHERE (TERM_ID  IN (1688849860263937, 1688849860263975,
1688849860263960));\n");
       buf.append("\n");
       System.out.println(buf.toString());
       System.out.flush();
    } catch (Exception e) {
      logger.log(Level.SEVERE, e.getMessage(), e);
    } finally {
      System.exit(0);
    }
  }
                
      was (Author: apb):
    I did some more testing and it looks like if I create a table with 128 rows, then b-tree
searches are performed in every case. However, if I increase the table to 256 rows, then I
start getting heap searches when querying for multiple rows via primary key. Here is some
code that will generate a table that demonstrates the problem:

public static void main(String[] args) {
    final Logger logger = Logger.getLogger(DerbyTester.class.toString());
    try {
      StringBuilder buf = new StringBuilder(1024*1024);
      buf.append("DROP TABLE VARIABLE_TERM;\n");
      buf.append("\n");
      buf.append("CREATE TABLE VARIABLE_TERM\n");
      buf.append("(\n");
      buf.append("        term_id                  BIGINT NOT NULL,\n");
      buf.append("        var_name              VARCHAR(1024) NOT NULL,\n");
      buf.append("        var_type                SMALLINT NOT NULL,     \n");
      buf.append("        kb_status              INTEGER NOT NULL        \n");
      buf.append(");\n");
      buf.append("\n");
      buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY
KEY (term_id);\n");
      buf.append("ALTER TABLE variable_term ADD CONSTRAINT variable_term_id_check CHECK ((TERM_ID
>= 1688849860263936) AND (TERM_ID <= 1970324836974591));\n");
      buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique
UNIQUE (var_name, var_type);\n");
      buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_var_type_enum_check
CHECK ((var_type >= 0) AND (var_type <= 4));\n");
      buf.append("\n");
      int count = 256; // @note count should be at least 40
      long idStart = 1688849860263936L;
      for (int i = 0; i < count; i++) {
        buf.append("INSERT INTO VARIABLE_TERM VALUES (").append(idStart++).append(", \'?var").append(i).append("\',
").append(((i %2) == 0) ? 1 : 4).append(", 1);\n");
      }
      buf.append("\n");
      buf.append("SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 1688849860263966;\n");
      buf.append("SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1688849860263937) OR (TERM_ID
=1688849860263975) OR (TERM_ID = 1688849860263960);\n");
      buf.append("SELECT * FROM VARIABLE_TERM WHERE (TERM_ID  IN (1688849860263937, 1688849860263975,
1688849860263960));\n");
       buf.append("\n");
       System.out.println(buf.toString());
       System.out.flush();
    } catch (Exception e) {
      logger.log(Level.SEVERE, e.getMessage(), e);
    } finally {
      System.exit(0);
    }
  }
                  
> Can't bulk load terms by primary key efficiently on tables >256 rows
> --------------------------------------------------------------------
>
>                 Key: DERBY-6045
>                 URL: https://issues.apache.org/jira/browse/DERBY-6045
>             Project: Derby
>          Issue Type: Bug
>          Components: Store
>    Affects Versions: 10.9.1.0
>         Environment: Linux Debian 6.0.5
>            Reporter: Tony Brusseau
>            Priority: Critical
>
> I have a table with a long integer primary key field and 11 million rows. I seem to be
unable to load large chunks of rows via id in a reasonably efficient manner.
>   1. If I do individual lookups via the primary key, then a fast indexed lookup occurs.
However, if I do large numbers of such queries, then the time is overwhelmed by round-trip
overhead which makes everything incredibly slow.
>   2. If I use a single query with a disjunction of the primary keys of interest,  then
a table scan is performed (even if the clause only contains 1-3 items), which walks over 11
million rows...incredibly inefficient.
>   3. If I use an IN clause, then a table scan is performed (even if the clause only contains
1-3 items), which walks over 11 million rows...incredibly inefficient.
> I'm guessing that this might have something to do with the fact that I'm using large
integers and really big numbers that don't start anywhere at or about 1 for my keys. Could
this possibly be confusing the optimizer?
> Here are the unlimited query plans for the 3 cases that I enumerated:
> *********************************************************************************************
> [EL Fine]: 2013-01-17 11:09:53.384--ServerSession(582235416)--Connection(1430986883)--Thread(Thread["Initial
Lisp Listener",5,SubL Thread Group])--SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH,
FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID = ?)
> 	bind => [2251799814033500]
> Thu Jan 17 11:09:53 CST 2013 Thread["Initial Lisp Listener",5,SubL Thread Group] (XID
= 4711079), (SESSIONID = 3), SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH,
FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID = ?) ******* Project-Restrict
ResultSet (3):
> Number of opens = 1
> Rows seen = 1
> 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: 1.00
> 	optimizer estimated cost: 6.59
> Source result set:
> 	Index Row to Base Row ResultSet for FORMULA_TERM:
> 	Number of opens = 1
> 	Rows seen = 1
> 	Columns accessed from heap = {1, 2, 3, 4, 5, 6, 7, 8}
> 		constructor time (milliseconds) = 0
> 		open time (milliseconds) = 0
> 		next time (milliseconds) = 0
> 		close time (milliseconds) = 0
> 		optimizer estimated row count: 1.00
> 		optimizer estimated cost: 6.59
> 		Index Scan ResultSet for FORMULA_TERM using constraint KB_FORMULA_TERM_TERM_ID_PK at
read committed isolation level using share row locking chosen by the optimizer
> 		Number of opens = 1
> 		Rows seen = 1
> 		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=All
> 			Number of columns fetched=2
> 			Number of deleted rows visited=0
> 			Number of pages visited=3
> 			Number of rows qualified=1
> 			Number of rows visited=1
> 			Scan type=btree
> 			Tree height=-1
> 			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: 1.00
> 			optimizer estimated cost: 6.59
> [EL Fine]: 2013-01-17 11:01:00.732--ServerSession(1237006689)--Connection(927179828)--Thread(Thread["Initial
Lisp Listener",5,SubL Thread Group])--SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH,
FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (((TERM_ID = ?) OR (TERM_ID
= ?)) OR (TERM_ID = ?))
> 	bind => [2251799814033500, 2251799814033501, 2251799814033499]
> Thu Jan 17 11:01:10 CST 2013 Thread["Initial Lisp Listener",5,SubL Thread Group] (XID
= 4711078), (SESSIONID = 3), SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH,
FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (((TERM_ID = ?) OR (TERM_ID = ?)) OR (TERM_ID
= ?)) ******* Project-Restrict ResultSet (3):
> Number of opens = 1
> Rows seen = 3
> 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: 1176730.30
> 	optimizer estimated cost: 5931065.54
> Source result set:
> 	Project-Restrict ResultSet (2):
> 	Number of opens = 1
> 	Rows seen = 11767298
> 	Rows filtered = 11767295
> 	restriction = true
> 	projection = false
> 		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: 1176730.30
> 		optimizer estimated cost: 5931065.54
> 	Source result set:
> 		Table Scan ResultSet for FORMULA_TERM at read committed isolation level using instantaneous
share row locking chosen by the optimizer
> 		Number of opens = 1
> 		Rows seen = 11767298
> 		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=9
> 			Number of pages visited=34358
> 			Number of rows qualified=11767298
> 			Number of rows visited=11767298
> 			Scan type=heap
> 			start position:
> 				null
> 			stop position:
> 				null
> 			qualifiers:
> 				None
> 			optimizer estimated row count: 1176730.30
> 			optimizer estimated cost: 5931065.54
> [EL Fine]: 2013-01-17 11:27:00.627--ServerSession(1237006689)--Connection(1688096771)--Thread(Thread["Initial
Lisp Listener",5,SubL Thread Group])--SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH,
FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID IN (?,?,?))
> 	bind => [2251799814033500, 2251799814033501, 2251799814033499]
> Thu Jan 17 11:47:26 CST 2013 Thread["Initial Lisp Listener",5,SubL Thread Group] (XID
= 4711080), (SESSIONID = 3), SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH,
FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID IN (?,?,?)) ******* Project-Restrict
ResultSet (3):
> Number of opens = 1
> Rows seen = 3
> 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: 1176730.30
> 	optimizer estimated cost: 5931065.54
> Source result set:
> 	Project-Restrict ResultSet (2):
> 	Number of opens = 1
> 	Rows seen = 11767298
> 	Rows filtered = 11767295
> 	restriction = true
> 	projection = false
> 		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: 1176730.30
> 		optimizer estimated cost: 5931065.54
> 	Source result set:
> 		Table Scan ResultSet for FORMULA_TERM at read committed isolation level using instantaneous
share row locking chosen by the optimizer
> 		Number of opens = 1
> 		Rows seen = 11767298
> 		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=9
> 			Number of pages visited=34358
> 			Number of rows qualified=11767298
> 			Number of rows visited=11767298
> 			Scan type=heap
> 			start position:
> 				null
> 			stop position:
> 				null
> 			qualifiers:
> 				None
> 			optimizer estimated row count: 1176730.30
> 			optimizer estimated cost: 5931065.54

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

Mime
View raw message