Re: slow query

I'm trying to explain the bigger function a bit although it's only
called in 2% of the cases.

В Втр, 22.07.2003, в 19:07, Markus Bertheau пишет:

> CREATE FUNCTION iGetNumOfBookedRes(integer, timestamp, timestamp) RETURNS numeric AS '
> SELECT
> CASE WHEN (MAX(kumulierte) IS NULL) THEN 0 ELSE MAX(kumulierte) END
> FROM
> (SELECT
> (SELECT
> SUM(dynmenge)
> FROM
-- (1) start
> (SELECT
> -- Ressource wird ausgeliehen
> von, menge AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND von > $2 AND von < $3
> UNION SELECT
> -- Ressource wird zurückgegeben
> bis AS von, -menge AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND bis > $2 AND bis < $3
> UNION SELECT
> -- Anfangsstand
> $2, SUM(auftragsressourcen.menge) AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND von <= $2 and bis > $2
> ) AS bel1
-- (1) end
> WHERE bel1.von <= bel2.von
> ) AS kumulierte
> FROM
-- (1) start
> (SELECT
> -- Ressource wird ausgeliehen
> von, menge AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND von > $2 AND von < $3
> UNION SELECT
> -- Ressource wird zurückgegeben
> bis AS von, -menge AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND bis > $2 AND bis < $3
> UNION SELECT
> -- Anfangsstand
> $2, SUM(auftragsressourcen.menge) AS dynmenge
> FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
> WHERE ressource_id = $1 AND von <= $2 and bis > $2
> ) AS bel2
-- (1) end
> ) AS belegte
> ' LANGUAGE SQL;
>

The query (1) with some values gives:

von | dynmenge
---------------------+----------
2000-01-01 08:00:00 | 4
2000-06-06 00:00:00 | 6
2000-07-07 00:00:00 | -6
2000-07-16 08:00:00 | 7
2000-08-16 16:00:00 | -7
2000-09-01 00:00:00 | 8
2000-11-01 00:00:00 | -8
2001-01-01 00:00:00 | -4

What we actually do with the WHERE bel1.von <= bel2.von trick is
calculate the sum over dynmenge over the first i rows where i \in (1..n)
A positive dynmenge signifies that x items of a ressource are given
away, a negative value that it is returned. This gives us

von | dynmenge | given_away
---------------------+----------+------------
2000-01-01 08:00:00 | 4 | 4
2000-06-06 00:00:00 | 6 | 10
2000-07-07 00:00:00 | -6 | 4
2000-07-16 08:00:00 | 7 | 11
2000-08-16 16:00:00 | -7 | 4
2000-09-01 00:00:00 | 8 | 12
2000-11-01 00:00:00 | -8 | 4
2001-01-01 00:00:00 | -4 | 0

num_of_ressources - max(given_away) gives us the number of ressources
available in the given time interval.

I hope this makes the function more clear.

--
Markus Bertheau
Cenes Data GmbH