›
Welcome to the SRP Forum! Please refer to the SRP Forum FAQ post if you have any questions regarding how the forum works.

btree.extract with multiple criteria using the AND operator

haroldvanceharoldvance
in OpenInsight
We are having an issue where btree.extract returns different results on different workstations when fed the same criteria with the AND (~) operator. The first criteria is field CUSTOMER_NUMBER equal to a customer number. The second criteria is field DATE with a range that starts one year ago and continues to two years in the future. Both dates are calculated on the fly and are relative to the current system date.

On one workstation I get zero hits. On another I get three hits. The OS does not seem to matter. Danh and I (the developers) are running the same OS, Win7 x64, and we get different results. I get three hits and he gets zero even though we are accessing the same data volume.

Has anyone seen this issue before?

Comments

  • DonBakkeDonBakke
    Hi Harold,

    I have a few questions and comments:

    I'm a little confused. The "~" operator performs a "BETWEEN" search The "#" operator performs a "NOT" search. Was that just a typo? It might help if you posted a sample syntax for a typical search.

    I'm also interested in knowing if the date values you are passing in are formatted as internal or external values.

    Finally, what is the value in your SYSENV\CFG_IDX_SETS record? Do you have more than one CFG_IDX_SETS records (i.e., perhaps one is configured for a specific user)?
  • haroldvanceharoldvance
    My bad. The "BETWEEN" operator is used for the date range. We are using the default operator for the two criteria, which I guess is an "AND".

    SEARCH.STRING = SEARCH.FIELD.NAME: @VM: ENTITY.ID : @FM
    SEARCH.STRING := "DATE": @VM: BETWEEN.START.DATE
    SEARCH.STRING := "~": BETWEEN.END.DATE :@FM
    SEARCH.FILE.NAME = "QUOTES"
    SEARCH.DICT = DICT.QUOTES
    CALL BTREE.EXTRACT (SEARCH.STRING,SEARCH.FILE.NAME,SEARCH.DICT,KEYS)

    In this case, SEARCH.FIELD.NAME contains "CUSTOMER_NUMBER" and ENTITY.ID contains "6667". Also, the dates are passed in ICONV format, as integers. I put a debug option in the program to display all of the values.

    See attached screen shot.

    I will look for the SYSENV\CFG_IDX_SETS record.

    We are also having issues with frozen locks on *INDEX records and on specific field name index records. The locks are placed by end user workstations, not by our indexing server. I was going to start a separate thread for that issue but maybe the two are related?
  • haroldvanceharoldvance
    The screen shot was for a search using a different table, but the code is identical as it was copied throughout that program.
  • DonBakkeDonBakke
    Harold,

    At this point I don't think the locks on *INDEX records are related.

    I have always thought that index searches should be done with Oconv formatted data, not Iconv formatted data. Can you see if this makes a difference?

    Also, the last time Danh and I worked on index problems, we found significant problems with the way the dictionary columns were maintained. Have you confirmed that your indexed columns all Key ID columns are properly configured?
  • haroldvanceharoldvance
    Thanks, Don. I will try the OCONV dates.

    What do I need to look for in the Key ID columns? Do they need to have a specific data type? Our keys tend to be variable length.
  • haroldvanceharoldvance
    On the date range criteria using the BETWEEN operator, I get the same results (zero hits) if the dates are submitted with D2/ format. I had the system display the contents of the search string variable in order to confirm it.
  • DonBakkeDonBakke
    Harold,

    Is D2/ the formatting specified in the dictionary definition?
  • haroldvanceharoldvance
    Here are the contents of CFG_IDX_SETS:

    <1> IDX_SETS2
    <2>
    <3>
    <4> If use of original IDX_SETS is desired, set field 2 to IDX_SETS1;
    <5> otherwise, leave blank to use most recent version

    I can't find IDX_SETS2 in SYSENV. Is this a table name?
  • haroldvanceharoldvance
    The OCONV format shows D2-, which is different.
  • haroldvanceharoldvance
    I changed the date format to D2- and got same results. I verified the search string contents prior to submission to betree.extract.
  • DonBakkeDonBakke
    What do I need to look for in the Key ID columns? Do they need to have a specific data type? Our keys tend to be variable length.
    Harold,

    Here are some basic rules to follow:
    1. Indexed columns should have a defined length that is at least 1 greater than the largest converted value stored in the data table.
    2. Key ID columns should also have a defined length that is at least 1 greater than the largest converted value stored in the data table.
    3. Look for old AREV columns like @ID. Make sure it has a length that is 1 greater than all of the Key ID parts.
    4. If you find any of the above items not being correct, then you will want to remove all indexes and add them back.
  • DonBakkeDonBakke
    I can't find IDX_SETS2 in SYSENV. Is this a table name?
    Harold,

    No, it is just a configuration setting. It means you are using the newer (and fixed) Indexing logic.
  • BarryStevensBarryStevens
    if using IDX_SETS2, then have the PCS that you are comparing results on, run the the same and latest ClientSetup.exe (I believe you should remove the existing .net components and idxsets first [check documentation as to what and how])
  • haroldvanceharoldvance
    Barry, thank you. We will give it a shot to see if we get any difference.
  • haroldvanceharoldvance
    Don, what is the best way to describe the length of a key field? Do I use the Justification Length field? Or do I set the data type? I usually put VARCHAR(10) in pretty much every data type unless I know it is Boolean in which case I use BOOLEAN. The @ID field in our QUOTES table doesn't even have a data type. We have three synonyms (real fields) plus @ID that point to the single part record key in this table. The three synonyms have VARCHAR(10), which is three characters short of the longest key.
  • BarryStevensBarryStevens
    I have always used the 'Justification Length field'
    I believe, the 'data type'ing is only relevant for external database connections.
  • haroldvanceharoldvance
    Barry, I just ran clientsetup.exe on the workstation where I was getting too few hits with btree.extract. This machine doesn't do much other than run Arev16, so I am pretty sure I had never run clientsetup on it before. There was no reason to do that. At any rate, after the install and a restart, I ran a test on the btree.extract on OI. The system returned the correct number of hits. This is great! (When the btree indexes don't work, the users can lose some basic functionality, so knowing what is causing this issue is a big deal here.) Again, thank you, Don and Barry!
  • BarryStevensBarryStevens
    Awesome!
  • DonBakkeDonBakke
    Harold,

    Sorry for being MIA. I had an appointment that I required me to be out of the office for a few hours. Thankfully, Barry stepped in and gave you some stellar advice.

    This goes to show you that you can take nothing for granted. The client setup possibility is something I bring up when I detect clues that it might be relevant (e.g., "new machine"). Plus, I was already familiar with past indexing issues with your database tables, so my thoughts went there as a first course of action.

    Nevertheless, I still encourage you to inspect your field definitions and make sure they are correct. Barry answered you correctly about setting the length. The datatype is irrelevant, although this does set a default length (which you can certainly change).
Sign In or Register to comment.