Jump to content
www.HDsports.at

Many Database queries

Recommended Posts

hey,

is it normal that one jreviews module ( JReviews Reviews Module) produces 25 databse queries?


i tested it with debug manager

when i activated this module i had 100 database-queries. many from them are jreviews

 

10 × SELECT Criteria.id AS `Criteria.criteria_id`, Criteria.title AS `Criteria.title`, Criteria.groupid AS `Criteria.group_id`, Criteria.state AS `Criteria.state`, Criteria.search AS `Criteria.search`, Criteria.required AS `Criteria.required`, Criteria.config AS `ListingType.config`
  FROM ngie_jreviews_criteria AS Criteria
7 × SELECT                     groupid                    FROM ngie_jreviews_groups

3 × SELECT CriteriaRating.criteria_id AS `CriteriaRating.criteria_id`, CriteriaRating.title AS `CriteriaRating.title`, CriteriaRating.required AS `CriteriaRating.required`, CriteriaRating.weight AS `CriteriaRating.weight`, CriteriaRating.description AS `CriteriaRating.description`, CriteriaRating.listing_type_id AS `CriteriaRating.listing_type_id`, CriteriaRating.ordering AS `CriteriaRating.ordering`, ListingType.state AS `CriteriaRating.listing_type_state`
  FROM ngie_jreviews_criteria_ratings AS CriteriaRating
  LEFT JOIN ngie_jreviews_criteria AS ListingType
  ON ListingType.id = CriteriaRating.listing_type_id

2 × SELECT Field.fieldid AS `Field.field_id`, Field.groupid AS `Field.group_id`, Field.name AS `Field.name`, Field.title AS `Field.title`, Field.showtitle AS `Field.showTitle`, Field.description AS `Field.description`, Field.required AS `Field.required`, Field.type AS `Field.type`, Field.location AS `Field.location`, Field.options AS `Field.params`, Field.contentview AS `Field.contentView`, Field.listview AS `Field.listView`, Field.compareview AS `Field.compareView`, Field.listsort AS `Field.listSort`, Field.search AS `Field.search`, Field.access AS `Field.access`, Field.access_view AS `Field.accessView`, Field.published As `Field.published`, Field.ordering AS `Field.ordering`, `
  Group`.groupid AS `
  Group.group_id`, `
  Group`.title AS `
  Group.title`, `
  Group`.name AS `
  Group.name`, `
  Group`.showtitle AS `
  Group.showTitle`, `
  Group`.ordering AS `
  Group.ordering`
  FROM ngie_jreviews_fields AS Field
  LEFT JOIN ngie_jreviews_groups AS `
  Group`
  ON `
  Group`.groupid = Field.groupid
 × SELECT                         *                     
  FROM                         ngie_jreviews_fieldoptions

2 × SELECT media.media_id, media.media_type, media.media_function, media.likes_rank, media.likes_up, media.likes_total, media.created, media.main_media, media.listing_id, media.review_id, media.user_id, media.filename, media.file_extension, media.rel_path, media.title, media.description, media.media_info, media.metadata, media.embed, media.published, media.approved, media.access, media.views, media.filesize, media.extension
  FROM ngie_jreviews_media AS media

and some more with 1x


when i deactivate this module i have only 75 db queries


i am not really know if thats normal, but i think thats not good for performance of web site...

Edited by www.HDsports.at

Share this post


Link to post

I did my own tests and the number of queries is lower, but it all depends on what you have active on the site. For example, do you have a Community integration with other extensions (EasySocial, JomSocial, etc.), do you have add-ons? These things can increase the number of queries, but the performance impact can be negligible. Even with 25 queries, the performance impact depends on the type of queries. For most pages in JReviews, there's one "heavy" query, which is already optimized, that retrieves the results and all subsequent queries are much faster (many times below 1ms) because we already have the unique IDs from the first query.

 

Test Results:

 

JReviews v2.7

 

Empty home page (no component or JReviews output): 34 queries

Reviews module with add-ons (no community integration): 45 queries

Reviews module without add-ons (no community integration): 45 queries

 

JReviews v3

 

Empty home page (no component or JReviews output): 34 queries

Reviews module with add-ons (no community integration): 43 queries

Reviews module without add-ons (no community integration): 40 queries

 

In conclusion, the number of queries can be important, but not as important as the type of query and how long it takes to run each specific queries. You could have 100 queries that run very fast and 1 bad, not optimized query, that can take longer than those 100 queries.

Share this post


Link to post

hello alejandro,

 

i unterstand. but the queries run also very slow:

Zeit: 105.79 ms / 359.56 ms Speicher: 8.122 MB / 18.46 MB Application: afterRenderModule mod_jreviews_listings (Die nächsten Laufevents)

Zeit = time, Speicher = storage/memory

 

when i change in the module the "Max. number of results" to 2 than its 4 MB. with only 2 results.... i am not sure if thats normal? (you can look on my frontpage hdsports.at where i have integrated some modules of jreviews)

all other modules in the debug modus have memory of 0.0xx MB

Edited by www.HDsports.at

Share this post


Link to post

Yes it is normal. JReviews has a lot more features than a regular article and for this reason also requires more queries to support these features. If all those queries run in less than 400ms then that is not slow. A slow query is a single query that can take several seconds to run. If your page has a lot of traffic, the MySQL cache will ensure that the same queries ran over and over will run faster because they are served from the query cache.

