Danny

>But doesn't it make more sense to find out what's wrong with DBISAM's query optimizer and fix it rather than design an elaborate workaround?

It does but sometimes it can take time to work out what the fix is and then implement a solution.

>These queries could get pretty complicated with many levels of ORs and ANDs.

Not really. Its a bit more complex than just putting OR or AND but its simply INSERT INTO for an OR and DELETE FROM for an AND. That's on the basis of what you've shown so far. If you allow the users to bracket expressions to alter how the ANDs and ORs interact then it becomes complex. It can still be done but it moves from trivial to non-trivial.

Out of interest I just thought I'd convert to ElevateDB and try

SELECT *
FROM InvMarc
WHERE
(Keywords CONTAINS 'DISNEY WORLD'  AND Contents LIKE '%DISNEY WORLD%')
OR
Keywords CONTAINS 'DISNEYLAND'

I tweaked the columns and index to be case-insensitive

It quotes 0 seconds which I don't quite believe but it is fast. If its possible and you're using full text indexing I'd certainly recommend a move to ElevateDB. With all the aggravation its given me in other areas the improvements in this feature alone (even with a the fact that it now only operates on indexed columns) will keep me with it rather than reverting to DBISAM. And to make sure this comment isn't misunderstood there are other features that I like, and are an improvement over DBISAM.

Roy Lambert [Team Elevate]

Roy,

I can try to implement a workaround but I want to make sure that this is posted and that someone (Tim?) takes a look at it. It might be a simple
bug fix in DBISAM.

Consider this user query:

holiday OR vacation AND paris OR london OR madrid -expensive

Using your workaround, I would have to figure out how to parse this into a bunch of queries -- not a trivial task (at least not for me!)

I would love to switch to ElevateDB. The problem is that we have about 8 major applications in this family of products, including a customized
DBISAM server with custom SQL functions, with an installed base of about 2,000. All applications have to work together with the same database.
We'll convert someday but it is going to be a huge undertaking and is going to shut down all development of any other enhancements in the
meantime. I'm not quite ready for that yet.

Thanks,
Danny

Danny

>I can try to implement a workaround but I want to make sure that this is posted and that someone (Tim?) takes a look at it. It might be a simple
>bug fix in DBISAM.
>
>Consider this user query:
>
>holiday OR vacation AND paris OR london OR madrid -expensive
>
>Using your workaround, I would have to figure out how to parse this into a bunch of queries -- not a trivial task (at least not for me!)

That's fairly trivial. Would they be allowed to put in something like

(holiday OR vacation AND paris) OR (london AND smog) OR madrid -expensive

and is the -expensive a not expensive or...


>I would love to switch to ElevateDB. The problem is that we have about 8 major applications in this family of products, including a customized
>DBISAM server with custom SQL functions, with an installed base of about 2,000. All applications have to work together with the same database.
>We'll convert someday but it is going to be a huge undertaking and is going to shut down all development of any other enhancements in the
>meantime. I'm not quite ready for that yet.

to use a phrase from the film "Stir Crazy" oooh shit

Roy Lambert [Team Elevate]

Danny

Being my normal nosey self is there a website I can see what your apps are?


Roy Lambert

Only if you have a fix for the query optimization thingie!

http://www.SurpassSoftware.com

Danny,

<< I can try to implement a workaround but I want to make sure that this is
posted and that someone (Tim?) takes a look at it. It might be a simple bug
fix in DBISAM. >>

Just to reiterate this - if you need immediate assistance you should always
use email or phone (and make sure that your support plan is paid up .
I'm checking the newsgroups, but not all of the time now.

I'm checking this out to see what the issue is.

--
Tim Young
Elevate Software
www.elevatesoft.com

Danny,

Okay, the issue is that the AND with the un-optimized condition is causing
the query optimizer to think that it would be more efficient to just scan
the entire table to satisfy the query.

I can send you a fix via email if you need something immediately.  Just let
me know.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

You have made my day! That would be fantastic!

Thanks,
Danny

Tim,

Thanks for pointing out that I should have sent an email for support on this issue. I always start by looking in the newsgroup for a quick answer
and I just got carried away in it!

Thanks also for sending me a fix for this even though I was not currently a paid support subscriber. That tickled my guilt bone so I immediately
purchased a support subscription. It's only fair and your support is definitely worth the price!

Danny

Danny,

<< You have made my day! That would be fantastic! >>

Okay, I've sent the corrected dbisamen.pas to you via email.  Just pop it
into the compiler search path and you should be all set.  I'm trying to wait
on doing another DBISAM build until Delphi 2009 is released.  At that time
I'll be doing a new DBISAM minor release with some improvements.

--
Tim Young
Elevate Software
www.elevatesoft.com