$100 paid to anyone who can rewrite this code to run fast: you can use temp tables, whatever. If it runs significantly better I'll send you the $100, so include your address with the rewritten code. If it works, I'll probably have more work for you..
G.

select distinct t.personnum, t.applydate,
round(t.wfctimeinseconds/3600,2) as amount,
substr(c.customdata,1,1) as shift,
(select count(*) from vp_alltotals
where personnum = t.personnum and
applydate = t.applydate and
wfctimeinseconds = t.wfctimeinseconds and
paycodename = 'Shift Differential') as NumShiftDiff,
(select count(*) from vp_alltotals where personnum = t.personnum and
applydate = t.applydate and
wfctimeinseconds = t.wfctimeinseconds and
paycodename in (select name from SF_QUAL_PAYCODES)) as NumPaycodes
from vp_alltotals t,
vp_personcustdata c
where
c.personnum = t.personnum and
c.customdatadefid = 4 and substr(c.customdata,1,1) in ('E','N') and
t.applydate >= t.prevpayperiodstart and
t.applydate <= t.currpayperiodend and
t.paycodename in (select name from SF_QUAL_PAYCODES) and
t.wfctimeinseconds <> 0 and
substr(t.wfclaborlevelname4,1,2) in (select * from SF_QUAL_JOBS)
order by t.personnum,t.applydate

What database and database version are you using?

Can you give us a rough idea of the data volumes for each table, each query, and each condition?

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

Hi Justin,
When I get into the office, will send more complete info. However, the db is Oracle 9i and the vp_* are views, not tables. The underlying base tables are on the order of 12,000 - 15,000 rows, and then entire db is probably just a few 100 Mb's or so. If you want the hefty code for the views, let me know, I'll post them or forward them to you.
G.

Here are the rowcounts.
SQL> select count(*) from vp_alltotals;

  COUNT(*)
----------
     33737

SQL> select count(*) from vp_personcustdata;

  COUNT(*)
----------
     95956

db version 9.2.0.4.0

Code for the views would be helpful...

If you could provide an export of the underlying tables and the code for the views/ indexes/ etc., that would be really cool. I hate trying to get query plans for complex SQL statements in forums like this-- it's a lot easier if I can see the data locally.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

Justin,
Recoded it myself! Your interest in the problem was appreciated. If I run into more bad code and don't have the time to do it myself, I might contact you in the future.
Gil