"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

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

<<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.>>

We have removed encryption from tables and speed a little improved.

<<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.>>

Because Topic has many Document, relationship is One to Many.
I also thought about adding new table for search and keep Title, Keywords and SearchText data there.
There are several disadvantages:

1. Database size will increase because two fields will be duplicated in two tables:
Title, Keywords in Topic table and in new search table. Remember relationship One to Many.

2. We will need to copy/update fields in search table when Title and Keywords fields will be changed in Topic table.
It will slow down CS app a little.

"Tom" <tomazaz@gmail.com> wrote in message
news:26402E1C-1B2C-4C62-9F21-913354A7BA9F@news.elevatesoft.com...
> <<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.>>
>
> We have removed encryption from tables and speed a little improved.
>
> <<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.>>
>
> Because Topic has many Document, relationship is One to Many.
> I also thought about adding new table for search and keep Title, Keywords
> and SearchText data there.
> There are several disadvantages:
>
Did you try the union, it removed the need for the join

Tom,

<< Because Topic has many Document, relationship is One to Many. >>

I understand that it is a one-to-many, but what I was looking for was more
information on *why* it is a one-to-many.  Does a document get published
under more than one title with the same body text, for example ?

I couldn't, visually at least, find any example in the tables that you sent
of a document ever having more than a 1-to-1 link to the topics table.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Tom,

<< Because Topic has many Document, relationship is One to Many. >>

<<I understand that it is a one-to-many, but what I was looking for was more
information on *why* it is a one-to-many.  Does a document get published
under more than one title with the same body text, for example ?>>

Every document records is unique also like topic records, one topic can has many document records.

<<I couldn't, visually at least, find any example in the tables that you sent
of a document ever having more than a 1-to-1 link to the topics table.>>

It is just conjunction, topic can has many document with different data in SearchText field.

Tom,

<< Every document records is unique also like topic records, one topic can
has many document records. >>

I understand that.  What I want to know is *why*.  IOW, what is the
requirement that dictates that a document has many related topic records ?
What information is in the topic records that couldn't just be put into the
document record itself ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Topic table is used to store Questions and Document table is used to store Answers/Solutions.
One question(Topic) can have several Answers/Solutions(Document). Questions and answers is test-indexed
and we need to search with OR condition in the both tables  - Title(Question) and TextSearch(Answer) fields.

Thank you Tim for help.

Regards,
Tomas

Tom,

<< Topic table is used to store Questions and Document table is used to
store Answers/Solutions.One question(Topic) can have several
Answers/Solutions(Document). Questions and answers is test-indexed and we
need to search with OR condition in the both tables  - Title(Question) and
TextSearch(Answer) fields. >>

Ahh, okay.  Thanks.  I'm going to have to think about this one some more....

--
Tim Young
Elevate Software
www.elevatesoft.com

Hello Tim,

Any news?

Regards
Tomas

Tom,

<< Any news? >>

Not really.  You may be stuck with the situation until I can figure out
something that we can do with DBISAM to try and optimize OR conditions
across two tables.

--
Tim Young
Elevate Software
www.elevatesoft.com