quote:

---

CREATE PROCEDURE [usp_Company_Search]


@KeywordNear as varchar(250),
@LocationNear as varchar(250) = null,
@PageIndex as int,
@Pagesize as int

AS

BEGIN

DECLARE @tbl TABLE
(
row int,
[Rank] int,
CompanyID int,
CompanyDesc text,
Title nvarchar(150),
Company nvarchar(150),
Category nvarchar(50),
Source nvarchar(50),
URI nvarchar(250),
Location varchar(60),
DateCreated nvarchar(50)
)

IF (@LocationNear is not null) BEGIN

WITH CompanySearch as
(
SELECT ROW_NUMBER() OVER (ORDER BY rs.rank desc) as row,
rs.Rank as [Rank],
J.CompanyID,
J.CompanyDesc,
J.Title,
J.Company,
J.Category,
J.Source,
J.URI,
J.Location,
J.DateCreated
FROM Company J
INNER JOIN
CONTAINSTABLE (Company,RawStripped, @KeywordNear) rs
ON J.Companyid = rs.[KEY] AND
CONTAINS (Location, @LocationNear)
)

insert into @tbl select * from CompanySearch

SELECT
CompanySearch.[Rank],
CompanySearch.CompanyID,
CompanySearch.CompanyDesc,
CompanySearch.Title,
CompanySearch.Company,
CompanySearch.Category,
CompanySearch.Source,
CompanySearch.URI,
CompanySearch.Location,
CompanySearch.DateCreated
FROM @tbl as CompanySearch
WHERE CompanySearch.row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
END
ELSE
BEGIN
WITH CompanySearch as
(
SELECT ROW_NUMBER() OVER (ORDER BY rs.rank desc) as row,
rs.Rank,
J.CompanyID,
J.CompanyDesc,
J.Title,
J.Company,
J.Category,
J.Source,
J.URI,
J.Location,
J.DateCreated
FROM Company J
INNER JOIN
CONTAINSTABLE (Company,RawStripped, @KeywordNear) rs
ON J.Companyid = rs.[KEY]
)

insert into @tbl select * from CompanySearch

SELECT
CompanySearch.Rank,
CompanySearch.CompanyID,
CompanySearch.CompanyDesc,
CompanySearch.Title,
CompanySearch.Company,
CompanySearch.Category,
CompanySearch.Source,
CompanySearch.URI,
CompanySearch.Location,
CompanySearch.DateCreated
FROM @tbl as CompanySearch
WHERE CompanySearch.row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

END

SELECT Max(row) as RecordCount from @tbl
select top 5 title, count(title) as cnt from @tbl group by title order by cnt desc
SELECT top 5 Location, count(location) as cnt from @tbl group by location order by cnt desc
SELECT top 5 Company, count(company) as cnt from @tbl group by company order by cnt desc


END

---

quote:

---

Also try adding a full text index on the columns used for full text search.

---