November 10, 2009 at 5:44 pm
The sp works fine, but it takes away long time to load. Like 30 sec, that is toooo much.
So I would apreciate any help.
Here's the sp:
ALTER PROCEDURE [dbo].[sp_GetCompaniesByKeywords]
@KeyWhatvarchar(150)= NULL,-- what to search for
@KeyWherevarchar(150)= NULL,-- where to search
@PageSizeint= NULL,-- page size
@PageNumber int= NULL-- current page
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @RowStart int
DECLARE @RowEND int
IF (@PageNumber > 0)
BEGIN
IF (@PageNumber <=1)
SET @RowEND = @RowStart + @PageSize +1
SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber+1
SET @RowEND = @RowStart + @PageSize - 1;
DECLARE @sql nvarchar(4000)
SELECT @sql =
'Select DISTINCT ROW_NUMBER() OVER (ORDER BY C.[Name]) AS RowNumber, COUNT(*) OVER() AS Count,
c.ID, C.[Name] AS CompanyName, C.Orgnumber AS orgnr,
a.Address, a.zipcode, a.county,
p.Phone,
act.[Name] AS Activity
FROM Companies as C
JOIN Addresses AS a ON a.Company_ID = C.ID
JOIN PhoneNumbers AS p ON p.Company_ID = C.ID
JOIN Activities AS act ON act.ID = C.Activity_ID
WHERE 1=1'
IF (@KeyWhat IS NOT NULL AND @KeyWhat <> '')
SELECT @sql = @sql + ' AND (C.[Name] LIKE ' + quotename('%' + @KeyWhat + '%', '''') +
' OR (C.Orgnumber LIKE ' + quotename(@KeyWhat + '%', '''') +
' OR p.Phone = ' + quotename(@KeyWhat, '''') +
' OR act.[Name] LIKE ' + quotename('%' + @KeyWhat + '%', '''') + '))'
IF (@KeyWhere IS NOT NULL AND @KeyWhere <> '')
SELECT @sql = @sql + ' AND (a.address LIKE ' + quotename('%' + @KeyWhere + '%','''') +
' OR (a.zipcode LIKE ' + quotename('%' + @KeyWhere + '%','''') +
' OR a.county LIKE ' + quotename('%' + @KeyWhere + '%','''') + '))'
--PRINT @sql
CREATE TABLE #Result
(
RowNumber int,
[Count] int,
id int,
companyName varchar(150),
orgnumber varchar(20),
address varchar(150),
zipcode varchar(5),
county varchar(20),
phone varchar(20),
activity varchar(100)
)
INSERT INTO #Result(rownumber, [count], id, companyname, orgnumber, address, zipcode, county, phone, activity)
EXEC sp_executeSQL @sql
SELECT * From #Result WHERE RowNumber Between @RowStart and @RowEND
DROP Table #Result
END
END
November 10, 2009 at 6:24 pm
the problem is you have a "do all find all" procedure. it has tha couple of problems:
multiple OR statements.
LIKE statements that start with a percent sign.
with multiple OR statement, the plan really has no choice but to scan the whole table in order to test multiple fields.
instead, you should try to do one of two things: make the client side create the SQL statement, or break up the proc into little procs that search for one thing. it's very.. easy to construct a sql statement on the client side, and NOT include stuff that was not used for the search criteria. if the user entered a single term to search, the execution plan would be much better than the plan that gets bilt now that says stuff like OR ZIPCODE LIKE '%%' because they did not search by that.
the second issue is how you use LIKE statements int he WHERE clause:
YOURFIELD LIKE '%SOMEPHRASE%'
if the like starts with a percent, it must use a table scan...so even if an index exists, it can't use it. if you use like to find stuff that starts with a pharase, ie YOURFIELD LIKE 'SOMEPHRASE%' than it can sue an index, if it exists.
Lowell
November 10, 2009 at 8:47 pm
Hello Lowell,
Thank your for your thoughts.
Here's some swedish websites you can check to see what I am trying to do.
both sites are using two conditions, condition what and where and they can be combined as well.
both sites have conditions like:
what = name, activity, phone or what ever keyword
where = street address, zipcode, county, area code
So it is possible to get this thing to work.
But the problem is how ?
// Daniel
November 11, 2009 at 1:31 am
First off read up on Sql injection, you may well of left youself wide open to an attack.
Secondly to repeat Lowel, the main issue is with the amount of table scanning SQLServer will have to do to satisfy the query. Indexing cannot be used. Think about if i asked you to find all the surnames in the phone booked that contained the string 'ant'. You would have to individually check each one one by one. Same thing for SQLServer.
Im assuming that you have checked the obvious than all the tables involved have indexes on their foreign keys, and that statistics are upto date.
You may find this thread interesting as well
http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx
November 11, 2009 at 5:14 am
at the very least, you can get rid of the OR statements, correct?
it looks like your application picks two search terms, but your current WHERE statement you build searches 8 terms no matter what they selected.
second, some of the terms, should be using starts with LIKE statements instead. zipcode should be an exact match, right? silly to do a like on zip.
here's my suggestion, where the WHERE statement would contain only 0,1 or two AND statements after the WHERE 1 =1 :
DECLARE @sql nvarchar(4000)
SELECT @sql =
'Select DISTINCT
ROW_NUMBER() OVER (ORDER BY C.[Name]) AS RowNumber,
COUNT(*) OVER() AS Count,
c.ID,
C.[Name] AS CompanyName,
C.Orgnumber AS orgnr,
a.Address,
a.zipcode,
a.county,
p.Phone,
act.[Name] AS Activity
FROM Companies as C
JOIN Addresses AS a ON a.Company_ID = C.ID
JOIN PhoneNumbers AS p ON p.Company_ID = C.ID
JOIN Activities AS act ON act.ID = C.Activity_ID
WHERE 1=1'
IF UPPER(@KeyWhat = 'NAME')
SELECT @sql = @sql + ' AND C.[Name] LIKE ' + quotename(@KeyWhat + '%', '''')
IF UPPER(@KeyWhat = 'ORGNUMBER')
SELECT @sql = @sql +' AND C.Orgnumber LIKE ' + quotename(@KeyWhat + '%', '''')
IF UPPER(@KeyWhat = 'PHONE')
SELECT @sql = @sql + ' AND p.Phone = ' + quotename(@KeyWhat, '''')
IF UPPER(@KeyWhat = 'ACTIVITY')
SELECT @sql = @sql +' AND act.[Name] LIKE ' + quotename(@KeyWhat + '%', '''')
IF UPPER(@KeyWhere= 'ADDRESS')
SELECT @sql = @sql + ' AND a.address LIKE ' + quotename('%' + @KeyWhere + '%','''')
IF UPPER(@KeyWhere= 'ZIPCODE')
SELECT @sql = @sql + 'AND a.zipcode = ' + quotename( @KeyWhere '''')
IF UPPER(@KeyWhere= 'COUNTY')
SELECT @sql = @sql + ' AND a.county LIKE ' + quotename( @KeyWhere + '%','''')
Lowell
November 11, 2009 at 5:20 am
oops sorry i misread your code...i thought @KeyWhat was which field,@keywhere was the value...i updated my code, but it's not what you wanted...except if they only use one of the two search terms.
do you have indexes on those 8 columns you are searching?
Lowell
November 11, 2009 at 7:29 am
Hello Lowell,
Yes I have put indexes on all columns used in the query. So i'm down to 0.35sec
That is still way to long time.
Yes, there are two search terms, that can be combined so I have to search on all columns for whatever keyword they have sent. That's why there are sooo many LIKE statements.
So I guess the code sample you supplied will not work, as you said.
I tried to search for
keyWhat = media
keyWhere = stockholm
on http://www.allabolag.se and it maybe took 1sek
They are using the same kind of conditions, with alot of LIKE statements, i guess.
I have tried to put half words and numbers, and it still cames upp with right information.
When I search for 'daniel' they using '%daniel%' becuase I get results like
Daniel Måleri firma,
Magnus & Daniel Livsmedel AB,
and so on ..
November 13, 2009 at 12:07 pm
Or maybe they are using something like:
C.Name CONTAINS ....
wkr,
Eddy
November 14, 2009 at 2:00 am
As eddy has suggested above, have you tried using full text searching ?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply