My query goes sth like this:
I have a table named "attendance", which records each shift and the
associated # of hours worked by every part-time staff each day.
The table's size is very large (over 1,000,000 rows) and keeps growing
every day.
Sample data for "attendance":
staff rdate shiftid hour
====================================
ABC 2006-05-01 1 2
ABC 2006-05-01 2 3
DEF 2006-05-01 1 3
My query is "Check how many hours worked by each staff per day during
'2006-05-01' AND '2006-05-02'.
Plain SQL
=================================
SELECT
staff,
rdate,
SUM(hour)
FROM
attendance
WHERE
rdate BETWEEN '2006-05-01' AND '2006-05-02'
GROUP BY
staff,
rdate
;
UDF Body
=========
CREATE FUNCTION query (csdate DATE,cedate DATE)
RETURNS TABLE
(
staff VARCHAR(20),
rdate DATE,
ttlhour INTEGER
)
SPECIFIC query
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT
staff,
rdate,
SUM(hour)
FROM
attendance
WHERE
rdate BETWEEN csdate AND cedate
GROUP BY
staff,
rdate
;
UDF sample invocation
=====================
select * from table(query(DATE('2006-05-01'),DATE('2006-05-02'))) as T1
Have I coded something wrong that causes such a long execution time
with UDF?
