× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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





As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.