We are seeing very poor performance for dynamic-mapped entities. A simple query takes over 20 seconds to complete, vs. around 1 second for the same query in SQL executed via straight JDBC.

The code follows:
import org.hibernate.cfg.Configuration;
import org.hibernate.SessionFactory;
import org.hibernate.Session;
import org.hibernate.Query;
import org.hibernate.Transaction;

import java.util.*;

class GoGetEm
{
public static void main (String args[])
{
Configuration c = new Configuration();
SessionFactory sf = c.configure().buildSessionFactory();
Session s = sf.openSession();
Query q = s.createQuery("select new list(pgl, accmas) from rdb_patient_group_list as pgl, rms_pdraccmas as accmas where pgl.ch_pno_key = accmas.ch_pno_key and pgl.user_number = '9949' and exists (from rms_pdraccmas as accmas where accmas.ch_hosp_id_code = 1 and accmas.ch_site_cd = '01' and accmas.ch_pno_key = pgl.ch_pno_key)");
List l = q.list();
Iterator i = l.iterator();
while (i.hasNext()) {
List il = (List) i.next();
Map item = (Map) il.get(1);
String last_name = (String) item.get("ch_pt_last_name");
System.out.println(last_name);
}
s.flush();
s.clear();
}
}

mapping files:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class entity-name="rms_pdraccmas" table="PDRACCMAS" lazy="false">
<id name="id" type="string" column="cnxrowid">
<generator class="assigned"/>
</id>
<property name="ch_pno_key" type="BigDUserType"/>
<property name="ch_status_key" type="string" length="2"/>
<property name="ch_pt_io_ind" type="string" length="1"/>
<property name="ch_pt_type" type="string" length="1"/>
<property name="ch_pay_class" type="string" length="2"/>
<property name="ch_pt_last_name" type="string" length="32"/>
<property name="ch_pt_first_name" type="string" length="32"/>
<property name="ch_pt_mid_init" type="string" length="16"/>
<property name="ch_enrollment_no_key" type="BigDUserType"/>
<property name="ch_adm_days_key" type="BigDUserType"/>
<property name="ch_mrn_key" type="string" length="16"/>
<property name="ch_grn_bl_no" type="BigDUserType"/>
<property name="ch_fyr_fpd_key_fill" type="BigDUserType"/>
<property name="ch_fyr" type="BigDUserType"/>
<property name="ch_dis_fpd" type="BigDUserType"/>
<property name="ch_followup_active_ind" type="string" length="1"/>
<property name="ch_dev_fill1" type="string" length="5"/>
<property name="ch_add_dt" type="timestamp"/>
<property name="ch_chg_dt" type="timestamp"/>
<property name="ch_adm_dt" type="timestamp"/>
<property name="ch_dis_dt" type="timestamp"/>
<property name="ch_add_res_dt" type="timestamp"/>
<property name="ch_add_pa_dt" type="timestamp"/>
<property name="ch_original_bill_dt" type="timestamp"/>
<property name="ch_purge_dt" type="timestamp"/>
<property name="ch_pd_purge_dt" type="timestamp"/>
<property name="ch_prev_adm_dt" type="timestamp"/>
<property name="ch_roll_from_eff_dt" type="timestamp"/>
<property name="ch_roll_to_eff_dt" type="timestamp"/>
<property name="ch_last_serv_dt" type="timestamp"/>
<property name="ch_retirement_dt" type="timestamp"/>
<property name="ch_last_actv_dt" type="timestamp"/>
<property name="ch_last_invoice_dt" type="timestamp"/>
<property name="ch_contract_dt" type="timestamp"/>
<property name="ch_last_pmt_dt" type="timestamp"/>
<property name="ch_conv_dt" type="timestamp"/>
<property name="ch_last_fin_chg_dt" type="timestamp"/>
<property name="ch_last_stmt_dt" type="timestamp"/>
<property name="ch_preadm_dt" type="timestamp"/>
<property name="ch_preadm_test_dt" type="timestamp"/>
<property name="ch_accident_dt" type="timestamp"/>
<property name="ch_ur_app_from_dt" type="timestamp"/>
<property name="ch_ur_app_to_dt" type="timestamp"/>
<property name="ch_agcy_xfer_dt" type="timestamp"/>
<property name="ch_trx_user_dt1" type="timestamp"/>
<property name="ch_user_dt1" type="timestamp"/>
<property name="ch_user_dt2" type="timestamp"/>
<property name="ch_user_dt3" type="timestamp"/>
<property name="ch_user_dt4" type="timestamp"/>
<property name="ch_user_dt5" type="timestamp"/>
<property name="ch_user_dt6" type="timestamp"/>
<property name="ch_user_dt7" type="timestamp"/>
<property name="ch_user_dt8" type="timestamp"/>
<property name="ch_user_dt9" type="timestamp"/>
<property name="ch_user_dt10" type="timestamp"/>
<property name="ch_tot_acct_bal" type="BigDUserType"/>
<property name="ch_billed_amt" type="BigDUserType"/>
<property name="ch_pt_due_amt" type="BigDUserType"/>
<property name="ch_ins_due_amt" type="BigDUserType"/>
<property name="ch_tot_pt_pmt_amt" type="BigDUserType"/>
<property name="ch_tot_ins_pmt_amt" type="BigDUserType"/>
<property name="ch_tot_pt_adj_amt" type="BigDUserType"/>
<property name="ch_tot_ins_adj_amt" type="BigDUserType"/>
<property name="ch_grp_total_amt" type="BigDUserType"/>
<property name="ch_grp_drg_amt" type="BigDUserType"/>
<property name="ch_grp_per_diem_amt" type="BigDUserType"/>
<property name="ch_grp_day_otl_amt" type="BigDUserType"/>
<property name="ch_grp_cost_otl_amt" type="BigDUserType"/>
<property name="ch_grp_spec_calc_amt" type="BigDUserType"/>
<property name="ch_tot_ins_bill_adj_amt" type="BigDUserType"/>
<property name="ch_tot_ins_oth_adj_amt" type="BigDUserType"/>
<property name="ch_deposit_quot" type="BigDUserType"/>
<property name="ch_deposit_coll" type="BigDUserType"/>
<property name="ch_bd_due_amt" type="BigDUserType"/>
<property name="ch_contract_amt" type="BigDUserType"/>
<property name="ch_contract_arrears" type="BigDUserType"/>
<property name="ch_pt_pay_since_stmt_amt" type="BigDUserType"/>
<property name="ch_ins_pay_since_stmt_amt" type="BigDUserType"/>
<property name="ch_drg_reimb_rate" type="BigDUserType"/>
<property name="ch_fin_chg_amt" type="BigDUserType"/>
<property name="ch_fin_chg_paid_amt" type="BigDUserType"/>
<property name="ch_last_stmt_amt" type="BigDUserType"/>
<property name="ch_tot_rev" type="BigDUserType"/>
<property name="ch_tot_rnb" type="BigDUserType"/>
<property name="ch_tot_anc" type="BigDUserType"/>
<property name="ch_tot_pro_fee" type="BigDUserType"/>
<property name="ch_drg_adjusted_reimb_rate" type="BigDUserType"/>
<property name="ch_total_actl_cost" type="BigDUserType"/>
<property name="ch_total_std_cost" type="BigDUserType"/>
<property name="ch_actl_fix_dir_cost" type="BigDUserType"/>
<property name="ch_actl_fix_indir_cost" type="BigDUserType"/>
<property name="ch_actl_var_dir_cost" type="BigDUserType"/>
<property name="ch_actl_var_indir_cost" type="BigDUserType"/>
<property name="ch_std_fix_dir_cost" type="BigDUserType"/>
<property name="ch_std_fix_indir_cost" type="BigDUserType"/>
<property name="ch_std_var_dir_cost" type="BigDUserType"/>
<property name="ch_std_var_indir_cost" type="BigDUserType"/>
<property name="ch_ytd_actl_avg" type="BigDUserType"/>
<property name="ch_ytd_std_avg" type="BigDUserType"/>
<property name="ch_user_pn1" type="BigDUserType"/>
<property name="ch_user_pn2" type="BigDUserType"/>
<property name="ch_user_pn3" type="BigDUserType"/>
<property name="ch_user_pn4" type="BigDUserType"/>
<property name="ch_user_pn5" type="BigDUserType"/>
<property name="ch_user_pn6" type="BigDUserType"/>
<property name="ch_user_pn7" type="BigDUserType"/>
<property name="ch_user_pn8" type="BigDUserType"/>
<property name="ch_user_pn9" type="BigDUserType"/>
<property name="ch_user_pn10" type="BigDUserType"/>
<property name="ch_add_user_id" type="integer"/>
<property name="ch_chg_user_id" type="integer"/>
<property name="ch_soft_lock" type="integer"/>
<property name="ch_cds_soft_lock" type="integer"/>
<property name="ch_baby_seq_no" type="integer"/>
<property name="ch_total_stmts" type="integer"/>
<property name="ch_tot_acct_pmts" type="integer"/>
<property name="ch_tot_ins_pmts" type="integer"/>
<property name="ch_tot_pt_pmts" type="integer"/>
<property name="ch_tot_pt_adjs" type="integer"/>
<property name="ch_tot_ins_adjs" type="integer"/>
<property name="ch_tot_acct_adjs" type="integer"/>
<property name="ch_tot_ins_bill_adjs" type="integer"/>
<property name="ch_tot_ins_oth_adjs" type="integer"/>
<property name="ch_grp_return_code" type="integer"/>
<property name="ch_num_carriers" type="integer"/>
<property name="ch_tot_days" type="integer"/>
<property name="ch_ur_grace_days" type="integer"/>
<property name="ch_user_lw1" type="integer"/>
<property name="ch_user_lw2" type="integer"/>
<property name="ch_user_lw3" type="integer"/>
<property name="ch_user_lw4" type="integer"/>
<property name="ch_user_lw5" type="integer"/>
<property name="ch_user_lw6" type="integer"/>
<property name="ch_user_lw7" type="integer"/>
<property name="ch_user_lw8" type="integer"/>
<property name="ch_user_lw9" type="integer"/>
<property name="ch_user_lw10" type="integer"/>
<property name="ch_pt_adm_age" type="BigDUserType"/>
<property name="ch_stmt_notice_no" type="BigDUserType"/>
<property name="ch_adm_md_no" type="BigDUserType"/>
<property name="ch_att_md_no" type="BigDUserType"/>
<property name="ch_ref_md_no" type="BigDUserType"/>
<property name="ch_ref2_md_no" type="BigDUserType"/>
<property name="ch_autopsy_md_no" type="BigDUserType"/>
<property name="ch_last_roll_from_no" type="BigDUserType"/>
<property name="ch_roll_to_no" type="BigDUserType"/>
<property name="ch_hosp_id_code" type="BigDUserType"/>
<property name="ch_dev_fill2" type="string" length="2"/>
<property name="ch_user_un1" type="BigDUserType"/>
<property name="ch_user_un2" type="BigDUserType"/>
<property name="ch_user_un3" type="BigDUserType"/>
<property name="ch_user_un4" type="BigDUserType"/>
<property name="ch_user_un5" type="BigDUserType"/>
<property name="ch_user_un6" type="BigDUserType"/>
<property name="ch_user_un7" type="BigDUserType"/>
<property name="ch_user_un8" type="BigDUserType"/>
<property name="ch_hsp_svc" type="string" length="4"/>
<property name="ch_final_drg" type="string" length="4"/>
<property name="ch_primary_cno" type="string" length="4"/>
<property name="ch_cob2_cno" type="string" length="4"/>
<property name="ch_cob3_cno" type="string" length="4"/>
<property name="ch_cob4_cno" type="string" length="4"/>
<property name="ch_cob5_cno" type="string" length="4"/>
<property name="ch_cob6_cno" type="string" length="4"/>
<property name="ch_cob7_cno" type="string" length="4"/>
<property name="ch_cob8_cno" type="string" length="4"/>
<property name="ch_cob9_cno" type="string" length="4"/>
<property name="ch_epi_cnty_cd" type="string" length="4"/>
<property name="ch_epi_oth_geo_cd" type="string" length="4"/>
<property name="ch_res_rmvl_rsn" type="string" length="4"/>
<property name="ch_add_init" type="string" length="4"/>
<property name="ch_chg_init" type="string" length="4"/>
<property name="ch_adm_init" type="string" length="4"/>
<property name="ch_dis_init" type="string" length="4"/>
<property name="ch_add_pa_init" type="string" length="4"/>
<property name="ch_add_res_init" type="string" length="4"/>
<property name="ch_transient_loc" type="string" length="4"/>
<property name="ch_coll_id" type="string" length="4"/>
<property name="ch_roll_typ_cd" type="string" length="4"/>
<property name="ch_stmt_cntrl_cd" type="string" length="4"/>
<property name="ch_adm_drg" type="string" length="4"/>
<property name="ch_adm_mdc" type="string" length="4"/>
<property name="ch_int_drg" type="string" length="4"/>
<property name="ch_int_mdc" type="string" length="4"/>
<property name="ch_dis_disp_cd" type="string" length="4"/>
<property name="ch_bob_drg" type="string" length="4"/>
<property name="ch_paid_drg" type="string" length="4"/>
<property name="ch_final_mdc" type="string" length="4"/>
<property name="ch_mpr" type="string" length="4"/>
<property name="ch_pt_emp_cd" type="string" length="4"/>
<property name="ch_pt_emp_length" type="string" length="4"/>
<property name="ch_referral_source" type="string" length="8"/>
<property name="ch_prin_dx_cd" type="string" length="8"/>
<property name="ch_prin_px_cd" type="string" length="8"/>
<property name="ch_clc_dpt" type="string" length="5"/>
<property name="ch_clc_svc" type="string" length="2"/>
<property name="ch_clc_ssvc" type="string" length="1"/>
<property name="ch_arrival_dest" type="string" length="8"/>
<property name="ch_tfr_from_hsp_id" type="string" length="8"/>
<property name="ch_tfr_to_hsp_id" type="string" length="8"/>
<property name="ch_adx" type="string" length="8"/>
<property name="ch_sdx" type="string" length="8"/>
<property name="ch_adm_dx_cd" type="string" length="8"/>
<property name="ch_hcpc_px" type="string" length="8"/>
<property name="ch_room_bed" type="string" length="8"/>
<property name="ch_nurse_stn" type="string" length="8"/>
<property name="ch_valuables_id" type="string" length="8"/>
<property name="ch_drg_ret_version" type="string" length="12"/>
<property name="ch_adm_md_name" type="string" length="32"/>
<property name="ch_att_md_name" type="string" length="32"/>
<property name="ch_ref_md_name" type="string" length="32"/>
<property name="ch_ref2_md_name" type="string" length="32"/>
<property name="ch_pt_accident_location" type="string" length="32"/>
<property name="ch_pt_emp_adr_line1" type="string" length="32"/>
<property name="ch_pt_emp_adr_line2" type="string" length="32"/>
<property name="ch_pt_emp_adr_line3" type="string" length="32"/>
<property name="ch_pt_emp_adr_line4" type="string" length="32"/>
<property name="ch_pt_emp_post_code" type="string" length="32"/>
<property name="ch_pt_occup" type="string" length="32"/>
<property name="ch_pt_emp_ph_area" type="string" length="3"/>
<property name="ch_pt_emp_ph_no" type="string" length="7"/>
<property name="ch_pt_emp_phone_expansion" type="string" length="14"/>
<property name="ch_epi_adr_line1" type="string" length="32"/>
<property name="ch_epi_adr_line2" type="string" length="32"/>
<property name="ch_epi_adr_line3" type="string" length="32"/>
<property name="ch_epi_adr_line4" type="string" length="32"/>
<property name="ch_epi_adr_post_code" type="string" length="32"/>
<property name="ch_pt_emp_name" type="string" length="60"/>
<property name="ch_adm_dx_descr" type="string" length="60"/>
<property name="ch_account_comment1" type="string" length="60"/>
<property name="ch_account_comment2" type="string" length="60"/>
<property name="ch_account_comment3" type="string" length="60"/>
<property name="ch_purge_cd" type="string" length="1"/>
<property name="ch_purge_restore_option" type="string" length="1"/>
<property name="ch_chart_def_ind" type="string" length="1"/>
<property name="ch_ins_coverage" type="string" length="1"/>
<property name="ch_mother_ind" type="string" length="1"/>
<property name="ch_stop_bill_ind" type="string" length="1"/>
<property name="ch_grp_spec_calc_ind" type="string" length="1"/>
<property name="ch_sm_cr_wo_ind" type="string" length="1"/>
<property name="ch_bob_drg_ovr_flag" type="string" length="1"/>
<property name="ch_billing_scan_flag" type="string" length="1"/>
<property name="ch_acct_cov_by_ctrct_ind" type="string" length="1"/>
<property name="ch_pss_ind" type="string" length="1"/>
<property name="ch_signatures_obtained" type="string" length="1"/>
<property name="ch_manual_fin_chg_ind" type="string" length="1"/>
<property name="ch_stop_stmt_cd" type="string" length="1"/>
<property name="ch_stmt_prcs_ind" type="string" length="1"/>
<property name="ch_loa_ind" type="string" length="1"/>
<property name="ch_autopsy_cd" type="string" length="1"/>
<property name="ch_coroner_cd" type="string" length="1"/>
<property name="ch_no_pub" type="string" length="1"/>
<property name="ch_valuables" type="string" length="1"/>
<property name="ch_pt_adm_age_ind" type="string" length="1"/>
<property name="ch_smoke_cd" type="string" length="1"/>
<property name="ch_pt_moa_cd" type="string" length="1"/>
<property name="ch_adm_prio" type="string" length="1"/>
<property name="ch_exp_loc" type="string" length="1"/>
<property name="ch_sur_exp_time_cd" type="string" length="1"/>
<property name="ch_pac_cd" type="string" length="1"/>
<property name="ch_exp_time_ind" type="string" length="1"/>
<property name="ch_room_pref_cd" type="string" length="1"/>
<property name="ch_loc_type" type="string" length="1"/>
<property name="ch_patient_cond" type="string" length="1"/>
<property name="ch_isolation_cd" type="string" length="1"/>
<property name="ch_loc_change_rsn" type="string" length="1"/>
<property name="ch_adm_cancel_cd" type="string" length="1"/>
<property name="ch_prior_adm_ind" type="string" length="1"/>
<property name="ch_adm_911" type="string" length="1"/>
<property name="ch_accident_flag" type="string" length="1"/>
<property name="ch_dis_plan_done" type="string" length="1"/>
<property name="ch_cd_blue" type="string" length="1"/>
<property name="ch_readm_ind" type="string" length="1"/>
<property name="ch_baby_health_ind" type="string" length="1"/>
<property name="ch_er_trmt_status" type="string" length="1"/>
<property name="ch_cert_status" type="string" length="1"/>
<property name="ch_outlier_cd" type="string" length="1"/>
<property name="ch_drg_error_ind" type="string" length="1"/>
<property name="ch_ur_app_ind" type="string" length="1"/>
<property name="ch_coll_agcy" type="string" length="1"/>
<property name="ch_fc" type="string" length="2"/>
<property name="ch_epi_ms" type="string" length="2"/>
<property name="ch_grn_rel_cd" type="string" length="2"/>
<property name="ch_adm_src" type="string" length="2"/>
<property name="ch_pt_emp_status" type="string" length="2"/>
<property name="ch_dis_plan_type" type="string" length="2"/>
<property name="ch_drg_period" type="string" length="2"/>
<property name="ch_apgar_score1" type="string" length="2"/>
<property name="ch_apgar_score2" type="string" length="2"/>
<property name="ch_user_tx1" type="string" length="1"/>
<property name="ch_user_tx2" type="string" length="1"/>
<property name="ch_user_tx3" type="string" length="1"/>
<property name="ch_user_tx4" type="string" length="1"/>
<property name="ch_user_tx5" type="string" length="1"/>
<property name="ch_user_tx6" type="string" length="1"/>
<property name="ch_user_tx7" type="string" length="1"/>
<property name="ch_user_tx8" type="string" length="1"/>
<property name="ch_user_tx9" type="string" length="1"/>
<property name="ch_user_tx10" type="string" length="1"/>
<property name="ch_user_tx11" type="string" length="1"/>
<property name="ch_user_tx12" type="string" length="1"/>
<property name="ch_user_tx13" type="string" length="1"/>
<property name="ch_user_tx14" type="string" length="1"/>
<property name="ch_user_tx15" type="string" length="1"/>
<property name="ch_user_tx16" type="string" length="1"/>
<property name="ch_user_tx17" type="string" length="1"/>
<property name="ch_user_tx18" type="string" length="1"/>
<property name="ch_user_tx19" type="string" length="1"/>
<property name="ch_user_tx20" type="string" length="1"/>
<property name="ch_user_tx21" type="string" length="2"/>
<property name="ch_user_tx22" type="string" length="2"/>
<property name="ch_user_tx23" type="string" length="2"/>
<property name="ch_user_tx24" type="string" length="2"/>
<property name="ch_user_tx25" type="string" length="2"/>
<property name="ch_user_tx26" type="string" length="2"/>
<property name="ch_user_tx27" type="string" length="2"/>
<property name="ch_user_tx28" type="string" length="2"/>
<property name="ch_user_tx29" type="string" length="2"/>
<property name="ch_user_tx30" type="string" length="2"/>
<property name="ch_user_tx31" type="string" length="2"/>
<property name="ch_user_tx32" type="string" length="2"/>
<property name="ch_user_tx33" type="string" length="2"/>
<property name="ch_user_tx34" type="string" length="2"/>
<property name="ch_user_tx35" type="string" length="2"/>
<property name="ch_user_tx36" type="string" length="2"/>
<property name="ch_user_tx37" type="string" length="2"/>
<property name="ch_user_tx38" type="string" length="2"/>
<property name="ch_user_tx39" type="string" length="2"/>
<property name="ch_user_tx40" type="string" length="2"/>
<property name="ch_trx_user_tx1" type="string" length="12"/>
<property name="ch_trx_user_tx2" type="string" length="32"/>
<property name="ch_user_tx41" type="string" length="4"/>
<property name="ch_user_tx42" type="string" length="4"/>
<property name="ch_user_tx43" type="string" length="4"/>
<property name="ch_user_tx44" type="string" length="4"/>
<property name="ch_user_tx45" type="string" length="8"/>
<property name="ch_user_tx46" type="string" length="8"/>
<property name="ch_user_tx47" type="string" length="8"/>
<property name="ch_user_tx48" type="string" length="8"/>
<property name="ch_user_tx49" type="string" length="12"/>
<property name="ch_user_tx50" type="string" length="12"/>
<property name="ch_user_tx51" type="string" length="12"/>
<property name="ch_user_tx52" type="string" length="20"/>
<property name="ch_user_tx53" type="string" length="20"/>
<property name="ch_user_tx54" type="string" length="20"/>
<property name="ch_user_tx55" type="string" length="32"/>
<property name="ch_user_tx56" type="string" length="32"/>
<property name="ch_user_tx57" type="string" length="32"/>
<property name="ch_site_expansion1" type="string" length="200"/>
<property name="ch_co_expansion1" type="string" length="200"/>
<property name="ch_earliest_adm_dt" type="timestamp"/>
<property name="ch_grp_alc_days_payment" type="BigDUserType"/>
<property name="ch_grp_outlier_days" type="integer"/>
<property name="ch_bd_pay_since_stmt_amt" type="BigDUserType"/>
<property name="ch_level_of_care_cd" type="string" length="4"/>
<property name="ch_alc_ind" type="string" length="1"/>
<property name="ch_site_cd" type="string" length="4"/>
<property name="ch_clinic_cd" type="string" length="4"/>
<property name="ch_partner_pt_id" type="BigDUserType"/>
<property name="ch_foreign_drg" type="string" length="4"/>
<property name="ch_foreign_drg_rate" type="BigDUserType"/>
<property name="ch_rel_of_info_cd" type="string" length="1"/>
<property name="ch_col_maint_cd" type="string" length="2"/>
<property name="ch_col_status_cd" type="string" length="2"/>
<property name="ch_state_submission_flag" type="string" length="1"/>
<property name="ch_dev_fill4" type="string" length="3"/>
<property name="ch_state_add_dt" type="timestamp"/>
<property name="ch_state_chg_dt" type="timestamp"/>
<property name="ch_state_del_dt" type="timestamp"/>
<property name="ch_accident_st_pr" type="string" length="2"/>
<property name="ch_accident_country" type="string" length="2"/>
<property name="ch_new_x12_rel_cds" type="string" length="1"/>
<property name="ch_dev_expansion1" type="string" length="97"/>
<property name="ch_dev_future_fields" type="string" length="68"/>
<property name="ch_modf1_lvl1" type="string" length="2"/>
<property name="ch_modf2_lvl1" type="string" length="2"/>
<property name="ch_modf1_lvl2" type="string" length="2"/>
<property name="ch_modf2_lvl2" type="string" length="2"/>
<property name="ch_modf_lvl3" type="string" length="2"/>
<property name="ch_modf_lvl3_fill1" type="string" length="6"/>
<property name="ch_apc_processed" type="string" length="1"/>
<property name="ch_tot_apc_reimb_amt" type="BigDUserType"/>
<property name="ch_dev_expansion2" type="string" length="107"/>
<property name="ch_dev_fill3" type="string" length="7"/>
<property name="ch_end_rec" type="string" length="1"/>
</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>

