VM2 needs more than 800 queries for the frontpage? (btw less than 200 now)

Started by Milbo, November 22, 2011, 21:38:13 PM

Previous topic - Next topic

MikeUK

#15
Quote from: alex.s on November 23, 2011, 14:18:22 PM
Now on the funny side, Max you need to rethink this entire thing man. You cannot be serious that everyone should use a good hosting company. The reality is not in the lab. Your results are only indicative, the real results can only come from the real world which unfortunately is full of crappy, overloaded, unsecure, outdated or bad-configured servers.

I disagree 100%. Let me explain why.

Imagine you are a tyre company making sports tyres. If you put them on a cheap car, they will not perform. You can not expect the sport tyre to change it's compound because of this. If you do that, it is not a sports tyre anymore. If someone buys sports tyres, you can expect them to have a car capable of using them.

E-commerce is a big thing, both from a software perspective and possibly from a business perspective. You simply should not put it on low-grade servers or hosting. Good hosting is simply not that expensive compared with other business costs. Plus if a business relies on e-commerce, the server is the heart of it. I think it is fair to say that someone who can not afford good hosting is probably better off not starting an e-commerce business at that time.

Look at it another way. If, for example, I want to open a regular store, location and the bulding itself are very important, and something I need to pay good money for. Yes, I could find an old garage in a bad location much cheaper, but the business would suffer because of it, and very likely would not work at all.
Get answers faster:
    [li]
Search forum. You might find answer[/li]
[li]Use existing threads. Keep Q + A consolidated[/li]
[li]Troubleshooting? See http://forum.virtuemart.net/index.php?topic=60033.0[/li]
[li]For admin user manual - http://virtuemart.net/documentation/User_Manual/index.html[/li]
[li]For coding (developer manual) - http://virtuemart.net/documentation/Developer_Manual/index.html[/li][/list]

I can build your online shop, setup or customize Virtuemart or help your existing shop maximize its potential. Email / PM for info

alex.s

Milbo:
It is true I believe data more easily than an authority. You may be the best programmer in the world, but as disheartening as it may be, I don't know you. But even if I knew you I would still prefer the data sheets and the data you presented is not enough for me. Please don't take it personal, I am a hard man to convince in general :)

My basic point is that this entire problem is not linear. Your linear approach to a software that runs as an algorithm with many internal and environment variables affecting it is not the appropriate approach.

One reason for my denial is a number of white papers released by Oracle (the current owner of MySQL). If you find some free time to register in mysql.com you will find various white papers that show how performance changes when various variables change. You will see for yourself how chaotic it can be and how hard it is to make a truly objective approach.

Another reason for my denial to your approach is Magento. It is built with all the academic correct ways ever published. The end user experience on performance? Not that good (I am trying to say it polite).

---

MikeUK:
I understand your point and mostly agree. But, try explain that to the majority of Joomla and Virtuemart webmasters. I don't agree 100% because there are some exception cases, like shops with multiple sub-shops that can justify so much money for their entire web exposure.

MikeUK

Quote from: alex.s on November 23, 2011, 23:57:03 PM
MikeUK:
I understand your point and mostly agree. But, try explain that to the majority of Joomla and Virtuemart webmasters. I don't agree 100% because there are some exception cases, like shops with multiple sub-shops that can justify so much money for their entire web exposure.

Yes, very true, and I know you didn't necessarily mean you were an advocate of this. I just think that many webmasters miss the point that the cost is a necessary and fairly minimal one, compared to other business costs and potential losses associated with poor quality hosting. I'd say that is true even for small shops. I mean, for good quality shared hosting (which is good enough for a small shop), we are only talking $10 - $20 per month. It is hard to imagine what online business could not justfify such a cost for something so fundamental.

To the point of your original post, I guess what matters is whether Virtuemart should be sensitive to that or not. I strongly believe it shouldn't, because I think it would mean time spent on something that should be the responsibility of the webmaster. Perhaps one improvement that could be made is the information given as requirements, which perhaps should make more of the fact that a server / hosting not only needs to be good and well setup, but also exceeding Joomla recommendations.

I've had clients who have had their e-coommerce experience transformed, saving masses of stress and time, and potentially lost sales, simply by changing to decent hosting.
Get answers faster:
    [li]
Search forum. You might find answer[/li]
[li]Use existing threads. Keep Q + A consolidated[/li]
[li]Troubleshooting? See http://forum.virtuemart.net/index.php?topic=60033.0[/li]
[li]For admin user manual - http://virtuemart.net/documentation/User_Manual/index.html[/li]
[li]For coding (developer manual) - http://virtuemart.net/documentation/Developer_Manual/index.html[/li][/list]

I can build your online shop, setup or customize Virtuemart or help your existing shop maximize its potential. Email / PM for info

Milbo

Quote from: alex.s on November 23, 2011, 23:57:03 PM
Milbo:
It is true I believe data more easily than an authority.
It is a math thing, that a query more can be sometimes faster ... and a speed difference of 800% is not some measurement error. It is just ridiculous. All this arguments of you are valid, when I would talk about 1% or 2, maybe lets say 5 or 10% difference. But I talk about 800% and now look back in your datasheets of oracle. Or directly ask a guy from there.

And I pointed at begin on the wheatstone bridge, because there is the idea used, that statisticaly the errors level them self. Just look on a carpet, or on a green and you should understand what I mean. Just download the code and test yourself,... it is very easy todo that. As long you did not tested it yourself,.. .and made 20 runs to see 20 times almost the same result,... you wont believe it. So do that first.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

zorkhh

Most of the posts in this thread are somehow right - and i don't want to judge one of the mentioned arguments or results... Of course it makes sense sometimes, to split up complex queries into 2 or 3 simple / cacheable queries. The best example for this is the shop_browse_queries.php of vm 1.1.x. The resulting query can sometimes kill the database... But - also it should not result in hundreds of queries. If so, something in the database design should be changed or at least some caching implemented.

But in real life, there are so many different types of shops, that it is hardly possible to optimize for every possible szenario - some shops have a huge number of nested categories and products, others have a lot of price variations for products and customer types and so on... All of these relations may become complex or produce lots of queries. Pushing all the data into memory is also no option - nobody can estimate the amount of Data has to be handled in memory. An "out of memory" may happen faster than you may think (anybody knows 1&1 shared hosting?).

My personal approach would be to define the structure how the data should be handled by the program / what the program expects from the database and create at least one overwriteable function that holds the queries. This way, the shopbuilder has the chance to optimize critical queries to the need of the shopowner - and even when the shop gets bigger and the number of visitors goes up, there is still a chance to do something.

Regarding the hosting: lots of Joomla users don't even know what quality their hosting is. They are happy that everything is running. When they start to try out an ecommerce system, they don't change their hosting for that. Though i agree 100% that ecommerce needs secure, reliable and fast hosting, reality is different...

Just my 2c...

Thomas
Virtuemart Professional Support at http://www.vm-expert.com

Visit the large Virtuemart Group on Joomla.org: http://people.joomla.org/groups/viewgroup/30-Virtuemart.html

Milbo

#20
Nice words Thomas. You meet directly what I try to explain. The thing which you mean is btw our class vmTable. There is no option to overwrite this file, but it is quite useful for plugins and modules.

Anyway my brain couldnt stop to think about it, because I am sure that I did some mistake with the calculation, because I explained before that it is slower for only one children category.
Btw my sampel data had most time more than 5 children. Sometimes more than ten. And I did not mentioned my final results of for a categorylist. the relation was for my sampel (important!) 1:4.

Edit: I just made some more tests:
Sampeldata, without extra query
0.006
0.0065
0.0067

Sampeldata, with extra query
0.0038
0.0040
0.0039

145 cats without extra query
0.114
0.115
0.113

145 cats with extra query
0.039
0.038
0.038

and if you have around 0.35 pageload for vm or 0.42 s, is in the sum the amount if the page load takes longer then a second or not. Also nice to see is the scaling when more data is in the game. The old solutions seems to get faster slower then the new solution. So Good night
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

stinga

#21
I am not sure what the real problem is here, but J!/VM like most modern CMS's is not going to run very well on a weedy host.
If you can't afford a decent server and they are pretty cheap now then you really should not be running a busy eCommerce site.

Like all generic CMS's, and I have said this before, J!/VM is trying to be all things to all people and this is going to create inefficiencies in the coding due to the way people will want to do stuff.
I am running 1.1.2 because I have hacked the code so much to remove all of the stuff I don't want or need plus alter the code to make it run faster .
Our site used to take forever now it is faster.

