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): [EMAIL PROTECTED] () => false [EMAIL PROTECTED] () => true [EMAIL PROTECTED] (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) => [EMAIL PROTECTED] [EMAIL PROTECTED] (1, T, 1) [EMAIL PROTECTED] () => [EMAIL PROTECTED] [EMAIL PROTECTED] () => true [EMAIL PROTECTED] (CO_PESSOA) => 8 [EMAIL PROTECTED] () => false [EMAIL PROTECTED] () => false [EMAIL PROTECTED] () [EMAIL PROTECTED] () [EMAIL PROTECTED] () --- 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