<class entity-name="rdb_patient_group_list" table="RDB.demo1.patient_group_list">
<id name="id" type="string">
<generator class="uuid"/>
</id>
<property name="user_number" type="integer"/>
<property name="ch_pno_key" type="BigDUserType"/>
</class>
</hibernate-mapping>

configuration file:
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
<!-- Settings for a remote CONNX database. -->
<property name="dialect">org.hibernate.dialect.ConnxDialect</property>
<property name="connection.driver_class">com.Connx.jdbc.TCJdbc.TCJdbcDriver</property>
<property name="connection.url">jdbc:connx:DD=SAPPHIRE;Gateway=windev01</property>
<property name="connection.username">username</property>
<property name="connection.password">password</property>

<!-- Use the C3P0 connection pool.-->
<property name="c3p0.min_size">3</property>
<property name="c3p0.max_size">5</property>
<!--property name="c3p0.timeout">5000</property-->
<property name="c3p0.timeout">0</property>
<!--property name="hibernate.c3p0.max_statements">100</property-->
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>
<property name="hibernate.c3p0.validate">false</property>


<!-- DISABLE THE SECOND LEVEL CACHE -->
<!-- Use EHCache but not the query cache. -->
<!--property name="cache.provider_class">org.hibernate.cache.EmptyCacheProvider</property-->
<property name="hibernate.cache.use_second_level_cache">false</property>
<!--property name="cache.use_query_cache">false</property-->
<!--property name="cache.use_minimal_puts">false</property-->


