Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 9 of 9 total
Thread NOWHEREOPTIMIZE
Wed, Apr 14 2010 7:55 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

I'd like to suggest a new switch NOWHEREOPTIMIZE which if in an sql statement would stop EDBManager from optimising the where clause and force it to execute left to right (or right to left as long as we know).

The reason for the suggestion is that I have a number of UDFs some of which I know will take a lot longer than a standard command and the standard command can be used to reduce the workload and considerably speed up the query eg

select * from contacts where _latestdate > date'2010-01-01' and textsearch(_latestcv,' sigma process improvement,'y')

textsearch if a UDF which 1) extracts a cv (generally in Word format) from a BLOB column, 2) uses Mike Skolnik's software to extract the text, 3) tests (like CONTAINS) for the existence of the words.

So if I want to have a look at the recent stuff first there is possibility that ElevateDB will execute textsearch first rather than limit it to just the newer stuff.

Roy Lambert

Wed, Apr 14 2010 10:05 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< So if I want to have a look at the recent stuff first there is
possibility that ElevateDB will execute textsearch first rather than limit
it to just the newer stuff. >>

ElevateDB will never execute a UDF condition before a standard condition
that can use an index.  Do you have a specific case where ElevateDB is
choosing a sub-optimal query execution plan under these circumstances ?

This query:

select * from contacts where _latestdate > date'2010-01-01' and
textsearch(_latestcv,' sigma process improvement,'y')

will always result in the _latestdate condition being executed first.

--
Tim Young
Elevate Software
www.elevatesoft.com
Wed, Apr 14 2010 10:25 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim

>ElevateDB will never execute a UDF condition before a standard condition
>that can use an index. Do you have a specific case where ElevateDB is
>choosing a sub-optimal query execution plan under these circumstances ?
>
>This query:
>
>select * from contacts where _latestdate > date'2010-01-01' and
>textsearch(_latestcv,' sigma process improvement,'y')
>
>will always result in the _latestdate condition being executed first.

What if its not indexed?

When you were helping me debug my UDF this was one exchange
-------------------------------------------------------------------------------------------------------------------------
Roy: <<As an aside ElevateDB seems to be processing the external function before
the _LatestCV IS NOT NULL because I was getting a load of FldStr as empty
string. Fortunately after the NOT _ID IN otherwise I would have disappeared
down a black hole. >>

Tim: You cannot, under any circumstances, rely on conditions being executed in
any particular order.  That's what the query optimizer is for - *it*
determines the best way to execute the WHERE clause conditions.
-------------------------------------------------------------------------------------------------------------------------

Roy Lambert
Wed, Apr 14 2010 8:26 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< What if its not indexed? >>

It will execute it left-to-right.

<< When you were helping me debug my UDF this was one exchange >>

Yes, and what I said is still good advice (you placed the UDF *before* the
other conditions in that exchange, not *after*).  UDFs are always executed
as a row scan, and will never take precedence over other conditions, so
*with this particular query*, you will be okay.

--
Tim Young
Elevate Software
www.elevatesoft.com
Thu, Apr 15 2010 2:27 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


><< What if its not indexed? >>
>
>It will execute it left-to-right.
>
><< When you were helping me debug my UDF this was one exchange >>
>
>Yes, and what I said is still good advice (you placed the UDF *before* the
>other conditions in that exchange, not *after*). UDFs are always executed
>as a row scan, and will never take precedence over other conditions, so
>*with this particular query*, you will be okay.

Sorry to be a pain but this is the query quoted when I posted about execution order

SELECT * FROM Contacts
WHERE
_LatestCV IS NOT NULL
AND
NOT _ID IN (1000083,1000274,1000317)
AND
CVSearch(_LatestCVType, CAST(_LatestCV AS CLOB), 'CONTAINS', 'French', 'Y')

Stepping through my code in the debugger the sequence seemed to be

1.    NOT _ID IN (1000083,1000274,1000317)
2.    CVSearch(_LatestCVType, CAST(_LatestCV AS CLOB), 'CONTAINS', 'French', 'Y')
3.    _LatestCV IS NOT NULL

Based on the fact that I only received the records with the correct _ID (_ID is indexed) and I also received records where an empty string was passed as _LatestCV to CVSearch (_LatestCV is an unindexed BLOB). I do not store empty strings its either data or NULL.

Roy Lambert
Thu, Apr 15 2010 8:47 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< Sorry to be a pain but this is the query quoted when I posted about
execution order >>

Sorry, I didn't remember the circumstances correctly.  However, your issue
was not one of execution order, but rather the source data being passed into
the external function.  IOW, the execution order didn't change anything
about the problem you were having.

--
Tim Young
Elevate Software
www.elevatesoft.com
Thu, Apr 15 2010 9:37 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim

>Sorry, I didn't remember the circumstances correctly.

I only have to remember one, as I've said before I'm impressed by your ability to remember stuff off these ngs etc.

>However, your issue
>was not one of execution order, but rather the source data being passed into
>the external function. IOW, the execution order didn't change anything
>about the problem you were having.

The actual problem was Mike Skolnik's component and Word/WordPerfect .doc compatibility Smiley

If your statement above is correct how does an empty string make it through _LatestCV IS NOT NULL? If I can get over this then knowing its essentially indexed stuff first then left to right means I can structure queries how I want, but I can't understand how I was getting the empty string passed through unless _LatestCV IS NOT NULL was either ignored or being carried out after CVSearch.

Roy Lambert
Thu, Apr 15 2010 9:55 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< If your statement above is correct how does an empty string make it
through _LatestCV IS NOT NULL? >>

I don't know, exactly, since I didn't have a chance to dig through all that
stuff you sent me and set everything back up again to test it.  I'll see if
I can get a chance in the next couple of days to examine it again.

--
Tim Young
Elevate Software
www.elevatesoft.com
Thu, Apr 15 2010 1:50 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim

>I don't know, exactly, since I didn't have a chance to dig through all that
>stuff you sent me and set everything back up again to test it. I'll see if
>I can get a chance in the next couple of days to examine it again.

Thanks, and when you have I'm sure that as is usually the case you'll prove to be right Smiley Right now I'm having to reformat my server. I had the weird event again. Ran my optimisation routine, went off shopping, when I got back rebooted server - unable to access database. This time recovering last Sunday's Acronis backup didn't work Frown

Roy Lambert
Image