1 Reply Latest reply on May 5, 2005 9:34 PM by markbrazil

help with read-ahead queries

markbrazil May 5, 2005 4:37 AM

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).

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.

1. Re: help with read-ahead queries

markbrazil May 5, 2005 9:34 PM (in response to markbrazil)

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=?)
Actions