Share this post


Link to post

Yes it is normal. JReviews has a lot more features than a regular article and for this reason also requires more queries to support these features. If all those queries run in less than 400ms then that is not slow. A slow query is a single query that can take several seconds to run. If your page has a lot of traffic, the MySQL cache will ensure that the same queries ran over and over will run faster because they are served from the query cache.

okay i unterstand

i checked the jreviews settings and i see

Disable jQuery = Yes
 Enable Javascript Loader = No
 
For a better Performance is it better to set "disable jquery" to "no" and enable the javascript loader?
 
(Disable jQuery UI = No)
Edited by www.HDsports.at

Share this post


Link to post

That setting is under Javascript & CSS Settings and it's not related to database queries, but to the way that scripts (javascript files) are loaded on the page.

 

There is one setting on that page called Disable DB Query Index Hints that can affect DB performance and how useful (or not) it is depends on server and amount of data you have on the site.

Share this post


Link to post

Yes it is normal. JReviews has a lot more features than a regular article and for this reason also requires more queries to support these features. If all those queries run in less than 400ms then that is not slow. A slow query is a single query that can take several seconds to run. If your page has a lot of traffic, the MySQL cache will ensure that the same queries ran over and over will run faster because they are served from the query cache.

hello alejandro,

sorry but i am not happy with that.

my hoster write me also that such values are very bad for the performance of a website.

 

i have this problem only with jreviews module and i found no way to reduce the time.

 

here some examples

167.92 ms / 398.01 ms Speicher: 4.508 MB / 14.35 MB Application: afterRenderModule mod_jreviews_listings (Die nächsten Laufevents)
Zeit: 22.84 ms / 444.69 ms Speicher: 2.010 MB / 16.74 MB Application: afterRenderModule mod_jreviews_listings (Beliebte Skigebiete)
Zeit: 26.21 ms / 644.29 ms Speicher: 0.326 MB / 30.51 MB Application: afterRenderModule mod_jreviews_geomaps (Skikarte)
Zeit: 49.91 ms / 577.65 ms Speicher: 0.000 MB / 29.81 MB Application: afterRenderModule mod_jreviews_fields (Nach Region filtern)
Zeit: 19.13 ms / 667.23 ms Speicher: 0.345 MB / 30.91 MB Application: afterRenderModule mod_jreviews_filters (Mein Wunschskigebiet finden)

okay, i can live with 20 ms. but 170 ms?

its a module which shows only 2 events. Maybe the problem is, that in this directory are 5000 events (listing detail pages)?

 

with an module from an other developer i had an similar problem. the module shows similar articles at the end of an article. if i used no filter in the module settings, than there was 200 - 400 ms.

after i used a filter "show only articles from 1 category, that was posted in the last 30 days" i had only 10 ms.

 

i think, this is the same problem with the jreviews module? but the filter options doesnt have an effect (maybe only 1 category or filtering with custom where).

When i use the listings modul for an directory with 600 listing detail pages (the 2nd in my code) than the time is not so high (22 ms)

 

so i think there is a context between the time and the number of listing detail pages. but when i have no possibility in the module settings to limit the search, than i have this long time as in my 1st example

Edited by www.HDsports.at

Share this post


Link to post

I can take a closer look if you submit a ticket with admin, the details of the module, and database access. However, I also want to make a couple of points:

1) Based on my professional experience doing this for over 10 years, hosting companies don't know anything about the software you run on your site. That makes them the least prepared to make statements about the perfomance of such software and when they do, what they say is typically not useful at all.

2) Modules and components are not very different. Just because a module is a module and it displays two records it doesn't mean it's going to be much faster than a component page that displays two records, or 20 records, or another module that displays 20 records. You can't compare any of this and jump to conclusions.

The same module with different filters will still run the same number of queries, regardless of the number of results. So performance-wise, the main difference is how quickly the results can be retrieved from the database and the main factor there is whether the query can find good indexes. If there are good indexes, then the result is faster and somethings adding a filter, like a category ID helps if it limits the number of records (depends on indexes again) that must be queried because there is no need to scan the entire database table.

The listings module is not much different than a list page in JReviews (component). It runs the same queries and most of the same features are available in both of them. So just because you want a module to run in under X ms, doesn't make it so. Maybe it's possible and maybe it just isn't.

From what you've said so far, there are no slow queries on your website, at least not coming from JReviews. The higher the number of modules per page, the higher the number of queries, the slower the page will load. I don't know how many modules you have running per page, but if you have a lot, then limiting the number of modules is the first thing you need to do.

Share this post


Link to post

hello alejandro

 

i hope you see my post not as a criticism, it was only a feedback.

i am a great fan of jreviews and your component helped me to realize new ideas with my website. But with about 20.000 articles (joomla articles + jreviews) on my site, performance is a very important point and i had in the past many problems with it. I had already some components / modules, which had not optimized database-queries, which is going to massive performance problems of my website. my hoster is specialized for joomla-site, and they helped me many times to solve such problems.

 

In principle i am happy with the performance of jreviews (listing detail pages, listings), but i have only a problem with the modules. My experience is not enought, to solve this problem. but i think such a long time (200ms.. the most muldes have less than 10 ms) can be avoid.

 

i can send you my site details, maybe you will find a way to solve that.

Edited by www.HDsports.at

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue. Please refer to our Privacy Policy.