Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 2 of 2 total
Thread WHEER clause execution order
Fri, Jul 26 2013 11:50 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Just testing I found something where I don't think ElevateDB is being clever enough

Consider the following

select * from emails
where
textsearch(_message, 'operations')
and
_message like '%Divisional Manager & Head of Operations%'

it takes c20 seconds which is about the same time as without the textsearch. If I use _Message CONTAINS 'operations' (there is a FTI) it reduces to c2 seconds.

If I leave off the like test then textsearch takes c.8 seconds with CONTAINS being c.04 seconds.

With textsearch and like the execution plan reads

The following filter condition was applied to the emails table:

"_message" LIKE '%Divisional Manager & Head of Operations%' AND
textsearch("_message", 'operations') = TRUE

I presume ElevateDB thinks the pure sql is going to be faster than the external function call so what I'd like is a flag similar to NOJOINOPTIMISE say IKNOWBESTANDLEAVEITALONEORELSE to get the engine to execute the statements in the order entered.

Roy Lambert

ps in emails _message can range from a few words to a small novel.
Mon, Jul 29 2013 12:20 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< I presume ElevateDB thinks the pure sql is going to be faster than the
external function call so what I'd like is a flag similar to NOJOINOPTIMISE
say IKNOWBESTANDLEAVEITALONEORELSE to get the engine to execute the
statements in the order entered. >>

Noted.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com
Image