<!-- USE THIS WHEN WE CONNX SUPPORTS SCROLLABLE RESULT SETS -->
<!--property name="hibernate.jdbc.use_scrollable_resultset">false</property-->

<property name="show_sql">false</property>
<property name="default_entity_mode">dynamic-map</property>
<property name="hibernate.connection.autocommit">true</property>
<property name="hibernate.jdbc.batch_size">5</property>
<property name="hibernate.default_batch_fetch_size">8</property>

<!-- Sapphire mapping files. -->
<mapping resource="rms_pdraccmas_1.hbm.xml"/>
<mapping resource="rdb_patient_group_list_1.hbm.xml"/>
</session-factory>

</hibernate-configuration>

Am I correct in my guess that 1 sec for straight JDBC does _not_ include creation of all the maps?
I mean that if we just execute query and get _initial_ resultset (batch size), it may take N second, but it is a different story if we try to iterate through it and create all the maps to represent rows. And the loop may cause fetching more data from server.

Please provide more information about your test.

It might be interesting if you tried your query against iBatis and compare performance, by default iBatis returns exactly that: list of maps, so the test should not be difficult.

Did you already torture and kill the person who created that table?
I wouldn't like to be in your place if you need an 58th transaction (or whatever tx stands for)...

The striaght JDBC includes access of all rows and columns in the result set - thought we do not put them into maps, we do access all the fields. Our application does not require the maps, so in the full-blown application we have to pull this data back out of the maps anyway (but this happens relatively quickly).

