What do you think

[Bill]

This SQL query works, but it is extremely slow, Does anyone have any ideas
on a more effective way to code this that might speed it up?



SELECT e.social_security_num,
e.last_name,
e.first_name,
c.emp_stat_code,
f.earnings_type_code,
c.clt_emp_stat_eff_dte,
f.end_dte,
h.effective_dte,
f.amount,
h.rate


FROM bfg_admin.db_emp_status_hist c,
bfg_admin.com_partic_info_stat e,
bfg_admin.db_ann_earning_hist f,
bfg_admin.db_pay_rate_hist h

WHERE e.db_employee_id = c.emp_employee_id
AND e.db_employee_id = f.emp_employee_id
AND h.emp_employee_id = c.emp_employee_id
AND f.earnings_type_code = '415PEN'

AND c.EMP_STAT_EFF_DTE = (SELECT MAX(c.EMP_STAT_EFF_DTE)
FROM bfg_admin.db_emp_status_hist c
WHERE c.emp_employee_id = e.db_employee_id
AND e.db_employee_id = f.emp_employee_id
AND h.emp_employee_id = f.emp_employee_id)

AND H.EFFECTIVE_DTE = (SELECT MAX(H.EFFECTIVE_DTE)
FROM bfg_admin.db_pay_rate_hist h
WHERE h.emp_employee_id = f.emp_employee_id
AND c.emp_employee_id = e.db_employee_id
AND e.db_employee_id = f.emp_employee_id)

AND f.end_dte = (SELECT MAX(f.end_dte)
FROM bfg_admin.db_ann_earning_hist f
WHERE f.emp_employee_id = e.db_employee_id
AND h.emp_employee_id = c.emp_employee_id
AND c.emp_employee_id = e.db_employee_id)

[DaveSatz]

You may want to re-code using ANSI SQL, i.e. JOIN's instead of tables in the
FROM clause. The optimizer may create a different plan once you do that.

Are the employee_id columns and f.earnings_type_code indexed ?

--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group only)
-----------------------------------------------------------------

"Bill" <Bill@yahoo.com> wrote in message news:3a70466f@news.devx.com...
>
> This SQL query works, but it is extremely slow, Does anyone have any ideas
> on a more effective way to code this that might speed it up?
>
>
>
> SELECT e.social_security_num,
> e.last_name,
> e.first_name,
> c.emp_stat_code,
> f.earnings_type_code,
> c.clt_emp_stat_eff_dte,
> f.end_dte,
> h.effective_dte,
> f.amount,
> h.rate
>
>
> FROM bfg_admin.db_emp_status_hist c,
> bfg_admin.com_partic_info_stat e,
> bfg_admin.db_ann_earning_hist f,
> bfg_admin.db_pay_rate_hist h
>
> WHERE e.db_employee_id = c.emp_employee_id
> AND e.db_employee_id = f.emp_employee_id
> AND h.emp_employee_id = c.emp_employee_id
> AND f.earnings_type_code = '415PEN'
>
> AND c.EMP_STAT_EFF_DTE = (SELECT MAX(c.EMP_STAT_EFF_DTE)
> FROM bfg_admin.db_emp_status_hist c
> WHERE c.emp_employee_id = e.db_employee_id
> AND e.db_employee_id = f.emp_employee_id
> AND h.emp_employee_id = f.emp_employee_id)
>
> AND H.EFFECTIVE_DTE = (SELECT MAX(H.EFFECTIVE_DTE)
> FROM bfg_admin.db_pay_rate_hist h
> WHERE h.emp_employee_id = f.emp_employee_id
> AND c.emp_employee_id = e.db_employee_id
> AND e.db_employee_id = f.emp_employee_id)
>
> AND f.end_dte = (SELECT MAX(f.end_dte)
> FROM bfg_admin.db_ann_earning_hist f
> WHERE f.emp_employee_id = e.db_employee_id
> AND h.emp_employee_id = c.emp_employee_id
> AND c.emp_employee_id = e.db_employee_id)
>
>