AnswerHub Upload Issue
Our AnswerHub community currently has a bug that prevents you from attaching your model or other files to your questions. AnswerHub is aware of this issue and they are working on a fix. Until we receive their patch, you can upload your models and other files to FlexSim's file share site. Include the file name in your question so that we know which upload to reference.

question

Joerg Vogel avatar image
1 Like"
Joerg Vogel asked Joerg Vogel commented

How can I accelerate a query in warehousing by clauses?

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

FlexSim 21.0.9
querysqlwarehousing
· 4
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

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.

1 Like 1 ·

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

1 Like 1 ·
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!

1 Like 1 ·
anthony.johnson avatar image
1 Like"
anthony.johnson answered Joerg Vogel commented

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

· 1
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

@anthony.johnson, thank you for this detailed answer.
0 Likes 0 ·
Steven Hamoen avatar image
1 Like"
Steven Hamoen answered Joerg Vogel commented

@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.

· 1
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.