Product search - bottleneck to site performanc

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   I am using PC for more than 10,000 product skus. I find that that PC can run on a fairly rudimentary hosted platform with relatively good user experience and responsiveness. (I have for a number of years moved all product thumbnail and main images to a separate server - for better performance).

Long ago - I reduced the standard search to only run the SQL against the sku and title fields - however this still gives slow query results depending on the number of hits. For example a search returning 1000 value will take around 105s for the asp to run the asp/query and about 6s to serve the thumbnail images. The user experience for such a query is very bad!

I use SQL 2008 on at least one of the sites and was wondering if anyone has had either :
good experiencing with modifying the standard PC code for using indexed SQL queries (e.g. http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/)
or whether anyone has found a better third party solution that integrates well with PC?
Hope you can help

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
geoff Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 15-January-2006 Status: Offline Points: 2	Post Options Post Reply Quote geoff Report Post Thanks(0) Quote Reply Topic: Product search - bottleneck to site performanc Posted: 25-November-2010 at 2:25pm
	I am using PC for more than 10,000 product skus. I find that that PC can run on a fairly rudimentary hosted platform with relatively good user experience and responsiveness. (I have for a number of years moved all product thumbnail and main images to a separate server - for better performance). Long ago - I reduced the standard search to only run the SQL against the sku and title fields - however this still gives slow query results depending on the number of hits. For example a search returning 1000 value will take around 105s for the asp to run the asp/query and about 6s to serve the thumbnail images. The user experience for such a query is very bad! I use SQL 2008 on at least one of the sites and was wondering if anyone has had either : good experiencing with modifying the standard PC code for using indexed SQL queries (e.g. http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/) or whether anyone has found a better third party solution that integrates well with PC? Hope you can help

ProductCart Members Profile Send Private Message Find Members Posts Add to Buddy List Admin Group ProductCart Team Joined: 01-October-2003 Status: Offline Points: 135	Post Options Post Reply Quote ProductCart Report Post Thanks(0) Quote Reply Posted: 25-November-2010 at 4:07pm
	You should not return 1,000 results. You should limit the number of results returned by the system. This will dramatically increase performance. To do so, go into "Settings > Store Settings > Search Settings" and set the maximum amount of results to be returned. As far as the source code is concerned: we are always looking for ways to increase performance. Performance in the search feature has been increased with each new release and I know that even recently our development team was making some code changes to introduce new improvements (I believe these were specifically tarted to stores using the Apparel Add-on). Additional performance improvements could be achieved by abandoning support for MS Access and focusing exclusively on optimizing for MS SQL, which is something that is currently under consideration.
	The ProductCart Team Home of ProductCart shopping cart software

geoff Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 15-January-2006 Status: Offline Points: 2	Post Options Post Reply Quote geoff Report Post Thanks(0) Quote Reply Posted: 25-November-2010 at 8:40pm
	Thank you for your prompt response - I agree that limiting the results will dramatically improve performance. However - in search examples where the requirement is to list all 1000+ items within for example a brand - it is not practical to limit the number of results returned. In the past the only work around - I developed was that for the particular searches for my need that required this - I served up static pre-sorted html. But a dynamic solution - particularly using an indexed solution seems the way to go - IMO.

geoff Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 15-January-2006 Status: Offline Points: 2	Post Options Post Reply Quote geoff Report Post Thanks(0) Quote Reply Posted: 12-December-2010 at 6:51pm
	As a continuation on this theme - is anyone else interested in requesting the expansion of the limited search capabilities within PC 4.1? Would YOU like to comment on their experience with using indexed product search (SQL 2008) with spelling correction, Google custom search, or third party search support? Geoff Edited by geoff - 12-December-2010 at 6:51pm