Slow Index

Luann_Dias

Any idea as to why an index might be slow? I have a table with about 120,000 records in it and whenever I trying running a query against an indexed column its taking a long time. I've tried removing and readding the BTree index but the issue persists. Another issue I noticed that might be related is that whenever I try using Database_Services("Search_Index") against the same column, it's returning a different set of values when running the same command through TCL. The TCL results were correct compared with the Database_Services results (and yes I enabled the update index parameter) however the query takes much longer.

BarryStevens

What is the full SELECT statement you used in TCL?

Luann_Dias

"SELECT TABLE_NAME WITH STATUS 'NEW'", STATUS column is indexed.

BarryStevens

OK, just humour me and change to:
SELECT TABLE_NAME WITH STATUS EQ 'NEW'

and TABLE_NAME is the actual name of the table?

PLUS:

Database_Services("Search_Index") - I assume this is a typo as the service name is SearchIndex

DonBakke

The SearchIndex service relies upon Btree.Extract which is one of the lowest level and fastest ways to search OI tables using Btree indexes. If that is not performing well then I suspect there is a problem with your index. TCL searches are higher level and will resolve to Btree.Extract queries and other methods as needed. Since you noted that TCL is taking awhile to run, it suggests to me that it is not using the index to search.

P.S. While not necessarily a problem, I recommend putting a comparison operator in your statement. I tend to not like implicit behavior since it is not always known to everyone reading your code. Also, AREV supported more implicit syntax than OI, so that could created compatibility problems.

Luann_Dias

I tried "SELECT DATANAV WITH STATUS EQ 'NEW'" as well and same issue. I did use SearchIndex, that was a typo on my end. TABLE_NAME is not the actual name of the table.

BarryStevens

How is STATUS defined in the dictionary for DATANAV ?

JaredBratu

Luann,

Just to clarify, I assume you were using SearchIndex and not "Search_Index". It's possible you have something custom named "Search_Index" in Database_Services but I'm assuming not.

The SearchIndex method uses btree.extract to query the indexes directly. When you do the TCL command, it may or may not use the indexes like btree.extract does.

Are you checking Error_Services after making the call to SearchIndex, if not, are any errors reported?

Is your search value a string or numerical value like 1234 or 0001234?

If needed, is it possible for you to remove all the indexes from the table and ensure the '!TABLENAME' table (where 'TABLENAME' is the name of your table with the index) is removed from the system prior to adding the index back onto the table?

Luann_Dias

Jared,

I ended up finding an article within the SRP Wiki regarding removing indexes manually and that fixed the problem. After removing all indexes, I ended up deleting the the two dictionary files "%PROTECT.SPEC%" and "%FIELDS%" and then removed the index table "!DATANAV". I restarted OI and added the BTREE index to the field I was having issues with and now the index seems to be working like a charm in the TCL and using Database_Services("SearchIndex").

The only remaining issue I'm trying to tackle is that when running the code:

• Database_Services("SearchIndex", "DATANAV", "STATUS", "NEW", TRUE$)
  

It is not working on a specific computer in our network, although the same line works fine on my system after the fix. Going to keep troubleshooting and get back to you guys, I'm assuming it has something to do with the environment of the other system.

Thank you guys for the help, the wiki link I was referring to is below.

https://wiki.srpcs.com/display/ProgrammingTechniques/Removing+Indexes+Manually

DonBakke

Luann - You might want to have the latest client setup installed on that machine giving you problems. The client setup includes .NET libraries used by indexing.

Luann_Dias

Don,

I forgot about that. Ended up running that on the system with the issue and now its working. Thanks a million!