aspose file tools*
The moose likes EJB and other Java EE Technologies and the fly likes jboss, xdoclet, read-ahead queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "jboss, xdoclet, read-ahead queries" Watch "jboss, xdoclet, read-ahead queries" New topic
Author

jboss, xdoclet, read-ahead queries

Mark Brazil
Greenhorn

Joined: May 11, 2005
Posts: 2
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

Joined: May 11, 2005
Posts: 2
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=?)
 
Consider Paul's rocket mass heater.
 
subject: jboss, xdoclet, read-ahead queries