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

Kapil Bhati

9y989SQL

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........

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
I have modified your query to certain extent. Please go through it and see if it helps.
(I am assuming that you are taking care of indexes).

Key points are: If possible, avoid using sub queries ( it doesn't help optimizer to determine to work faster, instead try putting data sets into temp tables- for which you know that it is going to be fixed length). In your case, you are querying same table in multiple subqueries. So, took it out. Also, use concat function to avoid casting (function does that internally for you). One last thing, (if possible), try to avoid any logical functions on strings in where clause, it may slowdown query a bit- for the same reason, I took out search pattern in variable outside from each where clause.
ALTER Procedure [dbo].[Get_SmartSearch]
@txt varchar(50),
@userId int
as
DECLARE @SearchPattern varchar(55) = CONCAT(@txt,'%')
Select distinct(Pcode) Pcode into #tempPCodes from test_UsersAssignedPcodes Where UserId=@userId
Select Suburbs into #tempSuburbs from test_UsersAssignedPcodes ap join test_UsersAssignedSuburbs sa on sa.PcodeId=ap.Id Where UserId=@userId
Select distinct(Pcode) Pcode into #tempODBPCodes from ODB_UsersAssignedPcodes Where UserId=@userId
SELECT CONCAT(p.Pcode,' ',StateName) as search,Id,Tbl_Id
from test_PinCode p
inner join #tempPCodes up on p.Pcode=up.Pcode
Where p.Pcode like @SearchPattern
union ---
SELECT (isnull(Suburb,'') +' '+ps.Pcode+' '+StateName) as search,Id,Tbl_Id
FROM test_PcodeSuburb ps
inner join #tempODBPCodes up on ps.Pcode=up.Pcode
inner join #tempSuburbs us on ps.Suburb=us.Suburbs
Where ((Suburb+' '+ps.Pcode) like @SearchPattern or ps.Pcode like @SearchPattern)
union ---
Select concat(isnull(StreetName,''),' ',isnull(Suburb,'') ,' ',pss.Pcode ,' ',StateName) as search,Id,Tbl_Id
FROM test_PcodeSuburbStreet pss
inner join #tempPCodes up on pss.Pcode=up.Pcode
inner join #tempSuburbs us on pss.Suburb=us.Suburbs
Where concat(StreetName,' ',isnull(Suburb,''),' ',pss.Pcode) like @SearchPattern
Union---
SELECT (UnitType+' '+isnull(StreetNo,'')+' '+isnull(StreetName,'')+' '+isnull(psu.Suburb,'') +' '+psu.Pcode +' '+StateName) as search,Id,Tbl_Id
FROM test_PcodeSuburbStreet_UnitNo psu
inner join #tempPCodes up on psu.Pcode=up.Pcode
inner join Otest_UsersAssignedPcodes us on psu.Suburb=us.Suburbs
inner join test_UsersAssignedSuburbs sa on sa.PcodeId=us.Id and UserId=@userId
Where (UnitType+' '+isnull(StreetNo,'')+' '+isnull(StreetName,'')+' '+isnull(Suburb,'')+' '+psu.Pcode) like @SearchPattern and @txt like 'unit %'
)tbl where isnull(tbl.search,'')<>''

DROP TABLE #tempODBPCodes
DROP TABLE #tempPCodes
DROP TABLE #tempSuburbs
0
Manas Mohapatra9y ago
Follow below steps that may help you:
Use UnionALL instead of Union. Make sure you will not have duplicate records.
Use non-clustered index in filtered columns.
Use parameter sniffing
Use WITH (RECOMPILE) in stored procedure
See execution plan:
      SET SHOWPLAN_TEXT ON
      GO
      Select 'query here'
      GO
      SET SHOWPLAN_TEXT OFF

Try to work to improve performance on wild card(%abc%)

https://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/

https://social.technet.microsoft.com/wiki/contents/articles/31238.t-sql-improve-the-performance-for-like-wildcard-by-changing-the-collation.aspx

Atlast you sqlserver profiler and then tuning advisor to see the loopholes where it is causing the issue.

https://www.mssqltips.com/sqlservertip/4318/sql-server-stored-procedure-runs-fast-in-ssms-and-slow-in-application/
0