In one OI 8.0.1 application, I have noticed that a Btree.Extract date range search that sometimes returns null depending on what range is specified. Using either the "~" or ]" and "SRP Computer Solutions, Inc.[/url]

Roger,

Are you searching on the Internal value? I have found with Date fields that the Btree is looking at the internal value.

Sean

Sean,

Yes, I am searching on the internal values.

rhall@srpcs.com

SRP Computer Solutions, Inc.

I get no keys returned if I search from 2/1/2000 to the present (using the Iconv values). Searching from 1/1/2000, though, works.

I believe Btree.extract will perform an Iconv on the search value passed to it according to the conversion on the indexed field. So perhaps Btree.extract is using iconv( iconv( '1 FEB 2000', 'D'), 'D')=19010 for your first search value and iconv( iconv( '1 JAN 2000', 'D'), 'D')=7687 for the second. This could very well explain why you get more results for the second search (7687=16 JAN 1989) than you do for the first (19010=17 JAN 2020 !)

HTH, M@

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

Easy enough to test: Create a dictionary item with the same FMC but without Date attributes on the O/IConv. BTree index and then try the BTREE.EXTRACT using that dictionary descriptor.

The dictionary item is right justified I hope.

Not to interrupt the discussion and the resolve of your issue…

Over the years I have read several times that developers have kind of "reinvented the wheel" on some of the Revelation items so as to improve or enhance. I have read some developers postings that seem to literally break down the workings and supply line by line information which is very helpful. What I don't understand is that it seems that the "indexing" area has not been touched.

My opinion is that using a "Btree" index for dates (and other index types) is somewhat like towing a jet ski trailer with a freight train engine. It works, but it is so darn wasteful and time consuming.

Wouldn't it be extremely quicker to use some kind of indexing system that just simply indexes the date by the date (the iconv value that is)? The answer is yes! And it is extremely quick even when using the "inclusive" operator.

If the index takes the internal date and uses that internal date as a record id within a Linear Hashed file then reading only the record ids are needed to determine what records need to be merged in order to create the "selected index", and that would be very fast.

Not to mention the extremely efficient and quick build or rebuild process when using V119 to sort and extract (using the "E" option, or raw mode) allows for one write for each index value when building or rebuilding.

Updating the index during a "delete" or "write" process would be as fast as any other read or write.

The index would not be presorted, although since sorting when using V119 happens within a blink of an eye, presorting the index would become unnecessary.

But then… maybe "Btree" indexing is something that some can not get away from.

Richard,

There is already a semi-method for what you've described. Using a separate table, you can build a relational index using the iconv date as the key. This gives you your date-to-keys relationship you've described. All the read/write/delete/update management is done by the index, so the developer doesn't have to worry about any of that. All you'd have to do is write your extract/merge/sort routine.

I know others that have used this quite successfully. I've found that with a little symbolic creativity, you can build the same kind of indexes on whole months or quarters, making the extraction process even faster.

This is essentially the same as what the Btree index is for something simple like dates. If you understand the structure enough, you can manually read the bang table and pull out whatever keys you need. In the end, it becomes a matter which is the fastest and simplest to use - reading 365 days of relational keys, or providing a range to Btree.Extract?

ftomeo@srpcs.com SRP Computer Solutions, Inc.

I tried using the Oconv values in the Btree.Extract range search and that solved the problem.

Thanks for your insight!

rhall@srpcs.com

SRP Computer Solutions, Inc.

View this thread on the Works forum...