Take the 2-minute tour ×
Webmasters Stack Exchange is a question and answer site for pro webmasters. It's 100% free, no registration required.

I have this sam e question on StackOverflow and I was advised to ask it here hoping for more information. Here is the question:

I am in rather unfavorable situation. I have aspdotneststore front e-commerce application and search addon called VibeTrib. I dont have source code for both of those. Store that runs on StoreFront and VibeTrib has close to 250k products. Also we have lots of filters. I spoke to ViTrib reps, and they want extra money so they could optimize Queries that they use. Money they require is nto a big deal, but the problem is I dont trust them anymore. What we got is much different then wha is being advertised.

To cut the long story short. I am runing the store on Amazon AWS now, and regardless of what DB (MsSQL 2012) server I set (I tried 32GB RAM monsters instances) it is slow. Ajax search uses Full Text search and it displays search keywords relatively fast, but once the search is performed ( to display all results) it is still slow.!!!

There is something that I could to do accelerate the speed on my own end? I do have full control over EC2 Instance (Web server Server 2012 and IIS 8). Can I set IIS to step in for the search and cache some of it? I was hoping to cache at least some most common words.

My best bet is IIS 8 :)

Is there any help in my case?

Thanks

share|improve this question
    
What do you mean what you got is not what was advertised? And if their program is shit with queries on sites with a large quantity of catalogs isn't it their job to increase performance of their own software? I would access your database with navicat or some graphical database viewer and determine how much work it would be to write a migration script to get your products, options, and variations into Magento. Probably not an option but I believe Magento on a 32 gig system would fly that plus APC (PHP Caching) would work wonders. Is migrating an option? If money wasn't an issue with optimizing? –  Anagio Dec 5 '12 at 1:16
add comment

2 Answers

up vote 3 down vote accepted

At its root, this is almost certainly a database problem. The first thing to check is the indexes on the tables. If the full-text search is fast and the SELECT is slow, then there are most likely JOINS being performed that could benefit from indexes, possibly even composite indexes.

You will need to profile the application (run a trace using SQL Profiler) and watch the Duration value for the queries. You can even show the query execution plan. The benefit of tuning this way is that you don't need access to the application source; you are just adding indexes to the database. Proper indexing will make all the difference in large datasets.

share|improve this answer
    
Thank you very much for such great advices!! –  Vedran Wex Maricevic Dec 4 '12 at 20:59
    
I agree with the above. The problem might also come from poor configuration of MySQL. Assuming you are using MySQL (it would appear so from what you wrote), you could also try these tools: tools.percona.com . Worst case, if you still think it's a DB problem, you could get a consultancy from those guys which are great with MySQL. (somebodyelse already linked to their site in this thread). Good luck! –  milo5b Dec 4 '12 at 21:33
add comment

You can take steps to optimise the DB. For example, you can have full-text index fields which increases the reponse time for SQL search queries dramatically (particularly when it includes medium - large bodies of text).

Beyond that, You need to have a look at what the problem is. Without access to source code it's difficult but it seems to me that, if the speed is cripplingly slow, then it is probably not very efficient code. It may be making hundreds or even thousands of individual DB queries for small pieces of data instead of grouping them. If you have no ability to modify the source code then you can't do anything to change this though.

So start by looking at the DB structure and trying to make sure it's fully indexed etc. If it is still too slow then you need to either get access to the source code so that you can modify the software yourself or find a different product.

I hope this helps.

Indexing links:

Official Documentation

MySQL Performance Blog

MySQL Optimization

P.S. SQL searches are exremely fast - so trying to have IIS cache results wouldn't necessarily be very productive. If you have a well indexed database then it's probably due to the number of queries as opposed to the time taken to respond to each query. Remember that each new query made by the code has overheads - such as time to establish connection, read/write actions etc.

share|improve this answer
    
Please forgive my ignorance. I have heard about adding indexes to the tables, but I am uncertain how. Could you please provide a place where I could read about it and get myself educated on the matter. I have normal knowledge regarding RDBS for a programmer, nothing fancy like DB Admin. I did run Profiler and oberved execution plan, and indeed there are places that take abnormal amount of time. –  Vedran Wex Maricevic Dec 4 '12 at 21:00
    
@VedranWexMaricevic I've modified my answer to include some links for you to read up on indexing. Just ask if you need anything else. –  Peter Scott Dec 4 '12 at 21:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.