Posted by hiddenhippo on 08/07/07 10:20

Hi,

I've got a problem in that a stored procedure of mine always times out
within my application. Realistically there's no need to increase the
timeout because the individual sections of the SQL execute fast, yet
when I bring them together the execution time increases dramatically.

Firstly, here's the entire SQL statement

select
rate_id,price_id,date_from,date_to,daysofweek,product_id,time_from,duration,sell_price,min_price,sub_rate
from rate_sheet_dates rsd, product_prices pp
where rsd.date_id = pp.date_id
and date_from <= '2007-08-07' and date_to >= '2007-08-07'
and rsd.rate_id in
(select mo.rate_id
from timesheet_detail ts, main_order mo
where ts.order_id = mo.order_id
and ((ts.status & 2) <> 0)
and actual_datetime_from between '2007-08-07' and '2007-08-07'
and not exists (select * from invoice_detail where job_no =
ts.job_no)
union
select mo.rate_id
from timesheet_detail ts left outer join timesheet_group_clients tgc
on (ts.job_no = tgc.job_no), main_order mo
where tgc.order_id = mo.order_id
and ((ts.status & 2) <> 0)
and actual_datetime_from between '2007-08-07' and '2007-08-07'
and not exists (select * from invoice_detail where job_no =
ts.job_no))
order by rate_id,daysofweek,time_from

This is taking 12 seconds to execute.

If I take the unions;

(select mo.rate_id
from timesheet_detail ts, main_order mo
where ts.order_id = mo.order_id
and ((ts.status & 2) <> 0)
and actual_datetime_from between '2007-08-07' and '2007-08-07'
and not exists (select * from invoice_detail where job_no =
ts.job_no)
union
select mo.rate_id
from timesheet_detail ts left outer join timesheet_group_clients tgc
on (ts.job_no = tgc.job_no), main_order mo
where tgc.order_id = mo.order_id
and ((ts.status & 2) <> 0)
and actual_datetime_from between '2007-08-07' and '2007-08-07'
and not exists (select * from invoice_detail where job_no =
ts.job_no))

this will execute in 2seconds and returns 4 rows.

If I then take the first half of my SQL and remove my unions and
specify the 4 integer values manually e.g.

select
rate_id,price_id,date_from,date_to,daysofweek,product_id,time_from,duration,sell_price,min_price,sub_rate
from rate_sheet_dates rsd, product_prices pp
where rsd.date_id = pp.date_id
and date_from <= '2007-08-07' and date_to >= '2007-08-07'
and rsd.rate_id in (1,2,3,4)

then it'll execute in less than a second.

Could someone please enlighten me as to why the entire SQL as a whole
takes 12 seconds, yet the components of take a fraction of the time.
Is there a better way to re-write this?

thanks for your help.

[Back to original message]