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

    help with read-ahead queries

    Mark Brazil Newbie

      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
          Mark Brazil Newbie

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