Hi,
Which query engine is executing the query? CQE or SQE?
If it is executed by the CQE try to move it to the SQE?
You may also use in RUN SQL Scripts the integrated debugger to find out how
often the function is called.
IIRC CQE executes the function for each record, while SQE will exectue the
function only for the selected rows.
Does the function always return the same value if it is called with the same
parameters? If so is DETERMINISTIC specified (NOT DETERMINISTIC is the
Default).
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Franco Lombardo
Gesendet: Monday, May 14, 2012 19:03
An: midrange-l@xxxxxxxxxxxx
Betreff: User defined SQL function
Hi all,
I have an SQL problem. I have a query like this
select
myfunction(field1) from mytable where field2 like 'A%'
The table mytable
is really big, the selection like 'A%' extracts quickly
only a few records,
while myFunction is a bit slow
A "good" execution plan should extract records
and apply myFuntion to the
results, but the system applies myFunction to the
whole table, and then it
does the filter...it takes forever....
I
tried to rewrite my query this way:
with x as (select field1 from mytable
where field2 like 'A%')
select myfunction(field1) from x
but it's
still very slow!
Can you help
me?
Bye
Franco
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.francolombardo.net
Scala,
Java, As400.....
http://twitter.com/f_lombardo
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
midrange.com
