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

Question

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

$("#txt_smartsearch").autocomplete({
           source: function (request, response) {
                $.ajax({
                    url: "/Home/Search",
                          type: "POST",
                       dataType: "json",
                       data: { txt: request.term },
                   beforeSend: function () {
                $('#divresult').hide();
                                       },
            success: function (data) {
               var val_sn = $.map(data, function (snm) {
                         return { label: snm.search, ID: snm.Id, TblId: snm.Tbl_Id };
                      });
         response(val_sn);
   },
error: function (data) {
               alert();                     }
       });             },
 select: function (event, ui) {
          var val = ui.item.label;
    I_RecId = ui.item.ID;
  I_tblId = ui.item.TblId;
      vardata = {
    id: I_RecId,
   tblid: I_tblId                 }
       $.getJSON("/Home/SearchResult/", vardata, function (data) {
                if (data > 0) {
 $('#result').animateNumber({ number: cnt });
              }                 });             },
          minLength: 1,
  });

here is urlcode(

url: "/Home/Search")

public JsonResult Search(string txt)
{
string loginId = Session["UserId"].ToString();
int UserId = Convert.ToInt32(loginId);
string txtval = txt.Trim();
string[] vali = txtval.Split(' ');
if (vali.Length > 1)
{
txtval = vali[0].ToString() + ' ' + vali[1].ToString();
}
var dataval = test.Get_SmartSearch(txtval, UserId).ToList();
return Json(dataval, JsonRequestBehavior.AllowGet);
}

and now my store procedure

ALTER Procedure [dbo].[Get_SmartSearch]
@txt varchar(50),
@userId int
as
SELECT (CAST(p.Pcode AS varchar(10))+' '+StateName) as search,Id,Tbl_Id from test_PinCode p inner join
(Select distinct(Pcode) from test_UsersAssignedPcodes Where UserId=@userId) up on p.Pcode=up.Pcode
Where p.Pcode like @txt+'%'
union
SELECT (isnull(Suburb,'') +' '+ps.Pcode+' '+StateName) as search,Id,Tbl_Id FROM test_PcodeSuburb ps
inner join (Select distinct(Pcode) from ODB_UsersAssignedPcodes Where UserId=@userId) up on ps.Pcode=up.Pcode
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
Where ((Suburb+' '+ps.Pcode) like @txt+'%' or ps.Pcode like @txt+'%')
union
Select (isnull(StreetName,'')+' '+isnull(Suburb,'') +' '+CAST(pss.Pcode AS varchar(10)) +' '+StateName) as search,Id,Tbl_Id FROM test_PcodeSuburbStreet pss
inner join (Select distinct(Pcode) from test_UsersAssignedPcodes Where UserId=@userId) up on pss.Pcode=up.Pcode
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
Where (StreetName+' '+isnull(Suburb,'')+' '+CAST(pss.Pcode AS varchar(10))) like @txt+'%'
Union
SELECT (UnitType+' '+isnull(StreetNo,'')+' '+isnull(StreetName,'')+' '+isnull(Suburb,'') +' '+psu.Pcode +' '+StateName) as search,Id,Tbl_Id FROM test_PcodeSuburbStreet_UnitNo psu
inner join (Select distinct(Pcode) from test_UsersAssignedPcodes Where UserId=@userId) up on psu.Pcode=up.Pcode
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
Where (UnitType+' '+isnull(StreetNo,'')+' '+isnull(StreetName,'')+' '+isnull(Suburb,'')+' '+psu.Pcode) like @txt+'%' and @txt like 'unit %'
)tbl where isnull(tbl.search,'')<>''

sometime is procedure runs very slow.

any suggestion........