[ 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 50000
Will run with 50000 rows.
* Testing with org.apache.derby.jdbc.EmbeddedDriver
Done inserting in 93.015 seconds
There are 50000 rows in users
There are 50000 rows in messaged_users
Done querying in 94.39 seconds
Retrieved 1 rows total.
There are 50000 rows in users
There are 99999 rows in messaged_users
Done querying in 965.197 seconds
Retrieved 50000 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 50000
Will run with 50000 rows.
* Testing with org.apache.derby.jdbc.EmbeddedDriver
Done inserting in 98.343 seconds
There are 50000 rows in users
There are 50000 rows in messaged_users
Done querying in 99.625 seconds
Retrieved 1 rows total.
There are 50000 rows in users
There are 99999 rows in messaged_users
Done querying in 955.01 seconds
Retrieved 50000 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.