Indexing Issue OI9.21 & OI9.3 (OpenInsight 32-bit) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>category:"OpenInsight 32-bit" author:"Jim Leong" author:"Dave Harmacek" author:"bshumsky" author:"Matthew Crozier" author:"Paulo Mendes" author:"Barry Stevens"}}

[[https://www.revelation.com/the-works|Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community]]


==== Indexing Issue OI9.21 & OI9.3  (OpenInsight 32-bit) ====
=== At 19 JAN 2012 01:46:14AM Jim Leong wrote: ===
 
 
<QUOTE>
I have a table consisting of 250,000 records.   2 of the indexes are Tracking_Flag and Customer_No.   There are about 500 records with tracking_Flag = 1 and over 17000 records of Customer_No = 914.  



When I Run the following  comands:

   Select TableName with Tracking_Flag = 1    - returns 500 records

   Select TableName with Customer_No = 914  - returns 135 records



But when I combine the commands:

    Select TableName with Tracking_Flag =1 and with Tracking_Flag = 914  - returns 110 records, which is incorrect



When I ran this in Arev32, I got the same results (110 hits).   In Arev3.12, it was fine (135 hits)



When I removed the indexes, the combined commands worked correctly (135 hits).



But when I rebuilt the indexes and ran the combine command it still did not work properly (110 hits)



When I did the same search with another customer_No it worked properly.   That Customer has only 4700 records in the table.










</QUOTE>
----

=== At 19 JAN 2012 08:28AM Dave Harmacek wrote:  ===

<QUOTE>Hi Jim,



When I get these types of strange results when it is specific to an indexed value I investigate for data rows whose keys contain Revelation delimiters.  This would offset the sets of keys that are joined.



Dave Harmacek

Harmacek Database Systems

</QUOTE>

----

=== At 19 JAN 2012 09:42AM bshumsky wrote:  ===

<QUOTE>
<QUOTE>I have a table consisting of 250,000 records.   2 of the indexes are Tracking_Flag and Customer_No.   There are about 500 records with tracking_Flag = 1 and over 17000 records of Customer_No = 914.  



When I Run the following  comands:

   Select TableName with Tracking_Flag = 1    - returns 500 records

   Select TableName with Customer_No = 914  - returns 135 records



But when I combine the commands:

    Select TableName with Tracking_Flag =1 and with Tracking_Flag = 914  - returns 110 records, which is incorrect



When I ran this in Arev32, I got the same results (110 hits).   In Arev3.12, it was fine (135 hits)



When I removed the indexes, the combined commands worked correctly (135 hits).



But when I rebuilt the indexes and ran the combine command it still did not work properly (110 hits)



When I did the same search with another customer_No it worked properly.   That Customer has only 4700 records in the table.

</QUOTE>



Hi, Jim.  Can you tell me if you have a record in SYSENV called CFG_IDX_SETS (or any variant, like CFG_IDX_SETS*<appid> or CFG_IDX_SETS*<appid>*<username>), and if so what this record contains?  If it does exist, and it's set to IDX_SETS1, can you change it to say IDX_SETS2 and retry your query?



Thanks,

- Bryan Shumsky

[url=http://www.revelation.com]Revelation Software, Inc.[/url]

</QUOTE>

----

=== At 19 JAN 2012 12:21PM Jim Leong wrote:  ===

<QUOTE>Hi Bryan



I made the changes and it it working correctly in OI/Arev32



Thank You Very Much??



Is there any case where you would use IDX_SETS1?

</QUOTE>

----

=== At 19 JAN 2012 12:33PM bshumsky wrote:  ===

<QUOTE>
<QUOTE>Hi Bryan



I made the changes and it it working correctly in OI/Arev32



Thank You Very Much??



Is there any case where you would use IDX_SETS1?</QUOTE>



Hi, Jim. IDX_SETS1 is the original routine that's been in OpenInsight since the beginning, while IDX_SETS2 is a relatively recent .NET based emulation of the functionality.  With small enough data sets, IDX_SETS1 seems a little faster, but we've found that with big data sets, sometimes IDX_SETS1 seems to get "confused" on modern versions of Windows.



IMHO, IDX_SETS1 should just be kept around because there may be some (unforeseen) circumstance where it behaves differently (and more correctly) than IDX_SETS2 - but to be honest, I haven't encountered such a case myself yet, and I always use IDX_SETS2. I would even go so far as to suggest that everyone on 9.2.1 and above should switch to IDX_SETS2 if they're now using IDX_SETS1, but that decision is of course something each developer and site should verify for themselves.



- Bryan Shumsky

[url=http://www.revelation.com]Revelation Software, Inc.[/url]

</QUOTE>

----

=== At 19 JUN 2012 11:18PM Matthew Crozier wrote:  ===

<QUOTE>First of all, I was having trouble switching between IDX_SETS1 and IDX_SETS2 by changing CFG_IDX_SETS - it seemed that IDX_SETS2 was being used regardless of how CFG_IDX_SETS was set.   This was resolved by running idxSets_setup.exe directly to install IdxSets v1.3.  (It appears that the ClientSetup.exe program will register IdxSets version 1.2 - not version 1.3 ?)

Then, to revert IDX_SETS to IDX_SETS1,  CFG_IDX_SETS< 2> needs to be set to 'IDX_SETS1' (as described in the CFG_IDX_SETS record itself).  Otherwise in all other cases, IDX_SETS2 seems to be used (even if setting field 1 to 'IDX_SETS1').



In previous versions of OI (ie IDX_SETS1), setting the second field of the Options parameter (Option<2>) in Btree.Extract() invokes a mode where all results can be returned by repeated calls to Btree.Extract().  In this mode, if the first Btree.Extract() returns Flag > 0, then Option<3> contains an IDX_SETS handle and further results are obtained by subsequent Btree.Extract() calls, until the Flag parameter returns 0 (or less).



In OI9.3.1 using IDX_SETS2, setting Option< 2> does not seem to be affective.  Option<3> does not get set and Flag returns 0 - implying, falsely, that all results have been returned.  For example in my case, it only returns 847,648 out of 1,001,577 hits on a table of 1,072,439 records.

 

So it seems IDX_SETS1 is more reliable in this case than IDX_SETS2.  I've tried this on both XP and Win7.



But all this seems contrary to this thread, and the [url=http://www.revelation.com/knowledge.nsf/90cb98ba69f5b6b5852566f500653992/2e18b6386e4d13a88525798b0069833c?OpenDocument]Configuring OpenInsight 9.3 to use IDX_SETS2[/url] knowledge article !?



Cheers, M@

[url=http://www.vernonsystems.com][img]http://www.vernonsystems.com/images/logo_main_ani.gif[/img][/url]

</QUOTE>

----

=== At 20 JUN 2012 12:16PM bshumsky wrote:  ===

<QUOTE>
<QUOTE>First of all, I was having trouble switching between IDX_SETS1 and IDX_SETS2 by changing CFG_IDX_SETS - it seemed that IDX_SETS2 was being used regardless of how CFG_IDX_SETS was set.   This was resolved by running idxSets_setup.exe directly to install IdxSets v1.3.  (It appears that the ClientSetup.exe program will register IdxSets version 1.2 - not version 1.3 ?)

Then, to revert IDX_SETS to IDX_SETS1,  CFG_IDX_SETS< 2> needs to be set to 'IDX_SETS1' (as described in the CFG_IDX_SETS record itself).  Otherwise in all other cases, IDX_SETS2 seems to be used (even if setting field 1 to 'IDX_SETS1').



In previous versions of OI (ie IDX_SETS1), setting the second field of the Options parameter (Option<2>) in Btree.Extract() invokes a mode where all results can be returned by repeated calls to Btree.Extract().  In this mode, if the first Btree.Extract() returns Flag > 0, then Option<3> contains an IDX_SETS handle and further results are obtained by subsequent Btree.Extract() calls, until the Flag parameter returns 0 (or less).



In OI9.3.1 using IDX_SETS2, setting Option< 2> does not seem to be affective.  Option<3> does not get set and Flag returns 0 - implying, falsely, that all results have been returned.  For example in my case, it only returns 847,648 out of 1,001,577 hits on a table of 1,072,439 records.

 

So it seems IDX_SETS1 is more reliable in this case than IDX_SETS2.  I've tried this on both XP and Win7.



But all this seems contrary to this thread, and the [url=http://www.revelation.com/knowledge.nsf/90cb98ba69f5b6b5852566f500653992/2e18b6386e4d13a88525798b0069833c?OpenDocument]Configuring OpenInsight 9.3 to use IDX_SETS2[/url] knowledge article !?



Cheers, M@

[url=http://www.vernonsystems.com][img]http://www.vernonsystems.com/images/logo_main_ani.gif[/img][/url]</QUOTE>



Hi, Matthew.



Sorry you had trouble configuring IDX_SETS1 versus IDX_SETS2; as you said, the configuration record does expect the value to be in field TWO instead of field one.  This was done intentionally, so that sites couldn't accidentally choose IDX_SETS1 (which we believe to be more error-prone) - it takes a very conscious effort to make that switch.



But this was a difference in 9.3 from the earlier versions, I believe, so perhaps that's where the confusion came in?  Maybe in your 9.21 system, it was configured differently?



Regarding the client setup, the installer file for idx_sets2 isn't actually named in a version-specific way, so I'm not certain how your manually running the idxsets_setup.msi would have made a difference (unless the problem is running both an older version, from 9.21, and a newer version, from 9.3, on the same PC?)



Finally, regarding the behavior when the "second field of the options parameter (Option<2>) in Btree.Extract()" is set - the new IDX_SETS2 is indeed designed to _not_ "chunk" the results into 64k pieces, as option<2> used to trigger, but the fact that you're not getting all your results may mean that there could be an issue on large result sets (possibly related to memory usage, or string space, or something similar).  We'll try to recreate your results, so we can see what's going on.



Thanks!



- Bryan Shumsky

[url=http://www.revelation.com]Revelation Software, Inc.[/url]

</QUOTE>

----

=== At 20 JUN 2012 05:40PM Matthew Crozier wrote:  ===

<QUOTE>
<QUOTE>Regarding the client setup, the installer file for idx_sets2 isn't actually named in a version-specific way, so I'm not certain how your manually running the idxsets_setup.msi would have made a difference (unless the problem is running both an older version, from 9.21, and a newer version, from 9.3, on the same PC?)</QUOTE>



Interesting!  In my [i]C:\Program Files\Revelation Software[/i] folder, I have both [i]OpenInsights idxSets v1.2[/i] and [i]OpenInsights idxSets v1.3[/i] folders.  The Registry (InprocServer32\CodeBase) was just pointing to the v1.2 folder, until I ran idxSets_setup.exe. This OI system was 9.2.1 until I upgraded it to 9.3.1, but I don't have two distinct OI systems of different versions.  Maybe the registry just wasn't updated during clientsetup?




<QUOTE>Finally, regarding the behavior when the "second field of the options parameter (Option<2>) in Btree.Extract()" is set - the new IDX_SETS2 is indeed designed to _not_ "chunk" the results into 64k pieces, as option<2> used to trigger, but the fact that you're not getting all your results may mean that there could be an issue on large result sets (possibly related to memory usage, or string space, or something similar). We'll try to recreate your results, so we can see what's going on.</QUOTE>



It seems at some stage after OI went 32bit, the max 'chunk' was increased from 64Kb to 100 times that.  Let me know if you'd like my test file.  I've got a Bitmap index on it as well, (and finding some curious things with that too...)



Cheers, M@

[url=http://www.vernonsystems.com][img]http://www.vernonsystems.com/images/logo_main_ani.gif[/img][/url]

</QUOTE>

----

=== At 22 FEB 2013 06:27AM Paulo Mendes wrote:  ===

<QUOTE>Bryan,



Just curious; we are on 9.2.1 and seems as we are using IDX_SETS1 and not IDX_SETS2; if I make the change to IDX_SETS2 do all indexes need to be rebuilt or will the change alone do the trick?



Thanks



Paulo



</QUOTE>

----

=== At 22 FEB 2013 11:04PM Barry Stevens wrote:  ===

<QUOTE>If you have done the clientsetup on all pcs, then afaik it will need no further work.

</QUOTE>

----

=== At 23 FEB 2013 10:04AM bshumsky wrote:  ===

<QUOTE>
<QUOTE>Bryan,



Just curious; we are on 9.2.1 and seems as we are using IDX_SETS1 and not IDX_SETS2; if I make the change to IDX_SETS2 do all indexes need to be rebuilt or will the change alone do the trick?



Thanks



Paulo

</QUOTE>

Hi, Paulo.  The IDX_SETS routine controls how multiple indices are joined together, and as such does not change the indices themselves.  You will not need to rebuild the indices.



Hope that helps,

- Bryan Shumsky

[url=http://www.revelation.com]Revelation Software, Inc.[/url]

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=D2F873E20004FE9FC6A81D800|View this thread on the Works forum...]]