Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread |
Messages 1 to 9 of 9 total |
NOWHEREOPTIMIZE |
Wed, Apr 14 2010 7:55 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent Link |
Roy Lambert NLH Associates 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 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 Roy Lambert |
This web page was last updated on Thursday, March 28, 2024 at 08:36 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |