topic Re: How can I accelerate a query in warehousing by clauses? in FlexSim Forum

How can I accelerate a query in warehousing by clauses?

joerg_vogel_HsH — Mon, 29 Nov 2021 06:39:01 GMT

[ FlexSim 21.0.9 ]

Theoretically as an example I would build a warehouse by

place (cities like Houston, Saint Louis, and so on)
zones (facilities in a place)
Type (floor storage and rack)
aisle
level
bay
slot.

The internal warehouse data is immense. How should I build my query to accelerate to find slots or items in methods like findItem, findSlot, queryItems or querySlots? In which order I can limit a query data from the beginning similar to partitions while I build clauses? I would set none standard attributes as warehouse labels like place and Type from my example above.

Many Thanks for your insights.

Joerg

Re: How can I accelerate a query in warehousing by clauses?

joerg_vogel_HsH — Mon, 29 Nov 2021 07:09:59 GMT

Found already table query speed

Re: How can I accelerate a query in warehousing by clauses?

ryan_c10 — Wed, 01 Dec 2021 01:31:50 GMT

Hi @Joerg Vogel,

Was the other post you linked to sufficient to answer your question? If so, please add and accept an answer to let others know the solution. Or please respond to the previous comment so that we can continue to help you.

If we don't hear back in the next 3 business days, we'll assume you were able to solve your problem and we'll close this case in our tracker. You can always comment back at any time to reopen your question, or you can contact your local FlexSim distributor for phone or email help.

Re: How can I accelerate a query in warehousing by clauses?

joerg_vogel_HsH — Wed, 01 Dec 2021 07:11:34 GMT

At all and @Ryan Clark, I attached this link to get further more specialized suggestions. If I didn’t do it, I would get suggestion already stated there. I wish from you and FlexSim not to close my question, because optimizing a model is a general issue. A warehouse can lead to a really large list. Are there any clauses that can split and keep up a sub list cached? Are there other clauses to update such a sub list? Thank you for considering my request and keep it open! Regards Joerg

Re: How can I accelerate a query in warehousing by clauses?

ryan_c10 — Wed, 01 Dec 2021 17:33:29 GMT

Hi @Joerg Vogel,

No worries! We'll definitely keep your question open! I don't personally have an answer to your question, but I will make sure to leave the question open for others to answer. Thanks for your reply!

Re: How can I accelerate a query in warehousing by clauses?

anthony_johnsonT83CM — Wed, 01 Dec 2021 17:38:32 GMT

The referenced article is a bit out of date, especially if you are using Storage.system.findSlot()/findItem() (it doesn't really address that). Here are some things to note as an addendum to that article:

Indexing Bundle Columns

If you are using Table.query() to query a single global table, and it is a bundle table, you can index certain columns with the right-click menu in the table to improve lookup speed. For example, if your query is SELECT * FROM MyTable WHERE id = 5, then if you have selected one of the right-click "Index this column" options on your bundle table's "id" column, then it should improve speed of queries significantly. The table will keep an index of that column, so that when it sees the clause WHERE id = 5, the index will have stored exactly which rows have id 5, and it will only look at those rows when evaluating the query.

Speed Improvement in 22.0

There is a significant speedup in version 22.0. In previous versions (I think from 20.0 through 21.2), we were too aggressive in trying to use available indexes, such that we were rebuilding indexes that cross reference those indexes, to optimize for the case where there are multiple possible indexes to use (which admittedly doesn't happen that often). In 22.0 we simplified this mechanism, which significantly improved speed. The side effect is that, if there are multiple indexes to use on the same table, it will only use one of them, namely the one that is further left in the query.

Take an example where you have a query like SELECT * FROM MyTable WHERE id = 5 AND quantity = 1, and you have an index on both the id column and the quantity column. In older versions we tried to be clever and filter on both the indexes. However, the result is that you actually have to cross-multiply the two indexes, which kind of defeats the improvement you get from indexing. So in 22.0 we've simplified it so that it will only use one index per table. If there are multiple possible indexes to use, it will use the one left-most in the query, i.e. the id column in this example. This means that, to get the best indexing bang for your buck, you want to put the most limiting indexes to the left of your query's where. If the id column represents a primary key (only one row per id), then that's the most limiting index and should be the left-most in the query.

Requirements for Using an Index

In order to use an index, the filtering expression must be either the main WHERE, or it must be part of an AND in the top-level expression of the WHERE. It cannot be part of an OR, or a "descendant" of an OR.

Examples of where clauses that can use the "id" column's index

WHERE id = 5
WHERE id = 5 AND quantity = 1

Examples of where clauses that cannot use the "id" column's index

WHERE id = 5 OR id = 7 (for this one, you could probably use UNION ALL instead to ensure index usage)
WHERE quantity = 1 OR (id = 5 and quantity = 2)

Indexes in Inner Joins

For inner joins, the table will see if there is an index, and use it. If there is no index, then it will build a temporary index and use it. The index to use must be on the table on the right side of the join. For example, say your query is SELECT * FROM users INNER JOIN locations ON users.location = locations.id. In this case, the query engine will look to see if there is an index on the locations.id column. If there is not one, then it will build one and run the query. Note that this is different than if your query were SELECT * FROM locations INNER JOIN users ON users.location = locations.id. Even though the results of those two queries may be the same, the speed differences may be significant. Since in the second query, the users table is on the right side of the join, the query engine will look at the users.location column for an index, and build one if there isn't one, meaning potential extra time if you haven't already designated that column as indexed.

Note that this also has significant implications for speed if there are other filters you want to use, because we only use one index per table. For example, say we add a where clause to the above examples: WHERE users.id = 5. Note that, in the first example (users on the left of the inner join, locations on the right), we will look for an index on locations. That leaves us space to also still use the "id" index on users without using two indexes per table. Thus, we could get a very fast total lookup complexity of O(1) (we're looking up the user with id 5, then looking up the location with that user's location). However, if we use the second example (locations on the left, users on the right), we're going to use (or build) an index on the users.location column, which leaves us no room to use the users.id index. Here the complexity could be O(n x m) where n is the number of rows in the locations table, and m is the average number of users per location. So you could potentially get significant speed differences for queries with the same end result.

Using Storage.system.findSlot()/findItem()

The warehousing mechanism uses the same indexing mechanism as described above, but with item/slot labels as the indexes. Note that you should use 22.0 to take advantage of this, as there were significant speed issues with our too aggressive index usage mentioned previously. If you mark an item/slot label as indexed in the storage system properties, then the system will keep an up-to-date index of all item/slot labels in the system, and findSlot()/findItem() with a WHERE clause that filters on those labels will be significantly faster. As of 22.0, the system does not keep indexes on the standard Storage.Slot / Storage.Slot.Item attributes (for example, things like bayID, address, etc.). Thus, if there are standard attributes that you want to be indexed, then you can copy those attributes to labels on the slot/item, so that you can index them. This can be done programmatically:

Storage.system.querySlots("UPDATE Slots SET BayID = slot.bayID")

(Although it looks like you have to first add the label on all of your slots using the painter first).

Hope this helps

Re: How can I accelerate a query in warehousing by clauses?

joerg_vogel_HsH — Wed, 01 Dec 2021 20:36:58 GMT

@anthony.johnson, thank you for this detailed answer.

Re: How can I accelerate a query in warehousing by clauses?

SCHamoen — Thu, 02 Dec 2021 09:56:50 GMT

@Joerg Vogel I also want to present a different solution, We have recently made a large warehouse model with a lot of data and we used very simple tree structures with node links where possible. So if you have a treenode structure with positions and a treenode structure with SKU's you can have a nodejoin between the SKU and the matching position for instance.

Sometimes you have to loop through the list and other times you can point directly to a treenode but on average we where surprised by the speed we could achieve.

Re: How can I accelerate a query in warehousing by clauses?

joerg_vogel_HsH — Thu, 02 Dec 2021 10:41:51 GMT

@Steven Hamoen, thank you for pointing at a parallel structure to shorten a search through a complete warehouse. I have to think about this.