You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (Created) (JIRA)" <ji...@apache.org> on 2012/04/19 14:20:41 UTC
[jira] [Created] (DERBY-5695) Speed up population of tables in
OrderByAndSortAvoidance
Speed up population of tables in OrderByAndSortAvoidance
--------------------------------------------------------
Key: DERBY-5695
URL: https://issues.apache.org/jira/browse/DERBY-5695
Project: Derby
Issue Type: Improvement
Components: Test
Affects Versions: 10.9.0.0
Reporter: Knut Anders Hatlen
Assignee: Knut Anders Hatlen
Priority: Minor
OrderByAndSortAvoidance executes about 3000 INSERT and UPDATE statements to populate table1 and table2 in its decorateSQL() method. Since each of the statements has to be parsed and compiled, this could take quite some time on less powerful hardware.
There are essentially three kinds of statements, and they could be parametrized like this:
INSERT INTO table1 VALUES (?)
INSERT INTO table2 VALUES (?,?,?)
UPDATE table2 SET value='true' WHERE id=? AND name='has_address'
We should parametrize the statements, prepare them once and execute them multiple times in order to speed up the test.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Resolved] (DERBY-5695) Speed up population of tables in
OrderByAndSortAvoidance
Posted by "Knut Anders Hatlen (Resolved) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5695?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen resolved DERBY-5695.
---------------------------------------
Resolution: Fixed
Fix Version/s: 10.9.0.0
Committed revision 1328328.
> Speed up population of tables in OrderByAndSortAvoidance
> --------------------------------------------------------
>
> Key: DERBY-5695
> URL: https://issues.apache.org/jira/browse/DERBY-5695
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.9.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Priority: Minor
> Fix For: 10.9.0.0
>
> Attachments: d5695-1a.diff
>
>
> OrderByAndSortAvoidance executes about 3000 INSERT and UPDATE statements to populate table1 and table2 in its decorateSQL() method. Since each of the statements has to be parsed and compiled, this could take quite some time on less powerful hardware.
> There are essentially three kinds of statements, and they could be parametrized like this:
> INSERT INTO table1 VALUES (?)
> INSERT INTO table2 VALUES (?,?,?)
> UPDATE table2 SET value='true' WHERE id=? AND name='has_address'
> We should parametrize the statements, prepare them once and execute them multiple times in order to speed up the test.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5695) Speed up population of tables in
OrderByAndSortAvoidance
Posted by "Knut Anders Hatlen (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5695?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5695:
--------------------------------------
Attachment: d5695-1a.diff
Attaching a patch that makes OrderByAndSortAvoidance populate the two large tables (table1 and table2) using prepared statements that are reused.
The test runs about three times faster in my environment with this patch applied. On my workstation the time is reduced from 24 seconds to 8 seconds. On a somewhat less powerful machine where I sometimes run tests, the time is reduced from 5 minutes to 1.5 minutes.
As an extra bonus, the test is now almost 7000 lines shorter. :)
For good measure, I also removed the following unnecessary calls in decorateSQL():
getConnection().setAutoCommit(false);
...
getConnection().commit();
st.close();
That these calls are unnecessary can be seen from the javadoc for the super-method, CleanDatabaseTestSetup.decorateSQL():
* Once this method returns the statement will be closed,
* commit called and the connection closed. The connection
* returned by s.getConnection() is the default connection
* and is in auto-commit false mode.
> Speed up population of tables in OrderByAndSortAvoidance
> --------------------------------------------------------
>
> Key: DERBY-5695
> URL: https://issues.apache.org/jira/browse/DERBY-5695
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.9.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Priority: Minor
> Attachments: d5695-1a.diff
>
>
> OrderByAndSortAvoidance executes about 3000 INSERT and UPDATE statements to populate table1 and table2 in its decorateSQL() method. Since each of the statements has to be parsed and compiled, this could take quite some time on less powerful hardware.
> There are essentially three kinds of statements, and they could be parametrized like this:
> INSERT INTO table1 VALUES (?)
> INSERT INTO table2 VALUES (?,?,?)
> UPDATE table2 SET value='true' WHERE id=? AND name='has_address'
> We should parametrize the statements, prepare them once and execute them multiple times in order to speed up the test.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira