Re: AW: User defined SQL function

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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