›
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 slow selecting fields with Null values

prattspetsprattspets
in OpenInsight
In my testing of using Btree.Extract to select records it seems that if I search for a field equal to "" it is substantially slower than searching for data that is not Null. Does Btree.Extract have trouble with grabbing indexed "" values?

Comments

  • DonBakkeDonBakke
    I don't recall this being a known issue, at least with current versions of OI (9.4.6->10.x). Are the number of rows with empty values significantly higher than the number of rows with data in the indexed column?
  • BarryStevensBarryStevens
    Do you have a very large .ov file on the ! index file?
    Maybe use the RTI's LH Statisics app to rebuild and aim for a better lk/ov ratio >1.0
    What version of OI are you on.?
  • prattspetsprattspets
    I tried to make a situation where I can compare apples to apples. I added to my dictionary a calculated BOOLEAN field called "NULL_DATE_FLAG" and had it return "1" if the DATE field was an empty value and return "0" if it had data. I indexed that field. When doing the Btree.Extract to search for DATE with value "" it would take over twice as long as searching for NULL_DATE_FLAG with value "1".

    @Barry - I'll look at those statistics on the file tomorrow and see if that is a factor.
  • DonBakkeDonBakke
    What is the total number of records in the table you are using? I did what you did and created a symbolic that would essentially return a value of 1 for half the keys and a value of an empty string for the other half. Searching for a '1' took 1ms to return 10K keys and searching for an empty string took 1ms to return 10K keys. The table has 20K rows.
  • prattspetsprattspets
    There is a total number of over 800K records in the table. The searches are returning about 250 records. Searching for '1' took just over 12 seconds and search for an empty string took over 30 seconds.

    The ! index LK file is ~65k and the OV file is ~136k. Is that out of balance enough to cause these kinds of problems?
    I am on OI 9.4
  • prattspetsprattspets
    Oddly enough, a search for ">= " a certain date will return about 700 records in 1s 449ms.
  • DonBakkeDonBakke
    While out of balance, I would not expect it to impact the index performance that much. Even your best matching time of 12 seconds seems way too long, much less over 30 seconds. I think something else is going on.
  • BarryStevensBarryStevens
    Re 'out of balance', my gut feel says give it a try
Sign In or Register to comment.