You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br> on 2003/09/04 18:21:15 UTC
Dynamic proxies and DB index usage optimization
Hi!
I'm using dynamic proxy feature in a large database.
I've detected the OJB always try to get every field, even if it is not used (in dynamic proxy, only the PK is required). This gives a performance penalty in large databases. I've a large database that takes near 60 seconds to load 5000 records (not so bad, but could be better) because my table has more fields (more, one is a blob that can't be indexed) and ao ojb turns impossible to use indexes under some circustances.
The question is: is possible make the query bellow be just
SELECT A0.CO_PESSOA FROM MGR.TB_PESSOA A0 WHERE A0.CO_PESSOA IN (SELECT min(A0.CO_PESSOA) FROM MGR.TB_PESSOA A0 WHERE A0.NO_PESSOA IN (SELECT min(A0.NO_PESSOA) FROM MGR.TB_PESSOA A0 WHERE A0.ST_ATIVO = ? ) )
so SapDB will use my index (and query will be so fast)?
Here is the log showing the "feature":
com.sap.dbtech.jdbc.ConnectionSapDB@12696c2.isClosed ()
=> false
com.sap.dbtech.jdbc.ConnectionSapDB@12696c2.getAutoCommit ()
=> true
com.sap.dbtech.jdbc.ConnectionSapDB@12696c2.prepareStatement (SELECT A0.ST_ATIVO,A0.IM_FOTO,A0.NO_PESSOA,A0.CO_PESSOA,A0.DS_NOME_FANTASIA,A0.ST_FISICA_JURIDICA FROM MGR.TB_PESSOA A0 WHERE A0.CO_PESSOA IN (SELECT min(A0.CO_PESSOA) FROM MGR.TB_PESSOA A0 WHERE A0.NO_PESSOA IN (SELECT min(A0.NO_PESSOA) FROM MGR.TB_PESSOA A0 WHERE A0.ST_ATIVO = ? ) ), 1003, 1007)
=> com.sap.dbtech.jdbc.CallableStatementSapDB@1465ca2
com.sap.dbtech.jdbc.CallableStatementSapDB@1465ca2.setObject (1, T, 1)
com.sap.dbtech.jdbc.CallableStatementSapDB@1465ca2.executeQuery ()
=> com.sap.dbtech.jdbc.ResultSetSapDB@6bba64
com.sap.dbtech.jdbc.ResultSetSapDB@6bba64.next ()
=> true
com.sap.dbtech.jdbc.ResultSetSapDB@6bba64.getInt (CO_PESSOA)
=> 8
com.sap.dbtech.jdbc.ResultSetSapDB@6bba64.wasNull ()
=> false
com.sap.dbtech.jdbc.ResultSetSapDB@6bba64.next ()
=> false
com.sap.dbtech.jdbc.ResultSetSapDB@6bba64.close ()
com.sap.dbtech.jdbc.CallableStatementSapDB@1465ca2.clearParameters ()
com.sap.dbtech.jdbc.ConnectionSapDB@12696c2.isClosed ()
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.515 / Virus Database: 313 - Release Date: 1/9/2003
Re: Simple (?) Proxy-Questions (Sorry for reposting)
Posted by Joerg Lensing <in...@softcon-lensing.de>.
Armin Waibel wrote:
>Hi,
>
>
>
>>No more hints? :-(
>>
>>
>hmm, can you write a test case reproduce
>your problem?
>
>
yes, I try it for tomorrow
thanx joerg
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Simple (?) Proxy-Questions (Sorry for reposting)
Posted by Armin Waibel <ar...@code-au-lait.de>.
Hi,
> No more hints? :-(
hmm, can you write a test case reproduce
your problem?
regards,
Armin
----- Original Message -----
From: "Joerg Lensing" <in...@softcon-lensing.de>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Monday, September 15, 2003 5:16 PM
Subject: Re: Simple (?) Proxy-Questions (Sorry for reposting)
> Joerg Lensing wrote:
>
> > Armin Waibel wrote:
> >
> >> Hi Joerg,
> >>
> >>
> >>
> >>>> - the Object is a superclass of two Objects (Classes A1,A2) both
> >>>> mapped to the same table
> >>>>
> >>>
> >> do you use 'ojbConcreteClass' feature for A1, A2 classes
> >> mapped to the same table?
> >>
> > yes!
> >
> > joerg
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Simple (?) Proxy-Questions (Sorry for reposting)
Posted by Joerg Lensing <in...@softcon-lensing.de>.
Joerg Lensing wrote:
> Armin Waibel wrote:
>
>> Hi Joerg,
>>
>>
>>
>>>> - the Object is a superclass of two Objects (Classes A1,A2) both
>>>> mapped to the same table
>>>>
>>>
>> do you use 'ojbConcreteClass' feature for A1, A2 classes
>> mapped to the same table?
>>
> yes!
>
> joerg
>
No more hints? :-(
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Simple (?) Proxy-Questions (Sorry for reposting)
Posted by Joerg Lensing <in...@softcon-lensing.de>.
Armin Waibel wrote:
>Hi Joerg,
>
>
>
>>>- the Object is a superclass of two Objects (Classes A1,A2) both
>>>mapped to the same table
>>>
>>>
>do you use 'ojbConcreteClass' feature for A1, A2 classes
>mapped to the same table?
>
yes!
joerg
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Simple (?) Proxy-Questions (Sorry for reposting)
Posted by Armin Waibel <ar...@code-au-lait.de>.
Hi Joerg,
> > - the Object is a superclass of two Objects (Classes A1,A2) both
> > mapped to the same table
do you use 'ojbConcreteClass' feature for A1, A2 classes
mapped to the same table?
regards,
Armin
----- Original Message -----
From: "Joerg Lensing" <in...@softcon-lensing.de>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Monday, September 15, 2003 12:21 PM
Subject: Simple (?) Proxy-Questions (Sorry for reposting)
> Hi ojb-users,
> I'm still struggling with dynamic proxies and I have no more idea what
I can
> do. Could someone please help me with this?
>
> joerg
>
> Joerg Lensing wrote:
>
> > Hi all,
> > some questions about proxies:
> >
> > - I want to query an Object
> > - the object (class A) is used by a dynamic proxy
> > - the Object is a superclass of two Objects (Classes A1,A2) both
> > mapped to the same table
> > - the extended objects are dynamic proxies too
> > - the criteria is a field from the superclass (A)
> >
> > how do the proxies know, which Interface (for class A1 OR A2) is the
> > right one (because only ONE interface can be the right one)
> >
> > The reason for asking is: In my code, allways the wrong interface is
> > returned (see mails before)
> >
> > thanks, joerg
> >
> >
>
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Simple (?) Proxy-Questions (Sorry for reposting)
Posted by Joerg Lensing <in...@softcon-lensing.de>.
Hi ojb-users,
I'm still struggling with dynamic proxies and I have no more idea what I can
do. Could someone please help me with this?
joerg
Joerg Lensing wrote:
> Hi all,
> some questions about proxies:
>
> - I want to query an Object
> - the object (class A) is used by a dynamic proxy
> - the Object is a superclass of two Objects (Classes A1,A2) both
> mapped to the same table
> - the extended objects are dynamic proxies too
> - the criteria is a field from the superclass (A)
>
> how do the proxies know, which Interface (for class A1 OR A2) is the
> right one (because only ONE interface can be the right one)
>
> The reason for asking is: In my code, allways the wrong interface is
> returned (see mails before)
>
> thanks, joerg
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Simple (?) Proxy-Questions
Posted by Joerg Lensing <in...@softcon-lensing.de>.
Hi all,
some questions about proxies:
- I want to query an Object
- the object (class A) is used by a dynamic proxy
- the Object is a superclass of two Objects (Classes A1,A2) both mapped
to the same table
- the extended objects are dynamic proxies too
- the criteria is a field from the superclass (A)
how do the proxies know, which Interface (for class A1 OR A2) is the
right one (because only ONE interface can be the right one)
The reason for asking is: In my code, allways the wrong interface is
returned (see mails before)
thanks, joerg
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Dynamic proxies and DB index usage optimization
Posted by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>.
Sending again... Sorry, but it's important...
----- Original Message -----
From: Edson Carlos Ericksson Richter
To: 'OJB Users List'
Sent: Thursday, September 04, 2003 1:21 PM
Subject: Dynamic proxies and DB index usage optimization
Hi!
I'm using dynamic proxy feature in a large database.
I've detected the OJB always try to get every field, even if it is not used
(in dynamic proxy, only the PK is required, right?). This gives a
performance penalty in large databases. I've a large database that takes
near 60 seconds to load 5000 records (not so bad, but could be better)
because my table has more fields (more, one is a blob that can't be indexed)
and ojb turns impossible to use indexes under some circustances.
The question is: is possible make the query bellow be just
SELECT A0.CO_PESSOA FROM MGR.TB_PESSOA A0 WHERE A0.CO_PESSOA IN (SELECT
min(A0.CO_PESSOA) FROM MGR.TB_PESSOA A0 WHERE A0.NO_PESSOA IN (SELECT
min(A0.NO_PESSOA) FROM MGR.TB_PESSOA A0 WHERE A0.ST_ATIVO = ? ) )
so SapDB will use my index (and query will be so fast)?
Here is the log (captured directly in the driver) showing the "feature" (as
you can see, even using only the PK, all fields are in field list clause):
com.sap.dbtech.jdbc.ConnectionSapDB@12696c2.isClosed ()
=> false
com.sap.dbtech.jdbc.ConnectionSapDB@12696c2.getAutoCommit ()
=> true
com.sap.dbtech.jdbc.ConnectionSapDB@12696c2.prepareStatement (SELECT
A0.ST_ATIVO,A0.IM_FOTO,A0.NO_PESSOA,A0.CO_PESSOA,A0.DS_NOME_FANTASIA,A0.ST_F
ISICA_JURIDICA FROM MGR.TB_PESSOA A0 WHERE A0.CO_PESSOA IN (SELECT
min(A0.CO_PESSOA) FROM MGR.TB_PESSOA A0 WHERE A0.NO_PESSOA IN (SELECT
min(A0.NO_PESSOA) FROM MGR.TB_PESSOA A0 WHERE A0.ST_ATIVO = ? ) ), 1003,
1007)
=> com.sap.dbtech.jdbc.CallableStatementSapDB@1465ca2
com.sap.dbtech.jdbc.CallableStatementSapDB@1465ca2.setObject (1, T, 1)
com.sap.dbtech.jdbc.CallableStatementSapDB@1465ca2.executeQuery ()
=> com.sap.dbtech.jdbc.ResultSetSapDB@6bba64
com.sap.dbtech.jdbc.ResultSetSapDB@6bba64.next ()
=> true
com.sap.dbtech.jdbc.ResultSetSapDB@6bba64.getInt (CO_PESSOA)
=> 8
com.sap.dbtech.jdbc.ResultSetSapDB@6bba64.wasNull ()
=> false
com.sap.dbtech.jdbc.ResultSetSapDB@6bba64.next ()
=> false
com.sap.dbtech.jdbc.ResultSetSapDB@6bba64.close ()
com.sap.dbtech.jdbc.CallableStatementSapDB@1465ca2.clearParameters ()
com.sap.dbtech.jdbc.ConnectionSapDB@12696c2.isClosed ()
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.515 / Virus Database: 313 - Release Date: 1/9/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 1/9/2003
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org