Jerry,

<< I've burned a CD with the the database and Delphi Win32 program I created
tonight to test in this mode rather than the Internet/IntraWeb environment.
The results are the same: very slow.

The CD will be in the mail first thing Tuesday morning. >>

Thanks very much.  I'll let you know what I find out when I receive the
database.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

It is coded as a filter in the app. I used the SQL in dbsys per your
suggestion. And both localmode and remote versions exhibit the same problem.

It isn't the Web. Last evening I coded the test in a Win32 app (as opposed
to an ISAPI DLL) with everything local and encountered the same problem.

I keep having the feeling that it's some really stupid thing I'm doing or
forgetting to do. Sent a CD off to Tim this morning with the database, so
we'll see.

Jerry

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:E2B17894-8793-46F1-B1D8-B6208CCFAB63@news.elevatesoft.com...
| Jerry
|
|
| Are you sure your problem isn't simply latency across the web?
|
| Have you tried using DBSys on the remote data rather than using your app.
If it returns fast its got to be you
|
| Nice that you're learning sql, but why not simply put it into the filter?

I was playing around with this a bit today and I don't understand why there
is a significant difference between a filter and an SQL query. Using dbsys I
set the following filter: TEXTSEARCH('cable television' IN PLAINTEXT)

The result takes 1 min. 45 seconds and yields 76 records out of some 14,000.

Then I ran this SQL query:

SELECT Bill, Ver, FileRef, Plaintext INTO Hits FROM BText WHERE
TEXTSEARCH('CABLE TELEVISION' IN PLAINTEXT)

I got the same 76 records within 3 seconds.

What is this telling us?

Jerry


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:9EF99B66-C798-418D-86ED-FB9CAC65CB17@news.elevatesoft.com...
| Jerry,
| Thanks very much.  I'll let you know what I find out when I receive the
| database.

Jerry,

<< Then I ran this SQL query:

SELECT Bill, Ver, FileRef, Plaintext INTO Hits FROM BText WHERE
TEXTSEARCH('CABLE TELEVISION' IN PLAINTEXT)

I got the same 76 records within 3 seconds.

What is this telling us? >>

More than likely the issue is the ordering issue that I mentioned before.
The above query only has to dump the 76 records into a static result set.
What is the timing for this query (RequestLive:=True):

SELECT Bill, Ver, FileRef, Plaintext FROM BText
WHERE TEXTSEARCH('CABLE TELEVISION' IN PLAINTEXT)

?

However, I still have a hard time believing that it is taking 1+ minutes to
navigate 14,000+ index keys.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

See below.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:6EC47AA6-8112-4E94-95BA-B54787A04BC6@news.elevatesoft.com...
| Jerry,
|  <<What is this telling us? >>
|
| More than likely the issue is the ordering issue that I mentioned before.
| The above query only has to dump the 76 records into a static result set.
| What is the timing for this query (RequestLive:=True):
|
| SELECT Bill, Ver, FileRef, Plaintext FROM BText
| WHERE TEXTSEARCH('CABLE TELEVISION' IN PLAINTEXT)

This runs in just under 4 seconds with RequestLive:=False, very acceptable.
However, with RequestLive:=True the search takes 1 min. 7 sec., and the
display after search another 2 min. 11 sec.

I don't do SQL but have read the description of RequestLive. However, I'm
not sure I understand it. It seems the same set is being returned.

|
| ?
|
| However, I still have a hard time believing that it is taking 1+ minutes
to
| navigate 14,000+ index keys.

So do I. That's why I flagged this. It almost looks like something is
forcing brute-force searching.

Jerry

Jerry

>I don't do SQL but have read the description of RequestLive. However, I'm
>not sure I understand it. It seems the same set is being returned.

It is the same result set but if Live then its the same as setting a filter on the table, the result set can be edited and changes posted are posted to the underlying table. If canned then a temporary table is created with the results in it. That table can be edited BUT changes posted are only in the temporary table and not back in the underlying table.

Roy Lambert

Roy,

Thanks for that explanation. Tim should add it to his Help description for
the property. It also answered another question I pondered yesterday, namely
if I deleted a record from the query set would it also be deleted from the
underlying table. Turns out, if I understand you correctly, the table record
gets deleted only if Live.

Appreciate it.

It seems that the slowness I am encountering has to do with the "filter"
setting. Tim should get the CD today, so I'll be interested in what he finds
out.

Jerry

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:5531D72C-2B33-4CAD-81DE-B7D324D0F353@news.elevatesoft.com...
| Jerry
|
| >I don't do SQL but have read the description of RequestLive. However, I'm
| >not sure I understand it. It seems the same set is being returned.
|
| It is the same result set but if Live then its the same as setting a
filter on the table, the result set can be edited and changes posted are
posted to the underlying table. If canned then a temporary table is created
with the results in it. That table can be edited BUT changes posted are only
in the temporary table and not back in the underlying table.
|
| Roy Lambert
|

Jerry,

<< This runs in just under 4 seconds with RequestLive:=False, very
acceptable. However, with RequestLive:=True the search takes 1 min. 7 sec.,
and the display after search another 2 min. 11 sec. >>

It's most definitely the filter/ordering issue then.

Try this, place a TDBISAMEngine on your main form or data module and change
the TableFilterIndexThreshhold property to a higher number than the default
of 1.  Try 2, 3, etc. until you see some improvement.  This property
controls when DBISAM switches to a special type of processing to handle the
special case of very few rows being selected from quite a few rows.  With
this special processing, DBISAM is able to get around the issue of
reconciling the internal filter representation using physical record bitmaps
with the active index order.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

It's not making any difference. I started at 2 and went to 16 with
RequestLive=True and the times are only 1 sec. off the original slow times.
I did these tests with the Query component rather than the table.

Out of curiosity, when should the DBISAMEngine component be set to Active? I
think it defaults to False when dropped on a form. I have set mine to Active
in the designer (except when changing property values).

I think you may have to examine the project and database I sent you to see
what is happening.

Jerry

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:EA7631B7-C715-4232-92E5-66CAB49568A6@news.elevatesoft.com...
| Jerry,
|
| << This runs in just under 4 seconds with RequestLive:=False, very
| acceptable. However, with RequestLive:=True the search takes 1 min. 7
sec.,
| and the display after search another 2 min. 11 sec. >>
|
| It's most definitely the filter/ordering issue then.
|
| Try this, place a TDBISAMEngine on your main form or data module and
change
| the TableFilterIndexThreshhold property to a higher number than the
default
| of 1.  Try 2, 3, etc. until you see some improvement.  This property
| controls when DBISAM switches to a special type of processing to handle
the
| special case of very few rows being selected from quite a few rows.  With
| this special processing, DBISAM is able to get around the issue of
| reconciling the internal filter representation using physical record
bitmaps
| with the active index order.

Jerry,

<< I think you may have to examine the project and database I sent you to
see what is happening. >>

I got your database and yes, never mind what I said about the filter index
threshhold setting, it's not the ordering issue.  The issue is that the BLOB
values are very large in the table, and DBISAM has to re-evaluate the
TextSearch() condition on the BLOB values as it does repositioning to ensure
that any changes to the table are reflected properly.  It's all rather
complicated, but it has to do with the changes in 4.x several minor releases
ago where we reduced the time that read locks are held for brute-force
filters and queries on a table.

The only workaround at this point is to use a canned query result set, which
won't have this issue.  ElevateDB doesn't have this issue because we did not
repeat the same design that the later 4.x issues had with respect to the
read locking, and I'm definitely considering putting DBISAM back the way it
was at some point also.

--
Tim Young
Elevate Software
www.elevatesoft.com