• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

jboss, xdoclet, read-ahead queries

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm looking for help with on-find and cmr queries with jboss4.02 and xdoclet.

I have posted this question to jboss forum without any replies.

So here goes again, any help would be great.

--------------------------------------

I'm using XDoclet 1.2.3 and JBoss 4.02

I have specified load-group and finder queries with read-ahead, but still for a query of a few thousand records I'm getting a few thousand select's (as seen from server.log).

The issue is mainly to do with the cmr relationships at how it generates extra sql statments at the database level.

Here is what I have at the class level

* @jboss.load-group
* name="modules"
* description="module fields used in query"
*
* @jboss.read-ahead strategy = "on-find"
* page-size = "200"
* eager-load-group="modules"
*
* @jboss.query
* signature = "java.util.Collection findByDynamic(java.lang.String query, java.lang.Object[] args)"
*
* dynamic = "true"
* eager-load-group="modules"
* strategy="on-find"
* page-size="200"
*


For every field I put
* @jboss.load-group name="modules"



For CMR relationships I put

* @jboss.relation-read-ahead
* strategy = "on-find"
* eager-load-group="modules"
* page-size = "50"


Any help would be great as performance isn't good at the moment.

Thanks,
Mark.
 
Mark Brazil
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The problem seems to be with the relationships. I had thought with the load-group's it would combine the query's.

This is the agentbean's code

