Virtuemart 2 Product SQL Queries

[chrisatomix]

Hi all,
I'm a developer that works heavily with Joomla/Virtuemart, and I'm currently testing out and examining the new code in Virtuemart 2.0 beta 3.

I've had issues before with the large number of SQL queries in Virtuemart, which can cause pages to load extremely slowly. In some cases I've been forced to rewrite entire modules to reduce the strain on the MySQL server.

I was very disappointed to see that despite the much cleaner code structure in VM2 (MVC layout etc), the SQL has not been optimised or made more efficient.
For example, loading a list of products in a category (when there's 2 product in 1 category on my test site) seems to perform 69 SQL queries (!!!), including the following actions:

   
• Select everything (*) from the category (jos_vm_category)
• Select the file_ids for this category (jos_vm_category_media_xref).
• Select the IDs of any children of this category (jos_vm_category_xref)
• Select the a count of the number of products in this category (jos_vm_product_category_xref and jos_vm_product)
• Select the IDs of any parents of this category (jos_vm_category_xref)
• Select the name of this category (jos_vm_category)
• Select the IDs of the first 20 products in this category (jos_vm_product_category_xref, jos_vm_category, jos_vm_product)
• Select the IDs of all products in this category (jos_vm_product_category_xref, jos_vm_product, jos_vm_category)
• For each product ID found, select everything (*) from the product table (jos_vm_product)
• For each product ID found, select the file_ids (jos_vm_product_media_xref)
• For each product ID found, select the product_price_ids (jos_vm_product_price)
• For each price ID found, select everything (*) (jos_vm_product_price)
• For each product ID found, select the manufacturer_id (jos_vm_product_mf_xref)
• For each Manufacturer ID found, select everything (*) (jos_vm_manufacturer)
• For each product ID found, select the category ID (jos_vm_product_category_xref)
• For each product ID found, select the vendor_currency (jos_vm_vendor)
• For each currency ID found, select the display_style (jos_vm_currency)
• For each product ID found, select the custom field ID (jos_vm_custom, jos_vm_custom_field, jos_vm_custom_field_xref_product)
• For each product ID found, select everything (*) from the prices (jos_vm_product_price)
• For each product ID found, select the vendor_id (jos_vm_product)
• For the vendor ID found, select the vendor_currency (jos_vm_vendor)
• For the vendor_id found, select all the default shopper_group IDs (jos_vm_shopper_group)
• For the vendor_id found, select everything (*) from the calcs where the type is "tax" (jos_vm_calc)
• For the vendor_id found, select everything (*) from the calcs where the type is "DBTax" (jos_vm_calc)
• For the vendor_id found, select everything (*) from the calcs where the type is "DATax" (jos_vm_calc)
• Select all the product_ids, product_list and product_name in this category (jos_vm_product_category_xref, jos_vm_product)
• For each product ID found, select all the related products (jos_vm_product, jos_vm_product_relations)
• Select the vendor_store_name for this vendor (jos_vm_vendor)
• Select the vendor_store_name for this vendor (jos_vm_vendor) (identical to previous query)
• Select Y/N if there are child products for this product (jos_vm_product)
• Select Y/N if there are child products for this product (jos_vm_product) (identical to previous query)
• Select the number of custom fields for this product (jos_vm_custom_field_xref_product)
• Select the votes for this product (jos_vm_product_votes)

After those queries, it loops back to query #9 in the list above and starts all over again for the next product.
After it's done that, it then:

   
• Gets the total number of products in this category (jos_vm_product, jos_vm_product_category_xref)
• Gets the product_ids of all the products in this category (jos_vm_product, jos_vm_product_category_xref, jos_vm_category)
• Gets the manufacturer names and IDs of all the manufacturers for the products above (jos_vm_manufacturer, jos_vm_product_mf_xref)

69 queries for only 2 products! This is mostly because of all the generic functions being used to access the database via the models. Usually in Joomla, variables are set in the model classes and checked if they exist. If not, the query is run, otherwise the existing variable value is used. This prevents the same query from being run multiple times on the same page.


Also, MySQL has a nice feature which will let you find out the total number of results that would have been found without limiting the query, which means you really don't need to do:

   
• "SELECT COUNT(product_id) FROM jos_vm_product_category_xref WHERE category_id = 1" to get the total number of products in the category
• "SELECT product_id FROM jos_vm_product_category_xref WHERE category_id = 1 LIMIT 20" to get the first 20 products in the category.

I'm quite surprised that "SQL_CALC_FOUND_ROWS" isn't being used here instead of two queries.


Also, why select the product_ids and then select the product details for each one individually? Why not select everything in the first place? Surely multiple functions in the model can use the same data returned by a single query.


I'm more than happy to help with improving this, but I just wanted to express my disappointment in seeing this. I just added a total of 20 products to the category, and 450 queries were needed to display the page!


If this is something that is planned on being fixed before the VM2 gold release, then please ignore this post. Otherwise, you may have a lot of people complaining that your software has caused their MySQL server to grind to a halt

[chrisatomix]

I'll add, I'm quite serious about my offer to help reduce the number of queries. I've become a bit of an expert over the last few years on MySQL query optimisation, and I'd love to speed up Virtuemart. Otherwise I'll probably have no choice but to "fork" the project and develop my own custom version for our clients, then backport any changes from VM into my own project.
But then everybody loses

[jjk]

I'm not a developer, but I do know that VM2 Beta3 code is totally obsolete meanwhile. If you want to look at this seriously, please follow this link:
http://dev.virtuemart.net/projects/virtuemart/wiki/Setting_up_a_Development_Environment and get the latest SVN version.

Since the release of VM2 RC1 approximately 2 months ago, the code and database tables have been changed significantly again. The xref tables don't exist anymore in the trunk.
A new public release is imminent...
(However, while I'm writing this, the latest commits to the trunk are producing some hickups again (500 errors)

[chrisatomix]

Thanks for the quick reply jjk!

My apologies, I was running VM2 RC1 (I looked at the virtuemart.xml file and it said VM2 Beta 3, obviously it hadn't been updated).

I did pull down the latest trunk from SVN yesterday while I was posting my comment, and I noticed that some of the code had been fixed quite a bit...but there was still a lot of room for improvement.

For example, I would have modified the $_data array inside the product model to store multiple products, so you can use the same wrapper functions to retrieve either one single product or multiple ones.
(This is quite simple, it just means instead of calling getProduct($product_id) with a single product ID, you can either pass a string or an array, ie getProduct(array(1,2,3,4)).
The function itself can check if $product_id is a string, if it is it can convert it to an array. Then it's just a matter of:

Code Select Expand


"SELECT * FROM jos_vm_product WHERE product_id IN(".implode(',',$product_id);


I tried some of these changes last night and was able to cut the number of duplicated queries quite drastically, however it was only as a proof of concept so I could find out if it was even possible.

Luckily VM2 is so much better laid out than VM1, it made it much easier to make changes (Thankyou for switching to MVC!)

SQL optimization and query reduction will be even more critical for those VM users connecting to remote MySQL servers. I was recently asked to rebuild a PHP script which synchronizes pricing and stock levels across VM sites on multiple servers. The script was taking up to 40 minutes to run (per website!). By rewriting the SQL and using data arrays, left joins, GROUP_CONCAT, SQL_CALC_FOUND_ROWS, and subqueries I was able to reduce this to around 2 minutes per website, and now we're able to synchronize pricing and stock levels every hour instead of every 24 hours.
Another script I managed to reduce from 72 minutes to 74 seconds. And by keeping the code clean and unsetting unused variables along the way, I don't even come close to PHP's memory limit (which can be as low as 8MB on some servers).

I'll check out a copy of the trunk from SVN once a week to watch the changes, and subscribe to the bug tracker so I know what's going on.

Can't wait for the final release...writing Joomla 1.5 components for our VM1.1 stores is getting quite painful after having the luxury of writing 1.6/1.7 components

[Milbo]

Hey chrisatomix,

the code you have seen was written by a member who already left the team. I tried afterwards to enhance it, but I think you are a better sql guru then me.
I did the sortSearchListQuery and the getProduct and getProductSingle functions. We want to use for getProductSingle a simular method as used in sortSearchListQuery, maybe we can even directly use it.
the sortSearchListQuery can also be enhanced. Atm there are some things doubled, the reason is that we used in the frontend other keywords then in the backend.
So of course, just join the team.

[chrisatomix]

Hey chrisatomix,

the code you have seen was written by a member who already left the team. I tried afterwards to enhance it, but I think you are a better sql guru then me.
I did the sortSearchListQuery and the getProduct and getProductSingle functions. We want to use for getProductSingle a simular method as used in sortSearchListQuery, maybe we can even directly use it.
the sortSearchListQuery can also be enhanced. Atm there are some things doubled, the reason is that we used in the frontend other keywords then in the backend.
So of course, just join the team.

Hi Milbo,
I'm going to have to discuss that with my colleagues to determine how I can fit this into my schedule. It would definitely be beneficial for all of us, but I'll have to let you know.
We also discussed making some enhancements to the default views and contributing those back. Before I can do anything else I need to find some time to get my development environment setup so I can test the latest changes. At first any improvements I make I'll post here so you can test them, then if you're happy with my work you can add me to your team.

I'll definitely help as much as I can.

[zerxx]

Hi!

Any improvements for SQL?
2.0.6 & 2.0.8 are still making too much queries.
Will this be fixed in near future?

Thanks!

[Milbo]

Why do you think that 2.0.8 has too much queries? Do you used it with cache? Do you compared it with other solutions with similar features activated?