Out of the Box Messageboards Very Slow

[Bill Liu, modificado 9 Anos atrás.]

I'm working on a modified Liferay message board that has many discussions and users. Some of our modifications include a sorting feature that sorts the discussions based on criterias such as: view count, reply count, ratings, etc. I've benchmarked the code, and it is very clear these sorting features now cause the Asset Publisher portlet to display the list of discussions very very slowly (~20 seconds!). Is there anything I can do to display all the messages faster?

The code to sort is very simple. It goes through every AssetEntry that is a message, sometimes filtering them by AssetTags, grabs all the required data from the related MBMessage, MBThread, and RatingsStats services, then filters and sorts based on the data it grabbed. I understand that this is not ideal at all. Is there any way I could make this faster?

Also, would simply increasing the memory usage of Java help at all?

I am using Liferay 6.1.0 CE

[David H Nebinger, modificado 9 Anos atrás.]

Are you doing the sorting in the SQL or are you doing the sort after the database retrieval?

[Bill Liu, modificado 9 Anos atrás.]

After the database retrieval.

I can't do the sorting using a DynamicQuery. Joining three tables using SQL is actually slower than sorting and filtering them after retrieval.

[David H Nebinger, modificado 9 Anos atrás.]

There's your answer - you're sorting after retrieval.

DQ is not the only game in town, you can also leverage custom sql as long as you can write the queries.

The problem w/ sorting after the fact, you have to retrieve all of the records (even more than what fit on the view page), complete the sort by iterating over the items (regardless of the sorting algorithm you're using, you have to touch each node in the list at least once), discard the ones that you don't plan on displaying, ... That's a whole bunch of work to complete just to come up w/ the items that you want to display.

[Jack Bakker, modificado 9 Anos atrás.]

I have BI solutions with designs to reduce overall processing load with goal towards improving end-user sense of response time ; is a sql query with Java logic mix

If you want to provide current data to user and if lots of joins across tables incurring overhead on its own then let the db take the query-crunching load via custom sql or a query within a *.persistence.MyReturnedEntityFinderImpl

[Bill Liu, modificado 9 Anos atrás.]

DQ is not the only game in town, you can also leverage custom sql as long as you can write the queries.

Sorry for not being clear, but I tried the custom SQL route and it was too slow (>1 minute) to do all the left joins. I tried a SQL query in MySQL Workbench and the query didn't even complete after 1 minute 30 seconds. Do note that MySQL WB actually enforces a 1000 row limit on SELECTs, so I wasn't even grabbing all the data I would need to in a production environment. I didn't even try the DQ, because if basic SQL can't even handle it, I doubted that DQs with the added overhead will be a better alternative.

If you want to provide current data to user and if lots of joins across tables incurring overhead on its own then let the db take the query-crunching load via custom sql or a query within a *.persistence.MyReturnedEntityFinderImpl

If I understand correctly, doing the custom SQL at the persistence layer will skip a lot of overhead brought about by using the DynamicQuery to do JOINs. Unfortunately, as stated above, my problem is that even pure SQL code is not fast enough to grab all the data I need, and a SQL and Java hybrid will only make it slower.

I've given up on grabbing all the data and spitting it back on demand every time the user hits the page (as per the Asset Publisher). Is there any alternatives? Caching maybe?

[David H Nebinger, modificado 9 Anos atrás.]

Okay, well then I'd take a different approach - a custom SB entity invoked via a model listener...

Your SB layer would define an entity with the keys/values that you plan on sorting on. Since you're populating it more or less like a trigger, the runtime joins and sql processing are greatly reduced; you select from your table w/ appropriate keys and sorting w/ one join to other tables.

Your model listener gets invoked as the message board is added/updated so you can invoke your SB layer to keep your table in sync. Combine that with a one time population script to fill your table from existing records and you should be good to go.

[Bill Liu, modificado 9 Anos atrás.]

David H Nebinger:

Okay, well then I'd take a different approach - a custom SB entity invoked via a model listener...

Your SB layer would define an entity with the keys/values that you plan on sorting on. Since you're populating it more or less like a trigger, the runtime joins and sql processing are greatly reduced; you select from your table w/ appropriate keys and sorting w/ one join to other tables.

Your model listener gets invoked as the message board is added/updated so you can invoke your SB layer to keep your table in sync. Combine that with a one time population script to fill your table from existing records and you should be good to go.

So if I understand correctly, you propose creating a whole new table (built by the service builder) that holds all the information necessary to filter and sort, therefore eliminating the JOINs and also allowing all filtering and sorting to be done on the DB level. You add a listener on the MBMessage model so that each time a new MBMessage is updated, you update the table as well.

I think this solution definitely works. I've already implemented a solution with a singleton holding the results of the query/fetching that gets updated every couple of minutes, but I think your solution would definitely be a good alternative.

[David H Nebinger, modificado 9 Anos atrás.]

Exactly. Eliminates the cache issue, eliminates the singleton, eliminates the joins causing the sql to be so hairy, and it should make your query process work a lot better because you can also window the return results.

These kinds of manual index tables are sometimes frowned upon by DBAs (they see these kinds of things as their job), but this is the Liferay database where they shouldn't be mucking around anyway. Leaves little option for much else, imho.