Our Hibernate code executes 40 queries (2 to get primary keys, then 38 more to actually get the data - verified via p6spy) whereas the JDBC code only executes a single query which returns all the data, so my assumption is that the overheard with these queries is the main cause of the difference.

I suggest you to complement Hibernate with iBatis for the case.

Yes, extra queries may slow down the process, although it is not all that simple:
http://sandbox.sourcelabs.com/kosta/hib ... _test.html

i have no idea why ibatis would help in this siutation ?

learn about efficient querying, fetching strategies and learn how to utilize native queries to minimize the performance impact.

iBatis allows mapping result of arbitrary SQL to and arbitrary object, which sometimes is quite handy. For example the same Hibernate persistent object (or list of them) might be populated by iBatis with using different queries, tables, connections, or combinations etc.

It does not mean that iBatis is better for everything, but in certain scenarios iBatis is more convenient to use. In my opinion this is the case.

and <sql-query> is not enough here ? The user can just return null for those columns he does not want values.

(and no, i am not trying to bash ibatis or something - im just pointing out that hibernate actually can do similar stuff and people tend to forget that)

<sql-query> is close, but not quite.
From atop of my head:
a. for scalars it requires declaring them;
b. does not support conditional queries ie. Depending on condition there might be different ORDER by or WHERE clauses used;
c. does not map to Map by default;
d. does not support reusing of named mappings (column to class-property maps )