So yes, J!/VM probably does need 800 queries to create the front page, but if you start removing the stuff you don't need the the requirement goes down and a lot of querys does not mean a slow site. MYSQL is pretty good a caching querys etc.
DB tuning and code tuning is the key to a fast site.
Stinga.
614869 products in 747 categories with 15749 products in 1 category.
                                             Document Complete   Fully Loaded
                Load Time First Byte Start Render   Time      Requests      Time      Requests
First View     2.470s     0.635s     1.276s          2.470s       31            2.470s      31
Repeat View  1.064s     0.561s     1.100s          1.064s       4             1.221s       4

EdgeCrafter

It might be worth looking at a nested set implementation as future performance enhancement.

I have tried nested set solution several times and the performance gain on read is incredible!

See
http://en.wikipedia.org/wiki/Nested_set_model
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

Trade off is insert of new nodes in hierarchy become more complex

stinga

I don't believe we actually have a problem.
The number of queries to generate a page is not an issue unless you have a weedy host and if you have a weedy host then it is going to be slow.
There is a lot you can do with flat filing stuff, I did this with our huge menu we had on the left, I realised it was being generated for every page that was shown and it it hardly ever changed, so I took the code, a load of js, dumped into a file and called it externally, this cached it in the browser and made a big impact.
There is a lot that could be done like this which would make things quick be would depend on having a scheduler to make it work, which some hosts don't have.
Stinga.
614869 products in 747 categories with 15749 products in 1 category.
                                             Document Complete   Fully Loaded
                Load Time First Byte Start Render   Time      Requests      Time      Requests
First View     2.470s     0.635s     1.276s          2.470s       31            2.470s      31
Repeat View  1.064s     0.561s     1.100s          1.064s       4             1.221s       4

Milbo

We implemented this already using the normal joomla cache. With cache activated your main page with all features activated loads under 20 queries.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Ultima

Quote from: Milbo on November 24, 2011, 03:27:25 AM...
Edit: I just made some more tests:
Sampeldata, without extra query
0.006
0.0065
0.0067

Sampeldata, with extra query
0.0038
0.0040
0.0039

145 cats without extra query
0.114
0.115
0.113

145 cats with extra query
0.039
0.038
0.038
...

Hi Milbo,

I can't really believe this, can I get some more information about your test?
  • Sample data
  • Table informations
  • The used SQL statements
I want to check it by my self. ;D

thx Thomas

Milbo

Go in the BE/helpers/shopfunctions.php to the line 776, there is

if($categoryModel->hasChildren($childId)){

just disable this and it works without extra query. Use

vmSetStartTime('myStartPoint');   and
vmTime('my comment','myStartPoint');

to measure the time.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

lipes

VM Administrators and Moderators: Forget the beginners users that they got here "two days ago" and have only 2 or 7 forum posts ... criticize everything when they appear for the 1st time....
That is what i really call "waste of time". There are much more interesting things to do... like help old users / discussing / debate another good post who actually have good questions for the community and dont go to personal attack.
So if the VM 2.0 has quickly search and queries, i'll consider closed topic. :P
but i'm not the moderator.. xD
VM V. online: J2.5.14 | VM 2.0.20a | SQL 5.1.70 | PHP 5.3.25

balai

QuoteVM Administrators and Moderators: Forget the beginners users that they got here "two days ago" and have only 2 or 7 forum posts ... criticize everything when they appear for the 1st time....
That is what i really call "waste of time". There are much more interesting things to do... like help old users / discussing / debate another good post who actually have good questions for the community and dont go to personal attack.

Since when the quality of a post is related to the number of posts a user has in the past?
Just for your info the person with the 2 posts has got a JOscar for 2011 and has developed the component with the most downloads in the joomla extensions directory ever

lipes

Great. Thanks Balai for your useful information. I didnt know what he is or what he do in the past but it's fine. Like you probably already know many people are jealous of the work that VM developers do, like you are doing (and like i can read above the number of posts). So I give my credits to those who do the work :)
If he had an appointment there I didnt say anything :P But no problem... Peace and Love xD
VM V. online: J2.5.14 | VM 2.0.20a | SQL 5.1.70 | PHP 5.3.25