3 Replies - 518 Views - Last Post: 23 July 2018 - 06:55 AM Rate Topic: -----

#1 mishaq   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-July 18

Website Fast Search Functionality For 2 Million Products

Posted 23 July 2018 - 04:46 AM

I have an Ecommerce/Online Shopping site with around 2 million items(MS SQL Server DB). It has various products and i want to implement a search functionality for it. The search could be anything like "White shirt" , "Black Shoes", "Leather Belt" so on.

How do i implement the search mechanism ? The site is expected to grow in years.

I know solutions like SQL free-text, Full-text search.

but which one would be appropriate for the scenario. You know people search weird things ?

CURRENT PROBLEM: If a user search "White Shirt" than I search this item from database 5 to 6 times. First search will load the results in website products list. Other searches will get the item count for different search filters.

1. First search will get the item for website search body.
2. Second search will get the counts for discount filter like 5%, 10% or 50% count.
3. Third search will get the counts for category filter like A category, B category and C category count.
4. Fourth search will get the counts for price range. like 0 to 10, 11 to 20 and 21 to 30 range counts.

What is the best** solution to perform this kind of search with minimum database calls in efficient way?

Is This A Good Question/Topic? 0
  • +

Replies To: Website Fast Search Functionality For 2 Million Products

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: Website Fast Search Functionality For 2 Million Products

Posted 23 July 2018 - 06:43 AM

Seems reasonable that each of those calls are doing different things over just getting items.

The not-so-obvious option is would be to encode search terms as numbers (varchar search is a tick more expensive than number).. Extrapolate on that and have a 'tagging' table where an item id matches to one-to-many numbers that are the encoded terms.
Was This Post Helpful? 0
  • +
  • -

#3 mishaq   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-July 18

Re: Website Fast Search Functionality For 2 Million Products

Posted 23 July 2018 - 06:52 AM

View Postmodi123_1, on 23 July 2018 - 06:43 AM, said:

Seems reasonable that each of those calls are doing different things over just getting items.

The not-so-obvious option is would be to encode search terms as numbers (varchar search is a tick more expensive than number).. Extrapolate on that and have a 'tagging' table where an item id matches to one-to-many numbers that are the encoded terms.


Thanks for your reply. Yeah it look obvious to me that I need to query/call database multiple times for different filters. But 2nd issue is that my SQL server has taking high cpu usage. My db server is very powerful and using 24 core but sometimes its taking/using high cpu usage.

As I checked using profiler and othe techniques I analyze this my search stored procedures using high cpu.

Any advise how to reduce hight cpu usage.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: Website Fast Search Functionality For 2 Million Products

Posted 23 July 2018 - 06:55 AM

It's hard to guess without knowing the tables, table optimizations, keys, and queries.

You can use things like viewing the 'execution plan' and 'view statistics' to help pin point choke points.

https://docs.microso...sql-server-2017
https://www.red-gate...on-plan-basics/
https://www.mssqltip...xecution-plans/
https://docs.microso...sql-server-2017
https://www.mssqltip...tistics-output/
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1