Home » Server Options » Text & interMedia » like performance than CATSEARCH Performance incase of wild character
like performance than CATSEARCH Performance incase of wild character [message #621303] Tue, 12 August 2014 08:25 Go to next message
ramesh.sannapureddy
Messages: 5
Registered: June 2014
Location: Bangalore
Junior Member
Hello,

We are facing the performnace issue with catsearch when using the wild character. But the performance is better if we use like search. Please suggest me how to improve the performance of catsearch used along with wild character.

SELECT ECID, SHORTNAME AS SHORTNAME, LONGNAME AS LONGNAME
FROM SEARCHTABLE
WHERE CATSEARCH(SEARCH_TEXT,'
<query>
<textquery grammar="context">
<progression>
<seq>"%ECN" WITHIN SHORTNAME</seq>
</progression>
</textquery>
</query>',null)>0 ;

It took around 5 secs. But below like search took only 1 sec.
SELECT ECID, SHORTNAME AS SHORTNAME, LONGNAME AS LONGNAME FROM SEARCHTABLE where SHORTNAME like '%ECN';

Please let me know how to improve the performance with cat search in this scenario.

We also have other scenario with performance issue.
i.e. if the catsearch is used to search the more frequent words like names of companies as ltd or inc or plc etc.. It is also taking so much time but if the same query is executing second time it is taking very less time.
for eg: first time 10 seconds and second time onwards 100 milli seconds.
But same query performing very well in case of normal words which does not occur so frquently.
please let me know how to improve the performance of catsearch incase of most frequest words.

Thanks in advance for your help.

Ramesh
Re: like performance than CATSEARCH Performance incase of wild character [message #621351 is a reply to message #621303] Tue, 12 August 2014 13:36 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Use a bind variable for your search string.

If you are going to limit the rows, you may be able to use some hints, like first_rows, that return that number of rows more quickly.

You might get better performance from a context index and contains instead of ctxcat index and catsearch.

You might be able to pre-load some things into memory.

I can't picture the query that you posted even running. Can you post the statements to create the table and preferences and index?

[Updated on: Tue, 12 August 2014 13:52]

Report message to a moderator

Previous Topic: extract information from an unstructured email into tables
Next Topic: Oracle Text Search Performance issue with frequent words
Goto Forum:
  


Current Time: Tue Mar 19 03:35:24 CDT 2024