db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3892) Query execution hangs
Date Tue, 14 Oct 2008 13:44:44 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3892?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Bryan Pendleton updated DERBY-3892:
-----------------------------------

    Attachment: Main_Bryan.java

I modified the test program some more:
 - added some more output so I could understand what it was doing
 - made the number of rows configurable.

Then I ran the program about half a dozen times, gradually increasing
the data size, to see what it was doing.

Certainly the times are not growing linearly, but they are not growing
all that badly either. I can run the program with 40,000 rows in about 10 minutes.

I also took a look at the query plan, and it seems that the query is
using a reasonable query plan:
 - scan Messaged_Users
 - for each Messaged_user which matched the account ID
 - query Users for that user id

Lastly, I looked at the program, and it seems that half the rows in the
Messaged_users table are for account 5324, and the other half are
for other accounts. So when we try to run the program with 100,000 rows,
we are processing 100,000 rows for account 5324, out of the 200,000
rows in the messaged_users table, and joining each of those 100,000
rows to the corresponding row in the users table, to get 100,000 rows
in the result set.

I'm not sure what the program is trying to do, but I still don't see the
performance bug yet.

I'll try running the program with still bigger table sizes, to see if
there is a "knee" somewhere.

Here's the output from smaller runs:

C:\bryan\src\derby\tests>java -cp .;%CLASSPATH% Main
Will run with 1000 rows.
* Testing with org.apache.derby.jdbc.EmbeddedDriver
Done inserting in 5.734 seconds
There are 1000 rows in users
There are 1000 rows in messaged_users
Done querying in 5.922 seconds
Retrieved 0 rows total.
There are 1000 rows in users
There are 2000 rows in messaged_users
Done querying in 6.234 seconds
Retrieved 1000 rows total.

C:\bryan\src\derby\tests>java -cp .;%CLASSPATH% Main 2000
Will run with 2000 rows.
* Testing with org.apache.derby.jdbc.EmbeddedDriver
Done inserting in 7.625 seconds
There are 2000 rows in users
There are 2000 rows in messaged_users
Done querying in 7.844 seconds
Retrieved 0 rows total.
There are 2000 rows in users
There are 4000 rows in messaged_users
Done querying in 8.547 seconds
Retrieved 2000 rows total.

C:\bryan\src\derby\tests>java -cp .;%CLASSPATH% Main 5000
Will run with 5000 rows.
* Testing with org.apache.derby.jdbc.EmbeddedDriver
Done inserting in 12.016 seconds
There are 5000 rows in users
There are 5000 rows in messaged_users
Done querying in 12.25 seconds
Retrieved 0 rows total.
There are 5000 rows in users
There are 10000 rows in messaged_users
Done querying in 13.703 seconds
Retrieved 5000 rows total.

C:\bryan\src\derby\tests>java -cp .;%CLASSPATH% Main 10000
Will run with 10000 rows.
* Testing with org.apache.derby.jdbc.EmbeddedDriver
Done inserting in 20.984 seconds
There are 10000 rows in users
There are 10000 rows in messaged_users
Done querying in 22.046 seconds
Retrieved 1 rows total.
There are 10000 rows in users
There are 19999 rows in messaged_users
Done querying in 33.374 seconds
Retrieved 10000 rows total.

C:\bryan\src\derby\tests>java -cp .;%CLASSPATH% Main 20000
Will run with 20000 rows.
* Testing with org.apache.derby.jdbc.EmbeddedDriver
Done inserting in 44.64 seconds
There are 20000 rows in users
There are 20000 rows in messaged_users
Done querying in 45.281 seconds
Retrieved 1 rows total.
There are 20000 rows in users
There are 39999 rows in messaged_users
Done querying in 93.624 seconds
Retrieved 20000 rows total.

C:\bryan\src\derby\tests>java -cp .;%CLASSPATH% Main 40000
Will run with 40000 rows.
* Testing with org.apache.derby.jdbc.EmbeddedDriver
Done inserting in 79.406 seconds
There are 40000 rows in users
There are 40000 rows in messaged_users
Done querying in 80.515 seconds
Retrieved 1 rows total.
There are 40000 rows in users
There are 79999 rows in messaged_users
Done querying in 368.888 seconds
Retrieved 40000 rows total.


> 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, 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.


Mime
View raw message