BTREE.EXTRACT returns with Flag=-1 (search failure) (OpenInsight 32-Bit)
At 10 SEP 2010 04:15:58PM Matt Hedges wrote:
Using OI 8.0.3 - We have a table with a three part key - BP, Contract Option, and Event Date. There is a BTREE index on each key part. There is a program to find records based on a BP and date range. The BTREE call goes like this:
Btree.Extract('BP':@vm:BP:@fm:'EVENT_DATE':@vm:]=:FromDate:@fm:'EVENT_DATE':@vm:'⇐:ToDate:@fm,table,dictvar,keys,option,flag)
The dates are in internal format. This extract sometimes does not return event records when they exist, and returns FLAG=-1. When we do the extract a second time with the same BP and event date, it returns successfully.
I would like to know how track down why the BTREE is failing. We have ideas of possibly changing the extract, but we can't identify WHY it's failing in the first place. Any help is much appreciated. Thanks.
At 10 SEP 2010 04:35PM [url=http://www.sprezzatura.com]The Sprezzatura Group[/url] wrote:
A between search is a LOT faster than a range search - try moving to this and see if it resolves the issue.
World leaders in all things RevSoft
At 10 SEP 2010 07:02PM Matt Hedges wrote:
We have thought about doing that. But we have yet to reproduce this error, even when running the same accounts through the process.
Is it possible that this could be caused by multiple OI sessions performing the BTREE extract at the same time, though with different variables?
At 10 SEP 2010 09:34PM [url=http://www.sprezzatura.com]The Sprezzatura Group[/url] wrote:
Where is the sort file location? Multiple OI sessions on the same workstationn?
World leaders in all things RevSoft
At 13 SEP 2010 08:12AM John Bouley wrote:
Matt,
Have you tried a set_status(0) before the btree? Perhaps there is an error that is pre-existing.
HTH,
John
At 13 SEP 2010 06:14PM Bob Carten wrote:
]]Is it possible that this could be caused by multiple OI sessions performing the BTREE extract at the same time, though with different variables?
I believe that in 8.03 it is possible for multiple sessions to interfere with one another.
Depending on the query, Btree.Extract may use a function called IDX_SETS. IDX_SETS uses temporary files to store intermediate results. It is possible for two sessions to generate the same temporary file name, which could lead to the behavior you are seeing. The algorithm for generating temporary file names uses @station in the file name, and puts the temporary files in the sort_file location. The likelihood of overlap is highest if the sessions are using the same @station, such as engineserver or terminal server deployments.
Recent versions of OI use GUIDs to generate temporary filenames, so name clashes should not occur. OpenInsight 9.2 offers a .Net version of IDX_sets which does not create those temporary files and is also fast even with large data sets, so there is less pressure have to restructure your queries.
Thus, to echo Sprezz, put the sort files in a user specific location and/or restructure the query so it does not use IDX_SETS.