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 "Steffen Kuche (Created) (JIRA)" <ji...@apache.org> on 2011/12/23 13:06:30 UTC
[jira] [Created] (DERBY-5555) Execution Time with a Statement 1 ms
with a PreparedStatement 3 minutes
Execution Time with a Statement 1 ms with a PreparedStatement 3 minutes
-----------------------------------------------------------------------
Key: DERBY-5555
URL: https://issues.apache.org/jira/browse/DERBY-5555
Project: Derby
Issue Type: Bug
Reporter: Steffen Kuche
Sounds incrediable? Test it by yourself with the attached program.
The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
This program creates a derby database with a the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
After the database is built one time a Statement and one time a PreparedStatment is used to retrieve the next messages.
The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
Here are the temporary files created by derby during the select Statement:
{noFormat}
insgesamt 5,4G
287M 2011-12-23 09:48 T1324630116288.tmp
287M 2011-12-23 09:49 T1324630116289.tmp
287M 2011-12-23 09:49 T1324630116290.tmp
287M 2011-12-23 09:49 T1324630116291.tmp
287M 2011-12-23 09:49 T1324630116292.tmp
287M 2011-12-23 09:49 T1324630116293.tmp
287M 2011-12-23 09:50 T1324630116294.tmp
287M 2011-12-23 09:50 T1324630116295.tmp
287M 2011-12-23 09:50 T1324630116296.tmp
287M 2011-12-23 09:50 T1324630116297.tmp
287M 2011-12-23 09:51 T1324630116298.tmp
287M 2011-12-23 09:51 T1324630116299.tmp
287M 2011-12-23 09:51 T1324630116300.tmp
287M 2011-12-23 09:51 T1324630116301.tmp
287M 2011-12-23 09:51 T1324630116302.tmp
287M 2011-12-23 09:52 T1324630116303.tmp
287M 2011-12-23 09:52 T1324630116304.tmp
287M 2011-12-23 09:52 T1324630116305.tmp
284M 2011-12-23 09:52 T1324630116306.tmp
{noFormat}
--
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-5555) Execution Time with a Statement 1 ms
and with a PreparedStatement about 4 minutes
Posted by "Mamta A. Satoor (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-5555:
-----------------------------------
Urgency: Normal
Labels: derby_triage10_9 (was: )
> Execution Time with a Statement 1 ms and with a PreparedStatement about 4 minutes
> ---------------------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.8.1.2, 10.8.2.2
> Reporter: Steffen Kuche
> Labels: derby_triage10_9
> Attachments: derby.log, test.zip
>
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
> This program creates a derby database with the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
> After the database is built one time a Statement and another time a PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
--
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-5555) Execution Time with a Statement 1 ms
and with a PreparedStatement about 4 minutes
Posted by "Steffen Kuche (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steffen Kuche updated DERBY-5555:
---------------------------------
Affects Version/s: 10.8.1.2
10.8.2.2
> Execution Time with a Statement 1 ms and with a PreparedStatement about 4 minutes
> ---------------------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.8.1.2, 10.8.2.2
> Reporter: Steffen Kuche
> Attachments: test.zip
>
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
> This program creates a derby database with the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
> After the database is built one time a Statement and another time a PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
--
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-5555) Execution Time with a Statement 1 ms
and with a PreparedStatement about 4 minutes
Posted by "Knut Anders Hatlen (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5555:
--------------------------------------
Attachment: derby.log
I could reproduce this in my environment. Actually, the second execution of the PreparedStatement failed with an OutOfMemoryError, but the first execution took a very long time, as reported.
The query executed with a Statement is:
SELECT ID, MSG FROM ACTIVEMQ_MSGS WHERE CONTAINER='queue://queue' AND ID > 100 ORDER BY ID
The PreparedStatement executes this query:
SELECT ID, MSG FROM ACTIVEMQ_MSGS WHERE CONTAINER=? AND ID > ? ORDER BY ID
I ran the test case with logging of query plans (derby.language.logQueryPlan=true, see the attached derby.log for the detailed plans). The first query uses the primary key index on the ID column. The second query uses a non-unique index on the CONTAINER column to retrieve the matching rows, and then sorts those results.
The optimizer probably makes a guess that the predicate CONTAINER=? won't match that many rows, in which case using an index on CONTAINER and sorting would be a good strategy. This turns out to be a bad decision when the parameter is set to a value that matches every row in the table at execution time.
In the first query, the predicate CONTAINER='queue://queue' is known at prepare time to match a high number of rows, so the optimizer avoids making the same mistake there.
> Execution Time with a Statement 1 ms and with a PreparedStatement about 4 minutes
> ---------------------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.8.1.2, 10.8.2.2
> Reporter: Steffen Kuche
> Attachments: derby.log, test.zip
>
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
> This program creates a derby database with the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
> After the database is built one time a Statement and another time a PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
--
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-5555) Execution Time with a Statement 1 ms
and with a PreparedStatement about 4 minutes
Posted by "Knut Anders Hatlen (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5555:
--------------------------------------
Component/s: SQL
Issue & fix info: Repro attached
> Execution Time with a Statement 1 ms and with a PreparedStatement about 4 minutes
> ---------------------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.8.1.2, 10.8.2.2
> Reporter: Steffen Kuche
> Attachments: derby.log, test.zip
>
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
> This program creates a derby database with the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
> After the database is built one time a Statement and another time a PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
--
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] [Commented] (DERBY-5555) Execution Time with a Statement 1
ms and with a PreparedStatement about 4 minutes
Posted by "Knut Anders Hatlen (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13178753#comment-13178753 ]
Knut Anders Hatlen commented on DERBY-5555:
-------------------------------------------
This problem looks similar to DERBY-3753. Both issues report problems with queries that select a primary key column and a BLOB column and order the result on the primary key column.
> Execution Time with a Statement 1 ms and with a PreparedStatement about 4 minutes
> ---------------------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.8.1.2, 10.8.2.2
> Reporter: Steffen Kuche
> Attachments: derby.log, test.zip
>
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
> This program creates a derby database with the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
> After the database is built one time a Statement and another time a PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
--
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-5555) Execution Time with a Statement 1 ms
with a PreparedStatement 3 minutes
Posted by "Steffen Kuche (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steffen Kuche updated DERBY-5555:
---------------------------------
Description:
Sounds incrediable? Test it by yourself with the attached program.
The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
This program creates a derby database with a the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
After the database is built one time a Statement and one time a PreparedStatment is used to retrieve the next messages.
The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
was:
Sounds incrediable? Test it by yourself with the attached program.
The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
This program creates a derby database with a the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
After the database is built one time a Statement and one time a PreparedStatment is used to retrieve the next messages.
The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
Here are the temporary files created by derby during the select Statement:
{noFormat}
insgesamt 5,4G
287M 2011-12-23 09:48 T1324630116288.tmp
287M 2011-12-23 09:49 T1324630116289.tmp
287M 2011-12-23 09:49 T1324630116290.tmp
287M 2011-12-23 09:49 T1324630116291.tmp
287M 2011-12-23 09:49 T1324630116292.tmp
287M 2011-12-23 09:49 T1324630116293.tmp
287M 2011-12-23 09:50 T1324630116294.tmp
287M 2011-12-23 09:50 T1324630116295.tmp
287M 2011-12-23 09:50 T1324630116296.tmp
287M 2011-12-23 09:50 T1324630116297.tmp
287M 2011-12-23 09:51 T1324630116298.tmp
287M 2011-12-23 09:51 T1324630116299.tmp
287M 2011-12-23 09:51 T1324630116300.tmp
287M 2011-12-23 09:51 T1324630116301.tmp
287M 2011-12-23 09:51 T1324630116302.tmp
287M 2011-12-23 09:52 T1324630116303.tmp
287M 2011-12-23 09:52 T1324630116304.tmp
287M 2011-12-23 09:52 T1324630116305.tmp
284M 2011-12-23 09:52 T1324630116306.tmp
{noFormat}
> Execution Time with a Statement 1 ms with a PreparedStatement 3 minutes
> -----------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Reporter: Steffen Kuche
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
> This program creates a derby database with a the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
> After the database is built one time a Statement and one time a PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
--
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-5555) Execution Time with a Statement 1 ms
and with a PreparedStatement about 4 minutes
Posted by "Steffen Kuche (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steffen Kuche updated DERBY-5555:
---------------------------------
Description:
Sounds incrediable? Test it by yourself with the attached program.
The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
This program creates a derby database with the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
After the database is built one time a Statement and another time a PreparedStatment is used to retrieve the next messages.
The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
was:
Sounds incrediable? Test it by yourself with the attached program.
The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
This program creates a derby database with a the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
After the database is built one time a Statement and one time a PreparedStatment is used to retrieve the next messages.
The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
> Execution Time with a Statement 1 ms and with a PreparedStatement about 4 minutes
> ---------------------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Reporter: Steffen Kuche
> Attachments: test.zip
>
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
> This program creates a derby database with the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
> After the database is built one time a Statement and another time a PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
--
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-5555) Execution Time with a Statement 1 ms
and with a PreparedStatement about 4 minutes
Posted by "Steffen Kuche (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steffen Kuche updated DERBY-5555:
---------------------------------
Attachment: test.zip
> Execution Time with a Statement 1 ms and with a PreparedStatement about 4 minutes
> ---------------------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Reporter: Steffen Kuche
> Attachments: test.zip
>
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
> This program creates a derby database with a the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
> After the database is built one time a Statement and one time a PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
--
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-5555) Execution Time with a Statement 1 ms
and with a PreparedStatement about 4 minutes
Posted by "Steffen Kuche (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steffen Kuche updated DERBY-5555:
---------------------------------
Summary: Execution Time with a Statement 1 ms and with a PreparedStatement about 4 minutes (was: Execution Time with a Statement 1 ms with a PreparedStatement 3 minutes)
> Execution Time with a Statement 1 ms and with a PreparedStatement about 4 minutes
> ---------------------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Reporter: Steffen Kuche
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
> This program creates a derby database with a the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
> After the database is built one time a Statement and one time a PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
--
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-5555) Execution Time with a Statement 1 ms
with a PreparedStatement 3 minutes
Posted by "Steffen Kuche (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5555?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steffen Kuche updated DERBY-5555:
---------------------------------
Description:
Sounds incrediable? Test it by yourself with the attached program.
The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
This program creates a derby database with a the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
After the database is built one time a Statement and one time a PreparedStatment is used to retrieve the next messages.
The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
Here are the temporary files created by derby during the select Statement:
{noFormat}
insgesamt 5,4G
287M 2011-12-23 09:48 T1324630116288.tmp
287M 2011-12-23 09:49 T1324630116289.tmp
287M 2011-12-23 09:49 T1324630116290.tmp
287M 2011-12-23 09:49 T1324630116291.tmp
287M 2011-12-23 09:49 T1324630116292.tmp
287M 2011-12-23 09:49 T1324630116293.tmp
287M 2011-12-23 09:50 T1324630116294.tmp
287M 2011-12-23 09:50 T1324630116295.tmp
287M 2011-12-23 09:50 T1324630116296.tmp
287M 2011-12-23 09:50 T1324630116297.tmp
287M 2011-12-23 09:51 T1324630116298.tmp
287M 2011-12-23 09:51 T1324630116299.tmp
287M 2011-12-23 09:51 T1324630116300.tmp
287M 2011-12-23 09:51 T1324630116301.tmp
287M 2011-12-23 09:51 T1324630116302.tmp
287M 2011-12-23 09:52 T1324630116303.tmp
287M 2011-12-23 09:52 T1324630116304.tmp
287M 2011-12-23 09:52 T1324630116305.tmp
284M 2011-12-23 09:52 T1324630116306.tmp
{noFormat}
was:
Sounds incrediable? Test it by yourself with the attached program.
The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
This program creates a derby database with a the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
After the database is built one time a Statement and one time a PreparedStatment is used to retrieve the next messages.
The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
Here are the temporary files created by derby during the select Statement:
{noFormat}
insgesamt 5,4G
287M 2011-12-23 09:48 T1324630116288.tmp
287M 2011-12-23 09:49 T1324630116289.tmp
287M 2011-12-23 09:49 T1324630116290.tmp
287M 2011-12-23 09:49 T1324630116291.tmp
287M 2011-12-23 09:49 T1324630116292.tmp
287M 2011-12-23 09:49 T1324630116293.tmp
287M 2011-12-23 09:50 T1324630116294.tmp
287M 2011-12-23 09:50 T1324630116295.tmp
287M 2011-12-23 09:50 T1324630116296.tmp
287M 2011-12-23 09:50 T1324630116297.tmp
287M 2011-12-23 09:51 T1324630116298.tmp
287M 2011-12-23 09:51 T1324630116299.tmp
287M 2011-12-23 09:51 T1324630116300.tmp
287M 2011-12-23 09:51 T1324630116301.tmp
287M 2011-12-23 09:51 T1324630116302.tmp
287M 2011-12-23 09:52 T1324630116303.tmp
287M 2011-12-23 09:52 T1324630116304.tmp
287M 2011-12-23 09:52 T1324630116305.tmp
284M 2011-12-23 09:52 T1324630116306.tmp
{noFormat}
> Execution Time with a Statement 1 ms with a PreparedStatement 3 minutes
> -----------------------------------------------------------------------
>
> Key: DERBY-5555
> URL: https://issues.apache.org/jira/browse/DERBY-5555
> Project: Derby
> Issue Type: Bug
> Reporter: Steffen Kuche
>
> Sounds incrediable? Test it by yourself with the attached program.
> The issued reased up because of AMQ-3644, but it is actually a derby problem so I post the issue here as well.
> Derby writes multiple gigabyte of temporary files in its tmp directory during one select statement. Afterwards these temporary files are deleted and rebuild by the next Statement.
> You can find a Test-Program (import it as a Maven-Eclipse project) which reproduces this issue.
> This program creates a derby database with a the activemq Message table and fills it with data (2000 Messages, this can take several hours). I assumed 3MB for one message as our messages contain same binary data and has an average size of 3MB.
> After the database is built one time a Statement and one time a PreparedStatment is used to retrieve the next messages.
> The Statement takes about 1 ms and the PreparedStatement about 258805 ms. Also the second PreparedStatement takes as much time.
> Here are the temporary files created by derby during the select Statement:
> {noFormat}
> insgesamt 5,4G
> 287M 2011-12-23 09:48 T1324630116288.tmp
> 287M 2011-12-23 09:49 T1324630116289.tmp
> 287M 2011-12-23 09:49 T1324630116290.tmp
> 287M 2011-12-23 09:49 T1324630116291.tmp
> 287M 2011-12-23 09:49 T1324630116292.tmp
> 287M 2011-12-23 09:49 T1324630116293.tmp
> 287M 2011-12-23 09:50 T1324630116294.tmp
> 287M 2011-12-23 09:50 T1324630116295.tmp
> 287M 2011-12-23 09:50 T1324630116296.tmp
> 287M 2011-12-23 09:50 T1324630116297.tmp
> 287M 2011-12-23 09:51 T1324630116298.tmp
> 287M 2011-12-23 09:51 T1324630116299.tmp
> 287M 2011-12-23 09:51 T1324630116300.tmp
> 287M 2011-12-23 09:51 T1324630116301.tmp
> 287M 2011-12-23 09:51 T1324630116302.tmp
> 287M 2011-12-23 09:52 T1324630116303.tmp
> 287M 2011-12-23 09:52 T1324630116304.tmp
> 287M 2011-12-23 09:52 T1324630116305.tmp
> 284M 2011-12-23 09:52 T1324630116306.tmp
> {noFormat}
--
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