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 21:27: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_Updated.java

Very interesting!

With your revised Main_Updated.java, I *AM* able to reproduce the behavior you see.

Furthermore, I then had an idea, and I took your Main_Updated.java
and updated it again:
 - I changed doQuery() so that it took a string of whitespace, and
   inserted that whitespace into the query it runs
 - I changed doSingleDriver so that it called doQuery() twice, passing
   a different string of whitespace each time.

Now, with the attached Main_Updated.java, I can run doQuery() twice,
and BOTH queries run very fast.

By changing the whitespace in the SQL text, I force Derby to re-compile
the query, and not use the pre-compiled query.

So, I think, it must be choosing a completely different query plan
when I trick the whitespace to be different.

Sure enough, I turned on Query Plan logging, and I can see that Derby
comes up with two totally different query plans for the same statement:
- the first time, it chooses a HashExists join with USERS as the left
  result set, and MESSAGED_USERS as the right result set
- the second time, it chooses a NestedLoopExists join with
  MESSAGED_USERS as the left result set and USERS as the
  right result set.

I'll attach the query plan logging in a separate comment.

This makes me think that:
1) There is an optimizer problem here, where sometimes Derby's
    optimizer is choosing a query plan that works well for the
    current conditions, but would fail badly if the table contents
    change significantly. It would be great if we could teach
   Derby's optimizer that, when it knows it's using such a plan, it
   should avoid caching the query plan forever. Or at least, that
   when re-using an already cached query plan, it should check to
   see if the table sizes/distributions have changed enough that
   it should re-optimize the plan rather than re-using the cached one.
2) If the optimizer is forced to re-optimize the query with the
   current conditions, it appears to pick a reasonable query plan
3) You can probably use this as a workaround: simply change your
   application so that, when you issue the query, you provide a
   slightly different string of whitespace in the query text, to
   force Derby to re-compile the query. It's an ugly workaround,
   but maybe you can use it for now.


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