hi, we are in the middle of our spryker upgrade to 202108 and got some problems with the pub/sync of

sebastian.larisch

hi, we are in the middle of our spryker upgrade to 202108 and got some problems with the pub/sync of categories. especially \Spryker\Zed\ProductCategorySearch\Persistence\ProductCategorySearchRepository::getAllCategoriesWithAttributesAndOrderByDescendant seems to make problems. In our case we get a result with ~130k (28 stores, lots of product->category relations, many attributes) entities here. The raw sql is fast, but with propel its very slow. We have already implemented memoization, so we only need to call it once, but still looking for improvements. We played around with the formatters and tried to use ModelCriteria::FORMAT_ARRAY as formatter here, but it runs into an exception. I dont understand why. It happens in \Orm\Zed\Category\Persistence\Base\SpyCategoryAttribute::hydrate where it for some reason has a $startcol of 6. Where does it come from? It might be that changing the formatter has no effect but I still want to understand this issue with the $startcol or $offset. It seems to be relation with using virtual columns (->withColumn()) but even when removing these I get the error. Any ideas on that or any other ideas how to improve it?

U01CRKGV3GW

Hello Sebastian, have you finished your update?

We hit some bottlenecks regarding P&S category node publishing performance (i.e. here \Spryker\Zed\Category\Persistence\CategoryRepository::getCategoryNodesWithRelativeNodes called by \Pyz\Zed\CategoryStorage\Business\Writer\CategoryNodeStorageWriter::writeCategoryNodeStorageCollection) and bugs in the CategoryFacade. The query is not scaling up sufficient if you have multiple shops, categories and locales.
I opened an issues regarding that problem.

Have you experienced something similar?

sebastian.larisch

hi @U01CRKGV3GW, we haven’t finished the upgrade completely but are done with several modules including categories. we could solve our problem so far and found several places that causes this high amount of messages. With the update of spryker modules we got a few new publisher plugins but didnt remove the old ones, so we created redundant messages. for example the new ParentWritePublisherPlugin replaced a few other plugins like CategoryNodeStoragePublishListener. We just checked all created messages and listeners and if they are deprecated and removed all of them.
We also added some static caches in several classes. And last thing we found was related to ORM and with boolean that have a default. In our case \Orm\Zed\ProductSearch\Persistence\Base\SpyProductSearch::setIsSearchable - this was always set to isModified nevertheless if the value has changed, because $hasDefaultValue = true; so this created useless messages. We avoided that by adding a value comparison before updating this field. maybe there is something that helps you.

U01CRKGV3GW

Hello @UNGMX0012,
thanks for your answer. Avoiding unnecessary publish events is a good idea.

But another point is the poor ORM performance you mentioned in your first post. Hydrating the objects seems to be the bottleneck if your have a big query resultset.

sebastian.larisch

@U01CRKGV3GW right, one of those heavy methods is \Spryker\Zed\Category\Persistence\CategoryRepository::getCategoryNodeUrlPathParts , we have overridden it on project level to add ->setFormatter(ModelCriteria::FORMAT_ARRAY) and could avoid recursion and memory issues.