AW: AW: User defined SQL function

Hi Vern,

No there is no easy switch to move from CQE to SQE.
Visual Explain, checking which Query Engine is used, creating the adviced
indexes or trying to remove the reasons why CQE is used, would be the way to
go.

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 Vern Hamberg
Gesendet: Monday, May 14, 2012 22:10
An: Midrange Systems Technical Discussion
Betreff: Re: AW: User defined SQL function


Hi Birgitta

Good to see you, again, at COMMON.

You say to move the execution to SQE - as if we can just change a
switch. As I recall, the reasons for using CQE are becoming fewer, but
it's not always obvious what to do.

I assume you would have them use Visual Explain to find out just why CQE
was used - then they could try to change how things work. Am I missing
an easy, obvious approach to this?

Regards
Vern

On 5/14/2012 1:52 PM, BirgittaHauser wrote:

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~