SQL index

[julio_r]

[ FlexSim 19.1.1 ]

Hello,

Is there a way to index a table so we can do a binary SQL search instead of a linear one?

I understand we could do something like this with a calculated table, but I'm not sure if this would allow a binary search.

Thank you

[joseph_g3]

@Julio R

Do you mean just doing a binary search without SQL? When using SQL there should be no reason to try to manually search, you can just use SQL select statements and it will find the answer in the best way possible.

Here's a similar question on stack overflow that has been answered: Can a binary search be done in a database in SQL? I like Donnie's answer to that question; there's no need to reinvent the wheel.

[julio_r]

Thank you @Joseph Gillespie,

I don´t know SQL that well, I didn´t know that the SELECT would do a binary search when posible.

Does this mean that all tables on FlexSim are indexed? For my very superficial search on this in order to do a binary search we need to index the table (this seems to be the only purpose on indexing a table). This question comes from a client who wants the querys to be faster and he suggested to index the tables.

[anthony_johnsonT83CM · Accepted solution]

There is active development on this feature.

In 19.0 and previous, if you add a Global Table and define it to use Bundle data (Use Bundle checkbox in quick properties), then you can right-click on individual columns and choose Optimize Column for Lookup. Then the table will keep an index for that column. However, the only feature that actually uses this index is when you do an SQL inner join with a WHERE or ON clause that compares a column of one table with an indexed column of another.

The forthcoming 19.2 release will include several improvements.

First, bundle tables will allow you to index columns either ordered or unordered. When ordered, lookup will be O(log n) (it's not a binary search, but rather a b-tree search). When unordered, lookups will be O(1).

Second, the Table interface will include two new methods:

• Variant getValueByKey( Variant key , Variant valueCol , Variant keyCol = nullvar)
• int getRowByKey( Variant key , Variant keyCol = nullvar )

These methods will lookup a value or row based on a key value. For tree-based tables or non-indexed columns, it will to a standard linear search. However, for bundle tables with indexed columns, it will do a fast lookup of those columns, either O(log n) or O(1).

Third, Table.query() will improve search speeds on single table queries, not just inner joins. For single table queries, lookup speeds will be O(1) for unordered indexed columns when you use a WHERE clause that compares an indexed column value with a constant value using the = comparison. Lookup speeds will be O(log n) for ordered indexed columns when you use a WHERE clause that compares an indexed column value with a constant value using either the =, <, >, <=, or >= comparison.