a. true, but is that a big problem? It also gives you the opportunity to return composite types/scalar and its more performant/safe.

b. true, might be usefull - also in context of HQL queries; but very few actually asked for it and it complexify the cachability of the query.

c. true, but that definitly might change in the near future for 3.1 development..

d. false, in 3.1 it can directly reuse the mappings from the <class> and we have resultset-ref

in any case, neither of the above have to do with the current threads issue as far as i can see.

>a. true, but is that a big problem? It also gives you the opportunity to return composite types/scalar and its more performant/safe.

Not that big, simply inconvenient for use. It is philosophical, but I like when system does most smart or simply make-sense things by default and allows to override its behavior if necessary. In this case H forces user to help Hibernate to be more performant/safe.

>b. true, might be usefull - also in context of HQL queries; but very few actually asked for it and it complexify the cachability of the query.

Your question was: why iBatis is more convenient? There are answers and they do not mean that iBatis is better than H, or that H should try to implement all the features of framework X. I think that H should not do that because of differences in philosophies.

>c. true, but that definitly might change in the near future for 3.1 development.
Up to you guys.

>d. false, in 3.1 it can directly reuse the mappings from the <class> and we have resultset-ref

Still true because 3.1 is not production yet.

>in any case, neither of the above have to do with the current threads issue as far as i can see.

Easiness of turning a result set into list of maps and performance of the process are relevant, as for the rest: I thought that you are interested to know why H is not considered to be the best framework for all occasions.

Hi,

Just curious where do you get the dialect package org.hibernate.Dialect.ConnxDialect from ?

I'm looking for it but to no avail.