Hi,

Is it possible to optimize SQL below? Because it is prety slow on table with 7000 records, takes about 7 seconds.

SELECT * FROM Topic Where TEXTSEARCH('12775' IN MyKeywords) or TEXTSEARCH('17737' IN MyKeywords) or TEXTSEARCH('10777' IN MyKeywords) or TEXTSEARCH('11362' IN MyKeywords) or TEXTSEARCH('27171' IN MyKeywords) or TEXTSEARCH
('14465' IN MyKeywords) or TEXTSEARCH('15749' IN MyKeywords) or TEXTSEARCH('23058' IN MyKeywords) or TEXTSEARCH('14013' IN MyKeywords) or TEXTSEARCH('30873' IN MyKeywords) or TEXTSEARCH('18772' IN MyKeywords) or TEXTSEARCH
('13157' IN MyKeywords) or TEXTSEARCH('10304' IN MyKeywords) or TEXTSEARCH('10277' IN MyKeywords) or TEXTSEARCH('15432' IN MyKeywords) or TEXTSEARCH('11662' IN MyKeywords) or TEXTSEARCH('17338' IN MyKeywords) or TEXTSEARCH
('30606' IN MyKeywords) or TEXTSEARCH('15229' IN MyKeywords)

Regards,
Tomas

Tomas,

<< Is it possible to optimize SQL below? Because it is prety slow on table
with 7000 records, takes about 7 seconds. >>

Could you post the query plan for the query ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Plan is below, strange thing is that it shows execution time as 1,484 seconds, but it takes much longer than two seconds.



================================================================================
SQL statement (Executed with 4.22 Build 6)
================================================================================

SELECT * FROM Topic Where TEXTSEARCH('17977' IN CategoriesID) or
TEXTSEARCH('28239' IN CategoriesID) or TEXTSEARCH('10995' IN CategoriesID) or
TEXTSEARCH('14132' IN CategoriesID) or TEXTSEARCH('22223' IN CategoriesID) or
TEXTSEARCH('19196' IN CategoriesID) or TEXTSEARCH('12812' IN CategoriesID) or
TEXTSEARCH('15763' IN CategoriesID) or TEXTSEARCH('15962' IN CategoriesID) or
....<SKIP>


Tables Involved
---------------

Topic (Topic) table opened shared, has 1268 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

WHERE Clause Execution
----------------------

NOTE: Optimized TEXTSEARCH() conditions do not use statistics and will show
zero (0) for any I/O costs

The expression:

TEXTSEARCH('17977',CategoriesID) = TRUE or TEXTSEARCH('28239',CategoriesID) =
TRUE or TEXTSEARCH('10995',CategoriesID) = TRUE or TEXTSEARCH('14132',
CategoriesID) = TRUE or TEXTSEARCH('22223',CategoriesID) = TRUE or
TEXTSEARCH('19196',CategoriesID) = TRUE or ....<SKIP>

is OPTIMIZED, covers 0 rows or index keys, costs 0 bytes, and will be applied
to the Topic table (Topic) before any joins

================================================================================
>>>>> 1268 rows affected in 1,484 seconds
================================================================================

Tom


Since its canned my guess would be that a large chunk of the time is involved in creating and populating the temporary table. Try setting RequestLive to True.


Roy Lambert

The difference is minimal between canned and live queries - Seven seconds on
Canned and Six on Live.

Tom,

<< Plan is below, strange thing is that it shows execution time as 1,484
seconds, but it takes much longer than two seconds. >>

In that case, the rest of the time is being spent elsewhere in your code.
Do you have any AfterOpen events or anything similar defined for the
TDBISAMQuery that is executing the query ?

--
Tim Young
Elevate Software
www.elevatesoft.com

I have tested this code in DBISAM utility.

I have tested this code in DBISAM utility and it shows 1,484
seconds but takes much longer about 6-7 seconds.

Tom,

<< I have tested this code in DBISAM utility and it shows 1,484 seconds but
takes much longer about 6-7 seconds. >>

And just to verify - it is generating a canned result set (not live) in
these cases ?

--
Tim Young
Elevate Software
www.elevatesoft.com

I tried both canned and live, there is no big difference in execution time.