2
Answers

I want to build smart search (like e-commerce autocomplete)

Kapil Bhati

Kapil Bhati
7y
723
1
Hi,
 
I am working with mvc5 and entity framework. I want to create a smart search on data. i have created this , but it runs very slow. I have created this with autocomplete(jquery). i want to speedup this function. could anyone suggest me, how i speedup this function....
 
thanks.
 
Here is some part of my code
  1. $("#txt_smartsearch").autocomplete({    
  2.            source: function (request, response) {   
  3.                 $.ajax({                     
  4.                     url: "/Home/Search",                 
  5.                           type: "POST",                     
  6.                        dataType: "json",                    
  7.                        data: { txt: request.term },                     
  8.                    beforeSend: function () {                       
  9.                 $('#divresult').hide();                     
  10.                                        },           
  11.             success: function (data) {                        
  12.                var val_sn = $.map(data, function (snm) {      
  13.                          return { label: snm.search, ID: snm.Id, TblId: snm.Tbl_Id };     
  14.                       });                          
  15.          response(val_sn);                    
  16.    },                       
  17. error: function (data) {           
  18.                alert();                     }            
  19.        });             },              
  20.  select: function (event, ui) {         
  21.           var val = ui.item.label;               
  22.     I_RecId = ui.item.ID;                  
  23.   I_tblId = ui.item.TblId;               
  24.       vardata = {                     
  25.     id: I_RecId,                      
  26.    tblid: I_tblId                 }           
  27.        $.getJSON("/Home/SearchResult/", vardata, function (data) {       
  28.                 if (data > 0) {                                         
  29.  $('#result').animateNumber({ number: cnt });        
  30.               }                 });             },     
  31.           minLength: 1,         
  32.   });  
here is urlcode(
url: "/Home/Search")
  1. public JsonResult Search(string txt)  
  2. {  
  3. string loginId = Session["UserId"].ToString();  
  4. int UserId = Convert.ToInt32(loginId);  
  5. string txtval = txt.Trim();  
  6. string[] vali = txtval.Split(' ');  
  7. if (vali.Length > 1)  
  8. {  
  9. txtval = vali[0].ToString() + ' ' + vali[1].ToString();  
  10. }  
  11. var dataval = test.Get_SmartSearch(txtval, UserId).ToList();  
  12. return Json(dataval, JsonRequestBehavior.AllowGet);  
  13. }  
and now my store procedure
  1. ALTER Procedure [dbo].[Get_SmartSearch]  
  2. @txt varchar(50),  
  3. @userId int  
  4. as  
  5. SELECT (CAST(p.Pcode AS varchar(10))+' '+StateName) as search,Id,Tbl_Id from test_PinCode p inner join  
  6. (Select distinct(Pcode) from test_UsersAssignedPcodes Where UserId=@userId) up on p.Pcode=up.Pcode  
  7. Where p.Pcode like @txt+'%'  
  8. union  
  9. SELECT (isnull(Suburb,'') +' '+ps.Pcode+' '+StateName) as search,Id,Tbl_Id FROM test_PcodeSuburb ps  
  10. inner join (Select distinct(Pcode) from ODB_UsersAssignedPcodes Where UserId=@userId) up on ps.Pcode=up.Pcode  
  11. inner join (Select Suburbs from test_UsersAssignedPcodes ap join test_UsersAssignedSuburbs sa on sa.PcodeId=ap.Id Where UserId=@userId)us on ps.Suburb=us.Suburbs  
  12. Where ((Suburb+' '+ps.Pcode) like @txt+'%' or ps.Pcode like @txt+'%')  
  13. union  
  14. Select (isnull(StreetName,'')+' '+isnull(Suburb,'') +' '+CAST(pss.Pcode AS varchar(10)) +' '+StateName) as search,Id,Tbl_Id FROM test_PcodeSuburbStreet pss  
  15. inner join (Select distinct(Pcode) from test_UsersAssignedPcodes Where UserId=@userId) up on pss.Pcode=up.Pcode  
  16. inner join (Select Suburbs from test_UsersAssignedPcodes ap join test_UsersAssignedSuburbs sa on sa.PcodeId=ap.Id Where UserId=@userId)us on pss.Suburb=us.Suburbs  
  17. Where (StreetName+' '+isnull(Suburb,'')+' '+CAST(pss.Pcode AS varchar(10))) like @txt+'%'  
  18. Union  
  19. SELECT (UnitType+' '+isnull(StreetNo,'')+' '+isnull(StreetName,'')+' '+isnull(Suburb,'') +' '+psu.Pcode +' '+StateName) as search,Id,Tbl_Id FROM test_PcodeSuburbStreet_UnitNo psu  
  20. inner join (Select distinct(Pcode) from test_UsersAssignedPcodes Where UserId=@userId) up on psu.Pcode=up.Pcode  
  21. inner join (Select Suburbs from Otest_UsersAssignedPcodes ap join test_UsersAssignedSuburbs sa on sa.PcodeId=ap.Id Where UserId=@userId)us on psu.Suburb=us.Suburbs  
  22. Where (UnitType+' '+isnull(StreetNo,'')+' '+isnull(StreetName,'')+' '+isnull(Suburb,'')+' '+psu.Pcode) like @txt+'%' and @txt like 'unit %'  
  23. )tbl where isnull(tbl.search,'')<>''  
sometime is procedure runs very slow.
any suggestion........
Answers (2)
Next Recommended Forum