Page 1 of 2
normal to have 1000's of db queries/page in ecommerce app?
Posted: Tue Jun 29, 2004 5:38 am
by SRC
I'm in the process of developing an ecommerce site using a shopping cart software package that I purchased. I have discovered that the page generation speed gets progressively slower as more items are added to a customer's cart. Upon some investigating I discovered that for each page view there is anywhere from 500 to over 10,000 database queries. I enabled the mysql query logging feature to find this out. It turns out that for each page view there are several queries that get repeated hundreds or thousands of times. This seems like a terribly inefficient design to me but I'm not an expert on php applications. So my question is whether or not this kind of behavior is considered normal?
As some background I have a pretty powerful VPS server which is very lightly loaded. For the pages which generate thousands of db queries the cpu usage spikes up to 20-80% from near 0%. I experimented with switching mysql's query_cache_type to "ON" but that didn't help and the vendor recommends that I set it to "DEMAND".
Just wondering if you guys could give me some feedback so that I have some leverage (or lack thereof) in talking to the software vendor about this problem. Also if there are suggestions on the best way to tackle this problem on my end that would be great. I tried implementing ADODB for its caching feature but haven't gotten it to work yet. I'm also interested in checking out PEAR's cache_lite and Turck MMCache.
Posted: Tue Jun 29, 2004 5:55 am
by JayBird
You have any idea what it is actually doing when executing all the 10,000 DB queries??
What software did you purchase?
Sounds like very bad design to me.
Mark
Posted: Tue Jun 29, 2004 6:04 am
by Grim...
The straight answer is: No.
Posted: Tue Jun 29, 2004 6:14 am
by patrikG
Which software are you using? 1000s of db-queries must be pretty much the worst-case scenario - surely the worst I've heard. Adding caching will help somewhat, but it's like swallowing pain killers when you've broken both legs.
If this is commercial software it sounds like it's a disgrace. There are many open source eCommerce solutions available (e.g. osCommerce, X-Cart, Zen Cart, phpShop etc.) which do a good job at that, most probably much more efficient than that package you are using.
Posted: Tue Jun 29, 2004 7:34 am
by McGruff
From your description the app is not fit for the purpose for which it was sold in which case you should be entitled to a full refund.
Since this is ecommerce, and I assume you'll be accepting credit cards, any signs of bad programming anywhere cast serious doubts about the package as a whole. You can't afford to take any chances.
It sounds so bad I don't know if it's even worth wasting your time trying to investigate & debug.
Posted: Tue Jun 29, 2004 9:14 am
by Findus
Ask for a refund.
I cant possible imagine anywhere you would need to run 10000 queries in one application. Well, unless it was a query speed tester or something.
Posted: Tue Jun 29, 2004 3:29 pm
by SRC
Thanks everyone for your feedback and the sanity check. The good news is that the vendor has finally agreed that there is an issue with the design and they are working to correct it. The problem lies in one of the optional modules that I paid them to customize for me. When I disable that module everything is fine. The software is Lite Commerce which is a new product from the company that makes X-cart. I must say that the software is very feature rich, very easy to customize, and very elegantly designed (aside from the problem that I mentioned). I did a lot of research before selecting this software and found it to be far and away the best product in its class for my purposes.
I'd like to say a couple things about the vendor's support since my initial post was a little negative. Overall they have been excellent. In fact they have gone above and beyond the call of duty to help me whenever I have asked. In this situation I think they didn't originally realize the problem because when they tested my site everything looked fine because the server was so lightly loaded at the time.
I bought that too
Posted: Tue Jun 29, 2004 11:48 pm
by EricS
I happened to buy that application as well and returned it for very different but equally important reason.
Just my 2 cents.
Posted: Wed Jun 30, 2004 1:22 am
by patrikG
I'd be interested in what your reasons for returning it were.
Posted: Wed Jun 30, 2004 9:40 am
by EricS
I purchased Lite Commerce 1.2.1 and it ended up taking about a full week to get it installed.
First of all it requires IonLoader to run, they think their code is so brilliant that they've compiled a portion of it so no one can see the source for it, even though it says on their homepage that it's Open Source.
Next, the reason it took a full week to install was that the support was on the other side of the world in Russia. We would have to wait an entire day to feed back on the next problem we ran into when installing. We finally ended up giving them a shell account and ftp account let them fix it themselves.
After it was FINALLY installed, by them mind you, it was EXTREMELY slow. Each page view was measured in seconds not hundredths and thousands of a second. It was also unstable, crashing randomly and often.
We were under a quick deadline, which was why we opted to buy a cart rather than have me build a custom one like I usually do, and we had burned over a week on installation and then gave them around 3 or 4 days to get the cart running a reasonable speed and that never happened, so I told them to stick it and asked for our companies money back. I ended up using OS Commerce on that particular project in the end and was glad I did.
Posted: Wed Jun 30, 2004 9:47 am
by patrikG
Thanks a lot for the feedback, EricS! I am currently evaluating different eCommerce packages (focus is on osCommerce & Zen Cart). I'll have to integrate it into a larger, purely OO framework and, having looked at the osCommerce-code, I would be very hesitant to use it. It works, but the coding is bog-standard ugly from an OO point of view. I haven't been able to evaluate Zen Cart properly, yet, but am hoping that it has some form of API and not just single pages that are being called (and I don't want to end up with installing ezPublish - which is great, but very resource-intensive).
Posted: Fri Jul 09, 2004 11:58 am
by bg
patrikG wrote:Thanks a lot for the feedback, EricS! I am currently evaluating different eCommerce packages (focus is on osCommerce & Zen Cart). I'll have to integrate it into a larger, purely OO framework and, having looked at the osCommerce-code, I would be very hesitant to use it. It works, but the coding is bog-standard ugly from an OO point of view. I haven't been able to evaluate Zen Cart properly, yet, but am hoping that it has some form of API and not just single pages that are being called (and I don't want to end up with installing ezPublish - which is great, but very resource-intensive).
Ive been working with Zen Cart lately and ive noticed it has quite a few bugs. I installed the software on our servers and out of the blue I get sql query errors on certain pages. Installed it on a different server with the db running on our server (not localhost) which seemed to bring along a host of new issues as well. When working in the admin section, ill randomly be kicked out to the admin login screen. I had to reinstall it once because accessing the store resulted in no output, a common problem it seems with zen cart, but none of the fixes worked for me. There is a new version on the horizon however (1.2 i believe) which introduces a new db abstraction layer which I believe to be the cause for a lot of my problems.
Posted: Fri Jul 09, 2004 1:03 pm
by m3mn0n
patrikG wrote:Thanks a lot for the feedback, EricS! I am currently evaluating different eCommerce packages (focus is on osCommerce & Zen Cart). I'll have to integrate it into a larger, purely OO framework and, having looked at the osCommerce-code, I would be very hesitant to use it. It works, but the coding is bog-standard ugly from an OO point of view. I haven't been able to evaluate Zen Cart properly, yet, but am hoping that it has some form of API and not just single pages that are being called (and I don't want to end up with installing ezPublish - which is great, but very resource-intensive).
If you didn't like osCommerce, there isn't a great chance you'll enjoy Zen Cart. Who knows though, you just might like it.
My $0.02 from my experiences with them both.
Posted: Wed Jul 14, 2004 3:04 pm
by BDKR
If you ask me, based on what EricS and SRC have experienced, it sounds like they are trying to store the data in some sort of object hiearchy. This can be made to work, but the number of queries required (All of those queries are the system 'traversing the tree' so to speak) is astronomical. What it spits out is an extremely flexible way of looking at and dealing with the data.
The last company I worked for had worked to 2+ years trying to make a RAD setup based on this very premise. PHP on top of MySQL (with a lot of IE specific ECMA magic thrown in). With small datasets the performance was fine, and of course, it really made development exceptionally easy. HOWEVER, once the size of the data set grew, it became exceptionally slow. In the span of one months testing, the system logged over 2.5 million queries! And this was only with two developers hitting it!
Essentially, the problem is that Relational DB's (like MySQL) store data differently than OR(Object Relational)DBM's or O(Object)DBM's . A relational table structure vs a hierchal tree like structure. And the overhead associated with making MySQL (as an example) work this way more often than not results in this huge overhead.
They prolly should've used a dedicated O(R)DBMS with an in memory caching scheme. Another possible scenario is to have a script (or whatever) that constantly chews on the data and generates arrays (since PHP doesn't have built in support for Trees) that represent that data in hiearchal structures and then perhaps stores them (serialzed) in a session or a table in the db. Just grab it and unserialize it. I tested a scenario like this for my old employers and showed how it was over a thousand times faster than yanking it out of the db!
Anyways, I could be wrong too and there is some other reason that thing is so slow. So sorry for going off there.
Cheers,
BDKR
Posted: Wed Jul 14, 2004 3:07 pm
by BDKR
Err..., I'm sorry,

..., but for those that may be interested, there is also OLAP.
http://en.wikipedia.org/wiki/OLAP
Cheers,
BDKR