Hi,

I have two tables TOPIC and DOCUMENT with ONE to MANY relationship.
I use query below to search in two fields of TOPIC table and in one field in DOCUMENT table.
Query executiuon is VERY slow, takes about 60 seconds. Any syggestion how to improve that?
I have posted database with the same subject in binary.

SELECT DISTINCT ID FROM Topic JOIN Document ON Topic.ID=Document.TopicID WHERE
(
(
(TEXTSEARCH('delphi' IN document.SearchText)) OR
(TEXTSEARCH('delphi' IN topic.Title)) OR
(TEXTSEARCH('delphi' IN topic.Keywords))
)
)


Regards,
Tomas

Tomas,

<< I have two tables TOPIC and DOCUMENT with ONE to MANY relationship. I use
query below to search in two fields of TOPIC table and in one field in
DOCUMENT table.  Query executiuon is VERY slow, takes about 60 seconds. Any
syggestion how to improve that? >>

Any time you use an OR condition between multiple tables, DBISAM has to
perform the joins first and then evaluate the the OR condition afterwards.
This is not the case with an AND condition.

You can see this with the query plan:

Scan Expression Execution
-------------------------

The expression:

TEXTSEARCH('delphi',document.SearchText) = TRUE OR TEXTSEARCH('delphi',
topic.Title) = TRUE OR TEXTSEARCH('delphi',topic.Keywords) = TRUE

is UN-OPTIMIZED and will be applied to each candidate row in the result set
as
the result set is generated

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim, can you suggest any way how to change query?

Another question, does ElevateDB will execute faster SQL above?
One month ago we have purchased DBISAM 4.x and faced with performance issues which does not
allow to release our software. So we have two choices, change database structure
or wait for ElevateDB. When ElevateDB will be released?

No disrespect to Tim, but it cannot be sensible to switch to a new
database and ship immediately. Using ElevateDB is sensible if you are a
few months from shipping I'm sure, but a little patience must surely be
sensible. Me, I'd just do the work on the DBISAM structure.

/Matthew Jones/

Tom,

<< Tim, can you suggest any way how to change query?  >>

The only thing that will improve the speed is getting rid of the OR between
the two separate table conditions.  If you think about it, you will see why
DBISAM has to evaluate it the way that it does.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tom,

<< Another question, does ElevateDB will execute faster SQL above? >>

Only from a general standpoint.  IOW, it can't do anything special to the
actual optimization process in order to execute things differently.  That's
just the nature of having an OR condition between two separate table
conditions.

<< One month ago we have purchased DBISAM 4.x and faced with performance
issues which does not allow to release our software. So we have two choices,
change database structure or wait for ElevateDB. When ElevateDB will be
released? >>

Well, the three things I have seen are:

1) You trying to use approximately 100+ TextSearch() calls in a single WHERE
clause to do basically accomplish what should have been split out into a
separate detail table.  IOW, using TextSearch() to parse string fields that
are concatenated together to avoid using a separate table is a bad idea.
TextSearch() is designed to be used for full-text searching, not parsing.

2) You're encrypting all of your tables, which is going to make the I/O
slower than necessary.  This may be a criteria for your deployment, but
you're going to have to recognize that this is going to hurt performance.

3) You might be able to get rid of the join above that is causing the issue
with the OR condition if you redesigned the structure of the tables.  I've
looked at the tables, but I'm not sure why exactly the topics table is split
out separately from what appears to be the actual document text since it
appears that the topics are references to the document text.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:126EDF85-AF3C-41A0-A557-7F538C5A740D@news.elevatesoft.com...
> Tom,
>
> << Tim, can you suggest any way how to change query?  >>
>
> The only thing that will improve the speed is getting rid of the OR
> between the two separate table conditions.  If you think about it, you
> will see why DBISAM has to evaluate it the way that it does.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
Tim, I'm hope I do not talk any b..sh.t but is also the distinct and
therefor the join a issue?

The

TEXTSEARCH('delphi' IN topic.Title)) OR
(TEXTSEARCH('delphi' IN topic.Keywords))

can be done without any join and after that only for those documenta that
have a topic that is not includes in the result above the textsearch on
SearchText needs to be executed.

(TEXTSEARCH('delphi' IN document.SearchText))

However as soon as a document is found al other documents with the same
topic can be skipped, no idea however how to do this in DBISAM SQL

"Frans van Daalen" <Account@is.invalid> wrote in message
news:938892E2-9E6F-480C-B8E2-88680C0B12B2@news.elevatesoft.com...
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
> news:126EDF85-AF3C-41A0-A557-7F538C5A740D@news.elevatesoft.com...
>> Tom,
>>
>> << Tim, can you suggest any way how to change query?  >>
>>
>> The only thing that will improve the speed is getting rid of the OR
>> between the two separate table conditions.  If you think about it, you
>> will see why DBISAM has to evaluate it the way that it does.
>>
>> --
>> Tim Young
>> Elevate Software
>> www.elevatesoft.com
> Tim, I'm hope I do not talk any b..sh.t but is also the distinct and
> therefor the join a issue?
>
> The
>
> TEXTSEARCH('delphi' IN topic.Title)) OR
> (TEXTSEARCH('delphi' IN topic.Keywords))
>
> can be done without any join and after that only for those documenta that
> have a topic that is not includes in the result above the textsearch on
> SearchText needs to be executed.
>
> (TEXTSEARCH('delphi' IN document.SearchText))
>
> However as soon as a document is found al other documents with the same
> topic can be skipped, no idea however how to do this in DBISAM SQL
>

Something like

SELECT DISTINCT ID FROM Topic WHERE
(TEXTSEARCH('delphi' IN document.SearchText)) OR
(TEXTSEARCH('delphi' IN topic.Title))
UNION
SELECT Document.TopicID FROM Document WHERE (TEXTSEARCH('delphi' IN
document.SearchText))

maybe ?

Frans,

<< Something like

SELECT DISTINCT ID FROM Topic WHERE
(TEXTSEARCH('delphi' IN document.SearchText)) OR
(TEXTSEARCH('delphi' IN topic.Title))
UNION
SELECT Document.TopicID FROM Document WHERE (TEXTSEARCH('delphi' IN
document.SearchText)) >>

Yes, that should do what he wants with better performance.  The DISTINCT is
not really a performance issue, but the OR across multiple tables is.
Therefore, by getting rid of the join you are eliminating the issue.

--
Tim Young
Elevate Software
www.elevatesoft.com