Can any one explain why the following query
select f(q) from
(
select * from times
where '2006-03-01 00:00:00'<=q and q<'2006-03-08 00:00:00'
order by q
) v;
never completes, but splitting up the time span into single days does work.
select f(q) from
(
select * from times
where '2006-03-01 00:00:00'<=q and q<'2006-03-02 00:00:00'
order by q
) v;
select f(q) from
(
select * from times
where '2006-03-02 00:00:00'<=q and q<'2006-03-03 00:00:00'
order by q
) v;
...
select f(q) from
(
select * from times
where '2006-03-07 00:00:00'<=q and q<'2006-03-08 00:00:00'
order by q
) v;
The stored procedure f(q) take a timestamp and does a select and a
calculation and then an update of a results table. The times table
containes only a 100 rows per day. It is also observed that the cpu
starts the query with 100% usage and then the slowly swings up and down
from 100% to 20% over the first half hour, and then by the following
morning the query is still running and the cpu usage is 3-5%. IO bound
i'm guessing as the hdd is in constant use at 5 to 15 MB per second usage.
In contrast the query that is split up into days has a 100% cpu usage
all the way through to its completion, which only takes twenty minutes
each. The computer is not being used for anything else, and is a dual
core Athlon 4400+ with 4GB of ram.
Thanks for any information you can give on this.
