Snipe-it: (not sure if bug) All-Assets search perfomance in 4.0.x

[snefedoff]

Expected Behavior

Fast assets search in All Assets list view.

---

Actual Behavior

Actual asset search takes 22 seconds after upgrade from 3.6.2 to 4.0.9.

---

• [x] I have enabled debug mode
• [x] I have read the Common Issues page](https://snipe-it.readme.io/docs/common-issues)

---

Snipe IT Version v4.0.9 build 120 (gf66e222), clean install. Database imported from .sql file.
Total Assets in database - 5 000 items. In old, 3.6.2, version same search in same db and server config, on same hardware took less than 2 sec.

Ubuntu 16.04 Server
nginx/1.10.3 (Ubuntu)
PHP 7.0.22-0ubuntu0.16.04.1
mysql Ver 14.14 Distrib 5.7.19

Server hardware
*-cpu:0
product: Intel(R) Xeon(R) CPU X5650 @ 2.67GHz

*-memory
size: 3947MiB

---

Did a roll-back to 3.6.2 version for now. What could possibly cause this perfomance drop?

[grandmamoses]

Hi

We also have the same issue. Most searches are taking WAY longer now. Using latest version...

Thanks!

[diekmanu]

Here it's 10 seconds for ~900 entries. Seems slower than 3,6.x.

[L3nny5]

Same here

[TheVakman]

Agreed, it is slower versus 3.6.2. I don't think it is as slow as others have mentioned but it is noticeable during searches.

[snipe]

That's an unfortunate side-effect of the ability to check things out to assets, locations, or people. We have some pretty crazy joins in there to make that happen.

[snefedoff]

@snipe so there's nothing we can do? Any chance on disable checkout assets to other assets-locations via UI feature in future for those who dont need it?

[snipe]

Any chance on disable checkout assets to other assets-locations via UI feature in future for those who dont need it?

Not really - that would require us making 2 versions of every query.

We're working on testing some indexes/optimizations, and may need to rewrite some of the queries in raw SQL instead of the query builder.

[snipe]

(We've also looked into offloading some of that onto something like ElasticSearch for very large datasets, but we shudder at the support nightmare that will create.)

[snefedoff]

Okay, I'll wait for optimizations. Thanks for support!

[snefedoff]

Just upgraded test machine to SnipeIT Version v4.0.13 build 273 (gab3f5f4) and found GREAT perfomance boost. Asset search now fast and furious. Great work!

[snipe]

@zuparplex we're definitely not done yet. When you search the assets section, it's effectively doing a full table scan, which is fine if you have 200 assets, but really starts to suffer when you have 20,000 assets and you need to scan that table AND load up all of those associated relationships (users, locations, etc). This is in part due to the new "checkout to location", "checkout to asset" features we added in v4 - and one of the very reasons I didn't want to offer those features in the first place. The complexity of the queries we have to do to allow you to search on anything has grown dramatically, since we have to ask questions of all of these additional relationships now.

Part of the problem is that Laravel (the framework that Snipe-IT is built on) doesn't support FULLTEXT searching natively.

We have a few options here, none of which I love, but we have to find a real solution here, and quickly:

Manually Adding Fulltext Indexes

Manually adding full text indexes (in SQL versus using the ORM), and modifying every single search query to use the full text vernacular of MATCH AGAINST.

Pros:

• No external dependencies

Cons:

• LOTS of code changes, so lots of places for code to break
• Locks us into specific versions of MySQL/MariaDB, and might mean we can't expand out to SQLIite, etc.

Using Laravel Scout

Laravel Scout seems like the most Laravelly way to handle this.

Pros:

• Laravel-native-ish

Cons:

• Would depend on a package like YABhq/laravel-scout-mysql-driver
• Not sure how well it will handle the crazy join/model relations we have
• Lots of code changes

De-Normalizing the Database with Meta Data to Improve Searching

This, combined with FULLTEXT indexes on the new "meta" columns would allow us to simplify our queries substantially, but it comes at the cost of lots of duplicated content, and potential for data to become outdated resulting in weird search results.

Pros

• No additional dependencies

Cons

• Duplicated content
• Lots of code changes
• Possibility for data to be out of sync, resulting in weird results
• Still needs FULLTEXT index to be optimized

Using ElasticSearch, Sphinx, Lucene, Solr, etc

By far my least favorite for an open source project where I have to support users who are not developers/web sysadmins. (This is an option I've used for private corporate projects before and it's worked very well, but the support hell is something I just don't know we can handle.)

Pros

• This is really the kind of thing these types of search engines are for

Cons

• HUGE additional dependency, problematic for free users and additional deps for us to manage on the hosted side :(
• Code changes

[snipe]

We've added some indexes that will hopefully speed things up a bit further.

[snefedoff]

Updated test machine to version Version v4.0.15 build 339 (g17b2719).
Search is smooth and fast, keep testing now.
But all Location column filled with "deployedLocationFormatter" value.

[snipe]

@zuparplex can you show me a screenshot?

[snipe]

(Also make sure you've cleared all of your view and route caches, etc. If you installed via git you can just run php upgrade.php from the project root)

[snefedoff]

I've upgraded using upgrade.php ( great util btw ) so I didnt run any usual for upgrade commands ( config:cache-clear etc)

here is screen
http://take.ms/oJw62

[snefedoff]

Ran config:cache and view:clear, just to make sure - no luck.
It shows deployedLocationFormatter for all assets, no matter location set or not.

[snipe]

So weird. Can you reproduce on the demo?

[snefedoff]

No, everything is fine on the demo :/

[snipe]

In your resources/views/partials/bootstrap-tables.blade.php do you see a deployedLocationFormatter method?

[snipe]

@zuparplex should look like this: https://github.com/snipe/snipe-it/blob/167cd4e4a0f5efdffd8872eefe55218b4ebcf379/resources/views/partials/bootstrap-table.blade.php#L383-L390

Tho the line numbers may be off, since I just pushed out some changes to that file.

[snefedoff]

@snipe no, I dont see that method.

Heres file that I got on server after php upgrade.php

https://paste.ofcode.org/XusXbsqRZR7p9mEcGNTiXY

[snipe]

Hm, looks like you have some outdated files then.

[snefedoff]

Yes, my bad. Didnt notice Errors in upgrade.php log.

error: The following untracked working tree files would be overwritten by merge:
database/migrations/2017_10_19_120002_add_custom_forgot_password_url.php
database/migrations/2017_10_19_130406_add_image_and_supplier_to_accessories.php
database/migrations/2017_10_20_234129_add_location_indices_to_assets.php
public/css/bootstrap-tables-sticky-header.css
Please move or remove them before you can merge.

Fixed them, rerun upgrade.php and all good. Sorry.