while (it.hasNext()) {
CopmBaleLocal bale = ((CopmBaleLocal)it.next());
CopmBaleLightValue baleVO = bale.getCopmBaleLightValue();

CopmModuleLocal module = bale.getCopmModule();
CopmModuleLightValue moduleVO = module.getCopmModuleLightValue();

SalesBatchBaleLocal salesBale = bale.getSalesBatchBale();

HviResultLocal hvi = bale.getHviResults();
VisualResultLocal visual = bale.getVisualResults();
VisualResultValue visualVO =null;
AverageResultLocal average = module.getAverageResults();
AverageResultValue averageVO = null;

QualityStackLocal qstack = bale.getQualityStack();



Below, is what it produces in the server.log file (plus thousands more !)

Is there a way to combine some of these query's ?

Thanks,
Mark.


2005-05-06 11:28:26,361 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.CopmBale#findByDynamic] DYNAMIC-QL: SELECT OBJECT(b) from CopmModule m, IN(m.bales) as b where 1=1 and m.ginNo = ?1 and b.merchant = ?2 order by b.baleNo
2005-05-06 11:28:26,361 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.CopmBale#findByDynamic] Executing SQL: SELECT t0_b.baleId, t0_b.bale_no, t0_b.bale_date, t0_b.bale_weight, t0_b.grower_code, t0_b.pool_code, t0_b.classer, t0_b.merchant_code, t0_b.gin_run, t0_b.fire_suspect, t0_b.contract_reference, t0_b.whse_code, t0_b.bale_notes, t0_b.bale_status, t0_b.created_in_apps, t0_b.apps_update_needed, t0_b.moduleId_fk, t0_b.bale_no FROM COPM_MODULE t1_m, COPM_BALE t0_b WHERE ((1 = 1 AND t1_m.gin_no = ? AND t0_b.merchant_code = ?)) AND t1_m.module_id=t0_b.moduleId_fk ORDER BY t0_b.bale_no ASC
2005-05-06 11:28:27,814 INFO [STDOUT] ==========> no of bales : 1855
2005-05-06 11:28:27,845 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:27,876 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:27,908 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmModule] Executing SQL: SELECT module_no, module_date, gin_no, grower_code, field_code, module_weight, seed_weight, mote_weight, trash_weight, location, variety, variety_group, pure_seed, stripper_picked, second_pick, dryland, fire_suspect, bur, grass, trashy, wet, postRain, trial, disease, auscott_loan, merchant_code, pool_code, contract_reference, no_of_bales, created_in_apps, apps_update_needed FROM COPM_MODULE WHERE (module_id=?)
2005-05-06 11:28:27,939 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:27,954 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.CopmBale] load relation SQL: SELECT salesBaleId FROM COPM_SALES_BATCH_BALE WHERE (baleId_fk=?)
2005-05-06 11:28:27,986 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:27,986 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.CopmBale] load relation SQL: SELECT resultId FROM COPM_HVI_RESULT WHERE (baleId_fk=?)
2005-05-06 11:28:28,079 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:28,079 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.CopmBale] load relation SQL: SELECT resultId FROM COPM_VISUAL_RESULT WHERE (baleId_fk=?)
2005-05-06 11:28:28,126 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmModule] Executing SQL: SELECT module_no, module_date, gin_no, grower_code, field_code, module_weight, seed_weight, mote_weight, trash_weight, location, variety, variety_group, pure_seed, stripper_picked, second_pick, dryland, fire_suspect, bur, grass, trashy, wet, postRain, trial, disease, auscott_loan, merchant_code, pool_code, contract_reference, no_of_bales, created_in_apps, apps_update_needed FROM COPM_MODULE WHERE (module_id=?)
2005-05-06 11:28:28,126 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.CopmModule] load relation SQL: SELECT resultId FROM COPM_AVERAGE_RESULT WHERE (moduleId_fk=?)
2005-05-06 11:28:28,189 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:28,189 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.CopmBale] load relation SQL: SELECT qualityStackId FROM COPM_QUALITY_STACKS WHERE (baleId_fk=?)
2005-05-06 11:28:28,236 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.SalesBatchBale] Executing SQL: SELECT dateCreated, baleId_fk, lineId_fk FROM COPM_SALES_BATCH_BALE WHERE (salesBaleId=?)
2005-05-06 11:28:28,267 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.SalesBatchLine] Executing SQL: SELECT spec_id, spec_name, line_no, starting_spec, max_bales, curr_bales, curr_weight, batchId_fk FROM COPM_SALES_BATCH_LINE WHERE (line_id=?)
2005-05-06 11:28:28,314 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.SalesBatch] Executing SQL: SELECT build_date, sales_lot_id, gin_no, formula_id, formula_no, max_bales, curr_bales, max_weight, curr_weight, warehouse, quality_Stack_Lot, status, shipmentId_fk FROM COPM_SALES_BATCH WHERE (batch_id=?)
2005-05-06 11:28:28,329 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.VisualResult] Executing SQL: SELECT resultDate, vgResult, vlResult, extResult, status, signedOff, created_in_apps, apps_update_needed, baleId_fk FROM COPM_VISUAL_RESULT WHERE (resultId=?)
2005-05-06 11:28:28,361 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.AverageResult] Executing SQL: SELECT resultDate, avgL2Result, avgUnResult, avgStrResult, avgMcResult, status, signedOff, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_AVERAGE_RESULT WHERE (resultId=?)
2005-05-06 11:28:28,392 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.AverageResult] Executing SQL: SELECT resultDate, avgL2Result, avgUnResult, avgStrResult, avgMcResult, status, signedOff, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_AVERAGE_RESULT WHERE (resultId=?)
2005-05-06 11:28:28,392 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.AverageResult] Executing SQL: SELECT resultDate, avgL2Result, avgUnResult, avgStrResult, avgMcResult, status, signedOff, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_AVERAGE_RESULT WHERE (resultId=?)
2005-05-06 11:28:28,408 INFO [STDOUT] array count 0 of 1855
2005-05-06 11:28:28,408 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:28,408 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:28,408 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmModule] Executing SQL: SELECT module_no, module_date, gin_no, grower_code, field_code, module_weight, seed_weight, mote_weight, trash_weight, location, variety, variety_group, pure_seed, stripper_picked, second_pick, dryland, fire_suspect, bur, grass, trashy, wet, postRain, trial, disease, auscott_loan, merchant_code, pool_code, contract_reference, no_of_bales, created_in_apps, apps_update_needed FROM COPM_MODULE WHERE (module_id=?)
2005-05-06 11:28:28,454 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:28,454 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.CopmBale] load relation SQL: SELECT salesBaleId FROM COPM_SALES_BATCH_BALE WHERE (baleId_fk=?)
2005-05-06 11:28:28,470 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:28,470 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.CopmBale] load relation SQL: SELECT resultId FROM COPM_HVI_RESULT WHERE (baleId_fk=?)
2005-05-06 11:28:28,470 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:28,486 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.CopmBale] load relation SQL: SELECT resultId FROM COPM_VISUAL_RESULT WHERE (baleId_fk=?)
2005-05-06 11:28:28,486 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmModule] Executing SQL: SELECT module_no, module_date, gin_no, grower_code, field_code, module_weight, seed_weight, mote_weight, trash_weight, location, variety, variety_group, pure_seed, stripper_picked, second_pick, dryland, fire_suspect, bur, grass, trashy, wet, postRain, trial, disease, auscott_loan, merchant_code, pool_code, contract_reference, no_of_bales, created_in_apps, apps_update_needed FROM COPM_MODULE WHERE (module_id=?)
2005-05-06 11:28:28,501 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.CopmModule] load relation SQL: SELECT resultId FROM COPM_AVERAGE_RESULT WHERE (moduleId_fk=?)
2005-05-06 11:28:28,501 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:28,501 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.CopmBale] load relation SQL: SELECT qualityStackId FROM COPM_QUALITY_STACKS WHERE (baleId_fk=?)
2005-05-06 11:28:28,517 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.SalesBatchBale] Executing SQL: SELECT dateCreated, baleId_fk, lineId_fk FROM COPM_SALES_BATCH_BALE WHERE (salesBaleId=?)
2005-05-06 11:28:28,517 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.SalesBatchLine] Executing SQL: SELECT spec_id, spec_name, line_no, starting_spec, max_bales, curr_bales, curr_weight, batchId_fk FROM COPM_SALES_BATCH_LINE WHERE (line_id=?)
2005-05-06 11:28:28,517 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.SalesBatch] Executing SQL: SELECT build_date, sales_lot_id, gin_no, formula_id, formula_no, max_bales, curr_bales, max_weight, curr_weight, warehouse, quality_Stack_Lot, status, shipmentId_fk FROM COPM_SALES_BATCH WHERE (batch_id=?)
2005-05-06 11:28:28,533 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.VisualResult] Executing SQL: SELECT resultDate, vgResult, vlResult, extResult, status, signedOff, created_in_apps, apps_update_needed, baleId_fk FROM COPM_VISUAL_RESULT WHERE (resultId=?)
2005-05-06 11:28:28,533 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.AverageResult] Executing SQL: SELECT resultDate, avgL2Result, avgUnResult, avgStrResult, avgMcResult, status, signedOff, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_AVERAGE_RESULT WHERE (resultId=?)
2005-05-06 11:28:28,548 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.AverageResult] Executing SQL: SELECT resultDate, avgL2Result, avgUnResult, avgStrResult, avgMcResult, status, signedOff, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_AVERAGE_RESULT WHERE (resultId=?)
2005-05-06 11:28:28,548 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.AverageResult] Executing SQL: SELECT resultDate, avgL2Result, avgUnResult, avgStrResult, avgMcResult, status, signedOff, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_AVERAGE_RESULT WHERE (resultId=?)
2005-05-06 11:28:28,564 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
2005-05-06 11:28:28,564 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.CopmBale] Executing SQL: SELECT bale_no, bale_date, bale_weight, grower_code, pool_code, classer, merchant_code, gin_run, fire_suspect, contract_reference, whse_code, bale_notes, bale_status, created_in_apps, apps_update_needed, moduleId_fk FROM COPM_BALE WHERE (baleId=?)
 
We're all out of roofs. But we still have tiny ads:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic