db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Amit <mramitk...@gmail.com>
Subject Re: st.executeQuery() got stuck
Date Thu, 04 Jul 2013 11:30:00 GMT
Thanks Dyre for your reply.

Yes, I mean it hangs in executeQuery().
The derby running as client/server mode.
Other threads/connections accessing the same table simultaneously.
The tables are not that large. Max records among the tables listed in the
query is 5,000

This is the query - (The columns ends with "_ID" are indexed)
SELECT "G_EVNT_MASTER_NAME",
       B."G_CORE_PARTY_NAME",
       A."G_CORE_PARTY_NAME",
       "G_EVNT_MASTER_START_DT",
       "G_EVNT_MASTER_ID",
       "G_EVNT_MASTER_OFFERING",
       "G_CORE_DOCUMENT_NAME",
       "G_CORE_DOCUMENT_URL"
FROM   "INTERNAL"."G_CUST_OPPORTUNITY",
       "INTERNAL"."G_XREF_EVNT_OPPR",
       "INTERNAL"."G_CORE_PARTY" A,
       "INTERNAL"."G_CORE_PARTY" B,
       "INTERNAL"."G_XREF_EVNT_PARTY",
       "INTERNAL"."G_EVNT_MASTER"
       LEFT OUTER JOIN "INTERNAL"."G_CORE_DOCUMENT"
                    ON G_EVNT_MASTER_ID = G_CORE_DOCUMENT_MEETING_ID
WHERE  "G_EVNT_MASTER_ID" = "G_XREF_EVNT_OPPR_EVENT_ID"
       AND "G_XREF_EVNT_OPPR_OPORTUNITY_ID" = "G_CUST_OPPORTUNITY_ID"
       AND A."G_CORE_PARTY_ID" = "G_EVNT_MASTER_ORGANIZER_ID"
       AND B."G_CORE_PARTY_ID" = "G_XREF_EVNT_PARTY_PARTY_ID"
       AND "G_XREF_EVNT_PARTY_EVENT_ID" = "G_EVNT_MASTER_ID"
       AND "G_EVNT_MASTER_TYPE" = 'Contact Event'
       AND "G_XREF_EVNT_PARTY_TYPE" = 'Primary Contact'
       AND "G_CUST_OPPORTUNITY_ID" = '99223977'



This is the derby.log -

Booting Derby version The Apache Software Foundation - Apache Derby -
10.9.1.0 - (1344872): instance a816c00e-013f-a339-24f0-0000697cf4d7
on database directory E:\database1 with class loader
sun.misc.Launcher$AppClassLoader@f08d0f
Loaded from file:/E:/lib/drivers/derby.jar
java.vendor=Oracle Corporation
java.runtime.version=1.7.0_13-b20
user.dir=E:\
derby.system.home=E:\
Database Class Loader started - derby.database.classpath=''

-- 
Thanks,
Amit



On Thu, Jul 4, 2013 at 4:06 PM, Dyre Tjeldvoll <Dyre.Tjeldvoll@oracle.com>wrote:

> On 07/ 4/13 10:36 AM, Amit wrote:
>
>> This is the code I am using to execute query.
>>
>> Statement st = con.createStatement(ResultSet.**TYPE_FORWARD_ONLY,
>> ResultSet.CONCUR_READ_ONLY);
>> st.setFetchSize(100);
>> ResultSet queryRs = st.executeQuery(sql);
>> System.out.println("Success");
>>
>> Usually the query takes 1-2 sec to execute. But very rare occasion the
>> code got stuck at line 4.
>>
>
> That would be println... I assume you mean that it hangs in executeQuery()?
>
>
>  I have waited for 1 hr and then killed the
>> thread. It's a production issue. I am using connection pool and my derby
>> version is - 10.9.1.0
>>
>> Can you help me narrow down the problem.
>>
>
> Really hard without more information. If you can post the SQL involved it
> would be helpful. Is this client/server or embedded?
> Is the table large? Do you access it through an index? Are other
> threads/connections accessing the same table simultaneously?
> Anything in derby.log at the time of the hang?
>
>
> Regards,
>
> Dyre
>
>
>

Mime
View raw message