I have this query:
select * from mbs where Description_Record50 contains '*lap*'

Description_Record50 is a CLOB with a text index:
create text index Description_Record50X on MBS (Description_Record50) INDEXED WORD LENGTH 15

the MBS table has 8800 rows.

1. Inside ElevateDB Manager the select takes 0.5 to 0.9 secs.

2. When done as a TEDBQuery.open it takes 8-9 secs.

Any clues on speeding up 1 and particularly 2.

thanks.

Bryn


Can you post the execution plans for both cases.

Roy Lambert Roy Lambert [Team Elevate]

1. As an elevateDB sql query:

SELECT ALL

..snip..

FROM "mbs"
WHERE "Description_Record50" CONTAINS '*lap*'

Source Tables
-------------

mbs: 5760 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the mbs table:

"Description_Record50" CONTAINS '*lap*' [Index scan (MBS.Description_Record50X):
84116 keys, 1265664 bytes estimated cost]


Result set I/O statistics
-------------------------

Total rows visited: 152

Row buffer manager

Max buffer size: 329728 Buffer size: 195776

Hits: 152   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

Index page buffer manager

Max buffer size: 65536 Buffer size: 4096

Hits: 152   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

BLOB block buffer manager

Max buffer size: 32768 Buffer size: 79872

Hits: 156   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

================================================================================
152 row(s) returned in 0.422 secs
================================================================================

2. From a TEDBQuery:

SELECT ALL

..snip..

FROM "mbs"
WHERE "Description_Record50" CONTAINS '*lap*'

Source Tables
-------------

mbs: 5760 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the mbs table:

"Description_Record50" CONTAINS '*lap*' [Index scan (MBS.Description_Record50X):
84116 keys, 1265664 bytes estimated cost]


Result set I/O statistics
-------------------------

Total rows visited: 152

Row buffer manager

Max buffer size: 329728 Buffer size: 195776

Hits: 152   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

Index page buffer manager

Max buffer size: 65536 Buffer size: 4096

Hits: 152   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

BLOB block buffer manager

Max buffer size: 32768 Buffer size: 79872

Hits: 156   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

================================================================================
152 row(s) returned in 8.937 secs
================================================================================

Bryn


They look pretty identical apart from the time so my guess would be external events / controls. In EDBManager there's going to be very limited interaction with any controls (only a fairly simple grid)  whilst in your app there might (obviously I don't know) be lots of interaction.

Try wrapping DisableControls / EnableControls around the query and/or setting any instances of it in a datasource to nil and see what happens.

The only other possibility is that you're addressing a different database in each case or going via a different route (eg internet vs high speed LAN)

A separate point CONTAINS '*lap*' should be CONTAINS 'lap*' unless Tim has sneaked in leading wildcards

Roy Lambert [Team Elevate]

Bryn,

Those execution plans are identical, but you snipped off the top that shows
the version of EDB in use.  Are you sure you're using the same version of
EDB in both cases ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< A separate point CONTAINS '*lap*' should be CONTAINS 'lap*' unless Tim
has sneaked in leading wildcards >>

I most certainly did.  I'll make sure that the docs are updated, because
I just looked, and they are not.

--
Tim Young
Elevate Software
www.elevatesoft.com

I only have one version of EDB as I have only just started using it: ie, it is the same
version in both cases - the latest.

The TEDBQuery.open is not connected to any other controls - it is created just before .open.

Tim

><< A separate point CONTAINS '*lap*' should be CONTAINS 'lap*' unless Tim
>has sneaked in leading wildcards >>
>
>I most certainly did. I'll make sure that the docs are updated, because
>I just looked, and they are not.

1) great 2) oh bugger I have to update my textsearch code now

Are you planning to build regex support into CONTAINS cos if so I'll start looking at that rather than just adjust for leading wildcard.

Roy Lambert

Roy,

<< Are you planning to build regex support into CONTAINS cos if so I'll
start looking at that rather than just adjust for leading wildcard. >>

Not any time soon, no.  But, sometimes these things change.

--
Tim Young
Elevate Software
www.elevatesoft.com

Bryn,

<< I only have one version of EDB as I have only just started using it: ie,
it is the same version in both cases - the latest.

The TEDBQuery.open is not connected to any other controls - it is created
just before .open. >>

Can you distill this down to a sample project that replicates the slowdown ?
If so, please email it to me at timyoung@elevatesoft.com

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com