[jira] [Commented] (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13464098#comment-13464098
]
Kathey Marsden commented on DERBY-3892:
---
I cannot reproduce this on trunk at least with the latest Main_Updated.java. Is
it possible the automatic index statistics update has corrected the issue?
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Labels: derby_triage10_5_2
> Attachments: derby_50kRows.log,
> derbyTwoDifferentOneHundredKPlans.log, Main_Bryan.java, Main.java,
> Main_Updated.java, Main_Updated.java, Main_Updated.java,
> query_plan_derby_3892.pdf, query_plan.jpg
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
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
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12640271#action_12640271
]
Georgi Hristov commented on DERBY-3892:
---
Can you give me a rough estimation on how long will it take to fix this issue ?
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Attachments: derby_50kRows.log,
> derbyTwoDifferentOneHundredKPlans.log, Main.java, Main_Bryan.java,
> Main_Updated.java, Main_Updated.java, Main_Updated.java
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12639735#action_12639735
]
Georgi Hristov commented on DERBY-3892:
---
Thanks a lot for your comments and I'm happy that we finally localized the
issue. Most probably we'll try to adopt the workarounds you've proposed and
will wait for the fix to become part of a new Derby release. Can we keep this
issue open until the fix is provided and included into a new Derby release ?
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Attachments: derby_50kRows.log,
> derbyTwoDifferentOneHundredKPlans.log, Main.java, Main_Bryan.java,
> Main_Updated.java, Main_Updated.java, Main_Updated.java
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12639692#action_12639692
]
Dag H. Wanvik commented on DERBY-3892:
--
As a workaround, there is also the system procedure
("call syscs_util.syscs_empty_statement_cache()") which may do the job.
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Attachments: derby_50kRows.log,
> derbyTwoDifferentOneHundredKPlans.log, Main.java, Main_Bryan.java,
> Main_Updated.java, Main_Updated.java, Main_Updated.java
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12639589#action_12639589
]
Georgi Hristov commented on DERBY-3892:
---
Please find attached the latest version of Main_Updated which executes the
query in about 5 secs. Please give it a try on your side. I have thoroughly
investigated the code you initially attached the Main.java and compared it with
my local implementation. Therefore I want also to assure you that all strange
statements like con.commit() etc. are double and triple checked. The reason why
I preserved them in the Main_Updated.java file is just to present you that the
same coding you've send me with some small add-ons produces such great
performance degradation.
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Attachments: derby_50kRows.log, Main.java, Main_Bryan.java,
> Main_Updated.java, Main_Updated.java
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12639578#action_12639578
]
Bryan Pendleton commented on DERBY-3892:
One thing I noticed while reading the code is that doQuery() calls
con.setAutoCommit(false), but insertData2() does not call con.commit().
So I thought maybe the problem involves having a large uncommitted transaction.
However, I tried adding a con.commit() to insertData2() , and I also tried
commenting out the con.setAutoCommit(false) from doQuery(), and
neither of those changes seemed to help the performance, either.
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Attachments: derby_50kRows.log, Main.java, Main_Bryan.java,
> Main_Updated.java
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12639576#action_12639576
]
Bryan Pendleton commented on DERBY-3892:
> remove the first call to doQuery() in the doSingleDriver() method,
> then the query lasts for not more than 6 seconds
Unfortunately, I can't reproduce this. Removing the first call to doQuery
doesn't seem to help things for me.
Can anybody else reproduce this finding?
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Attachments: derby_50kRows.log, Main.java, Main_Bryan.java,
> Main_Updated.java
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12639537#action_12639537
]
Georgi Hristov commented on DERBY-3892:
---
Thanks a lot for your detailed analysis I really appreciate it a lot. I can
give you one hint with which I manage to execute the query for the 100,000
rows: just remove the first call to doQuery() in the doSingleDriver() method,
then the query lasts for not more than 6 seconds. So from my point of view most
probably there is some faulty implementation, which maybe caches something, and
makes the subsequent calls to the same query, after the data in the target
tables has been modified, to take forever.
Anyway I'm a bit surprised that you accept a behaviour like 50,000 rows for 20
minutes ? Have you ever tried other implementations like Derby, for example
HSQLDB ? Do you have any performance benchmarks for Derby DB in embedded mode ?
It is really crucial for the application we want to deliver to have a high
performance DB which can handle a load of several hundred thousand rows. And
moreover we want this DB to be Derby and to add a new successor for Derby DB.
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Attachments: derby_50kRows.log, Main.java, Main_Bryan.java,
> Main_Updated.java
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12639433#action_12639433
]
Bryan Pendleton commented on DERBY-3892:
I tried setting -Xmx768m to see if it would make any difference,
and it didn't really make much difference:
C:\bryan\src\derby\tests>java -cp .;%CLASSPATH% -Dderby.language.logQueryPlan=tr
ue Main 5
Will run with 5 rows.
* Testing with org.apache.derby.jdbc.EmbeddedDriver
Done inserting in 93.015 seconds
There are 5 rows in users
There are 5 rows in messaged_users
Done querying in 94.39 seconds
Retrieved 1 rows total.
There are 5 rows in users
There are 9 rows in messaged_users
Done querying in 965.197 seconds
Retrieved 5 rows total.
C:\bryan\src\derby\tests>vim derby.log
C:\bryan\src\derby\tests>java -cp .;%CLASSPATH% -Xmx768m -Dderby.language.logQue
ryPlan=true Main 5
Will run with 5 rows.
* Testing with org.apache.derby.jdbc.EmbeddedDriver
Done inserting in 98.343 seconds
There are 5 rows in users
There are 5 rows in messaged_users
Done querying in 99.625 seconds
Retrieved 1 rows total.
There are 5 rows in users
There are 9 rows in messaged_users
Done querying in 955.01 seconds
Retrieved 5 rows total.
Given that it takes nearly 20 minutes for 50,000 rows, and that the performance
is definitely getting worse as the number of rows in the result grows, it's a
bit
hard to predict how long it would take to run 100,000 rows. It wouldn't surprise
me if it took 5 hours or more.
Unfortunately, I don't have a spare computer right now to let the 100,000 row
version run overnight. Perhaps the original poster could try letting the test
program run overnight, and see if it runs to completion in 8 or 10 hours?
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Attachments: derby_50kRows.log, Main.java, Main_Bryan.java,
> Main_Updated.java
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12639185#action_12639185
]
Georgi Hristov commented on DERBY-3892:
---
Please find attached an updated version of the Main.java (Main_Updated.java)
which hangs for real. It turns out that not the query hangs but the ResultSet
afterwards used to loop through the retrieved table records. It took me really
a lot of time to figure out how exactly the issue I have locally is getting
reproduced that's why I want to apologize for my delayed answer to this issue.
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Attachments: Main.java
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12637322#action_12637322
]
Bryan Pendleton commented on DERBY-3892:
Main.java ran to completion in about 2 minutes on my computer. For me,
too, most of the time was spent inserting the data.
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
> Attachments: Main.java
>
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12636513#action_12636513
]
Dag H. Wanvik commented on DERBY-3892:
--
I tried to reproduce this (see Main.java uploaded), with 10 rows in each
table,
but it ran in ca a minute on my box, most of the time inserting the data.
I had to modify the JOIN statement though, switching A and B in places to make
it compile.
Could your cardinality statistics be out of date (commented out call to
COMPRESS_TABLE in the
enclosed would update them)?
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12636445#action_12636445
]
Georgi Hristov commented on DERBY-3892:
---
Thanks for the quick response and the useful advices. I want to point out that
this issue report do not target performance regression as desribed in the
provided link but simply demonstrates that the described query just hangs, or
lets say that depending on the amount of records in the table, the time needed
for the query to finish rises exponentially. I thought that it will be
interesting for you to try it out locally as from my point of view it is very
easy to set up the environment locally. I've provided the table descriptions,
just insert about 100,000 records in each, and run the query. I hoped that by
reporting the issue directly to you, instead of trying to analyze the execution
plan or even debug derby's code, I'll finally reach to the solution/bug fix,
but I thought that you can do it faster than me and this is really important
for application delivery date. Please let me know if you will to try it on your
side or will leave me to struggle on my own.
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3892) Query execution hangs
[
https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12636241#action_12636241
]
Bryan Pendleton commented on DERBY-3892:
Here are some starting points for researching performance problems:
http://wiki.apache.org/db-derby/PerformanceDiagnosisTips
Knowing the query plan would be very interesting. To get that, you
may wish to start by trimming your data set down to a smaller number
of records, so that the query runs in a more timely fashion.
You might also wish to post a message to derby-users, rather than
filing a bug, as that might get you more suggestions for how to
improve performance.
> Query execution hangs
> -
>
> Key: DERBY-3892
> URL: https://issues.apache.org/jira/browse/DERBY-3892
> Project: Derby
> Issue Type: Bug
>Affects Versions: 10.4.1.3, 10.4.2.0
> Environment: OS: Windows Vista
> Application environment: Java 1.5 Swing Application using Derby DB in the
> embedded mode as part of the application.
>Reporter: Georgi Hristov
>
> Having the following db schema:
> CREATE TABLE Messaged_Users (ACCOUNT_ID VARCHAR(20) NOT NULL,
> USER_ID VARCHAR(20) NOT NULL,
> DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
> STATUS INT DEFAULT 0,
> CONSTRAINT PK_ACC_USER_ID PRIMARY KEY (ACCOUNT_ID, USER_ID))
> CREATE TABLE Users (USER_ID VARCHAR(20) NOT NULL CONSTRAINT USER_ID_PK
> PRIMARY KEY,
> USER_NAME VARCHAR(50) NOT NULL)
> with approximately 100,000 records in each table and trying to execute the
> following query:
> PreparedStatement searchStatement = conn.prepareStatement("SELECT
> b.USER_ID, b.USER_NAME FROM Users a JOIN Messaged_Users b ON
> a.USER_ID=b.USER_ID AND a.ACCOUNT_ID=(?)");
> searchStatement.setString(1, "5533232");
> ResultSet foundUsers = searchStatement.executeQuery();
> the executeQuery method actually never ends. I've waited for more than 30
> mins and it didn't finish. I've tried it really many times but without any
> progress the query just hangs. I presume that this is definitely a bug in the
> derby db which should be fixed. Please note also that this fix is very urgent
> for us so that we can deliver our application.
> Thanks in advance for looking into this issue.
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
