Hi,

I tested DBISAM vs ElevateDb for TextSearch operation.

Please apply below steps:
1. Create a word generator using ids_wg.txt in Delphi 2009
2. Create a session and database and run below statament:
  CREATE WORD GENERATOR "MyWord"
  MODULE "MyWordGen"
  description 'RxMediaPharma Word Generator'
2. Create db using ids_db.sql
3. Run ids_insert with 10000 param
4. Run above statement:
select * from ids
where
(id in (select z.id from idg z where z.g collate trk CONTAINS '0206080301*'))

This query too slow (57 sec). DBISAM run this query less than 1 sec.

Have you any suggestion?

<<Yavuz Ozdemir wrote:

Hi,

I tested DBISAM vs ElevateDb for TextSearch operation.

Please apply below steps:
1. Create a word generator using ids_wg.txt in Delphi 2009
2. Create a session and database and run below statament:
  CREATE WORD GENERATOR "MyWord"
  MODULE "MyWordGen"
  description 'RxMediaPharma Word Generator'
2. Create db using ids_db.sql
3. Run ids_insert with 10000 param
4. Run above statement:
select * from ids
where
(id in (select z.id from idg z where z.g collate trk CONTAINS '0206080301*'))

This query too slow (57 sec). DBISAM run this query less than 1 sec.

Have you any suggestion?
>>

i try below sql:

select id from ids
intersect
select z.id from idg z where z.g collate trk CONTAINS '0206080301*'

Executin time : 0.9 sec

Yavuz


What happens if you try it without the collate?

Roy Lambert

Yavuz,

<< 4. Run above statement:
select * from ids
where
(id in (select z.id from idg z where z.g collate trk CONTAINS
'0206080301*')) >>

Are you using RequestSensitive=True or RequestSensitive=False when you
execute the query ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

RequestSensitive=False

test results:

'02*'  : 0.875 sec
'0206*' : 0453 sec

'020608*' : 4.61 sec
'02060803*' : 21.234 sec

Yavuz

Sorry,

The first command (with IN operator) and the second command (with INTERSECT) not identical.

I must use the first command. But its is too slow.

Yavuz

Tim,

please redesign idg table:
1. column order  :G, id
2. primary index :G, id

and run below statement:
select * from ids
where
(id in (select z.id from idg z where z.g CONTAINS '0206080301*'))

Elapsed  time is appr. 0.1 sec.

does  the elevatedb use only primary index for the statement?

Yavuz,

<< RequestSensitive=False >>

Okay, the issue is that the IN operator is a brute-force operator, i.e. it
results in the SELECT for the IN being searched once for every row in the
outer query.  This version of the query accomplishes the same thing much
faster:

select ids.* from ids
inner join idg z on ids.id=z.id
where z.g collate trk CONTAINS '0206080301*'

--
Tim Young
Elevate Software
www.elevatesoft.com