Help with optimizing stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with optimizing stored procedure

Author

Topic

n3tx
Starting Member

3 Posts

Posted - 2009-11-10 : 19:46:15

Hey!
I Need help with optimizing my sp.
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]
@KeyWhat varchar(150) = NULL, -- what to search for
@KeyWhere varchar(150) = NULL, -- where to search
@PageSize int = 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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-11 : 10:47:46

do you have indexes on following

Companies field ID and Activity_ID
Addresses field Company_ID
PhoneNumbers field Company_ID
Activities field ID

also do you have index on
C.[Name]
C.Orgnumber
p.Phone
act.[Name]
a.address
a.zipcode
a.county
also try an index on rownumber for the temp tables
it would help a lot to have indexes on these.
BOL says :
With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:
Can be used for frequently used queries.
Provide a high degree of uniqueness.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources