2

I have an ASP.Net MVC 5 website. One of my controllers has a Search action which searches the list of shops in the database.

I want to somehow keep track of latest search queries and show them to the users in another page. What comes to my mind is:

  1. Most straightforward way would be to save the queries in the database in the search action: I think it's not a good idea to hit the database for every single query.
  2. Saving the log for the search action and then parsing it and showing it to the user. It seems a little dirty to me!
  3. Save the queries in the cache and push them every couple of seconds or minutes to the database. This seems like the best option for me. Currently I'm using this for page hits. I increment the page's hit every time, save it to the cache and save the result to the DB.

Is there a better way? Which approach you'd suggest?

Alireza Noori
  • 14,961
  • 30
  • 95
  • 179
  • Which database server are you using? For how long do you want to keep the latest search information and how many search records? – juanreyesv Jan 15 '14 at 23:31
  • I'm using SQL Server 2008. Also I'm using entity framework code first. I want the latest 50 queries. – Alireza Noori Jan 16 '14 at 00:21

1 Answers1

1

Have you considered storing the keywords of the searches on the client side? (HTML5 Web Storage localStorage or sessionStorage depending on your requirements).

Then, for example, you could build a list of links to the actual search action method of your controller, if the user clicks it, the search will be executed again since the results may have changed.

If you really want to store the search queries on the database I would suggest using a stored procedure (Does Entity Framework Code First support stored procedures?) which could contain both the historic insertion and the actual search.

Hope this could help you

Stored procedure Example:

CREATE PROCEDURE uspGetShopsByName
                 @Keyword nvarchar(30) 
   AS
      -- Store the search in the history table
      INSERT INTO [dbo].[SearchHistory] ([Keyword]) VALUES (@Keyword);
      -- Execute the search
      SELECT * FROM [dbo].[Shops] WHERE [ShopName] LIKE @Keyword + '%' 
GO
Community
  • 1
  • 1
juanreyesv
  • 853
  • 9
  • 22
  • Thank you for the answer. I'm not looking to show the users *their* queries on the website. I want to show them what others have been looking for. Where do you think I should use the stored procedure? Because the main reason I asked this question is to minimize the overhead on the database. – Alireza Noori Jan 16 '14 at 03:19
  • I see.. I think you could create a store procedure to do the search and inside that stored procedure, prior to the search being executed, you could do an `INSERT` to save the search on a "search_history" table – juanreyesv Jan 16 '14 at 05:13
  • I'm confused, where should I store those 50 searches **before** storing them on the database then? – Alireza Noori Jan 17 '14 at 15:57
  • What I meant is that the same stored procedure could store the search prior to execute it. I have updated my answer to show an example of the stored procedure. You could even add more logic to the stored procedure to only insert a maximum of 50 searches on the SearchHistory Table. – juanreyesv Jan 18 '14 at 08:51
  • Thank you very much for your updated answer. But there's a chance that I want to show the previous searches on a different page. That way there's no query to `INSERT`. How about that scenario? – Alireza Noori Jan 18 '14 at 10:21
  • No worries. To show the previous searches on a different page you just have to query the `SearchHistory` table for the last records. In that table you could have a column to store the date when the search was made and order the results by that column (`DESC`). Does that makes any sense? – juanreyesv Jan 19 '14 at 11:16
  • In that case I would hit the DB (for `INSERT`) for every query, right? Wouldn't that be inefficient? – Alireza Noori Jan 19 '14 at 20:29
  • Yes, it will it the DB but you're already hitting the DB to run the query. Doing both operations in the same stored procedure saves you one round-trip to the database from your application which makes it more efficient. Either way you will have to make an `INSERT` to store the search, there is no escape to that if you want to store it on the DB. If you want to avoid that, maybe consider using some sort of Cache (memcached or some other) to store the searches there... That is why I mentioned that if you really want to store the searches on the DB, consider using the approach stated on my answer – juanreyesv Jan 20 '14 at 06:34
  • Hi. Thanks again for taking the time to answer me. The problem is, I want to enable cache on this controller so I'm really not hitting the DB every time. I just hit it once in a while. On the other hand I want to record every query. So you think going with cache is the best option? – Alireza Noori Jan 20 '14 at 07:31
  • No worries. What type of cache are you planing to implement in your controller? – juanreyesv Jan 21 '14 at 03:54