Structured query vs Full Search Query

[gagsbd]

Hello Everyone,

I have a table with the below fields

CategoryId (int)
Code (varchar)
City (varchar)
State (varchar)
Zip (varchar)
DocumnetText (text)

I am developing a web application, where user can search on any combination of the fileds. DocumnetText is searched on keywords.


Where condition in my stored procedure looks some thing like this

WHERE
((@CategoryId is Null) or (CategoryId = @CategoryId ))
AND
((@Code is Null) or (Code = @Code ))
AND
((@City is Null) or (City = @City ))
AND
((@State is Null) or (State = @State ))
AND
((@Zip is Null) or (Zip = @Zip ))
AND
((@Keyword is Null) or (CONTAINS(DocumnetText ,@Keyword ))

The performance of the above query is not upto the expectations.

Can some one shed light on the best practices to write a query in such cases?

Thanks for your help in advance

 

[mrdenny]

What does your execution plan look like?

What variables are passed when the execution is fine?

What variables are passed then the execution is slow?

What columns are indexed?

Can you take a screen shot of your execution plan and post it so we can see it?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bborissov]

Did you have indexes on every field? If so, hat is the execution plan of this:

WHERE
 CategoryId = ISNULL(@CategoryId, CategoryId) AND
 Code       = ISNULL(@Code      , Code)       AND
 City       = ISNULL(@City      , City)       AND
 State      = ISNULL(@State     , State)      AND
 Zip        = ISNULL(@Zip       , Zip)        AND
((@Keyword is Null) or (CONTAINS(DocumnetText ,@Keyword ))

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

gagsbd,
Did you intentionally spell it DocumnetText? Since you said it twice I'm wondering, but maybe you just copied and pasted the first one.

Borislav,
Some articles I've read suggest that the OPs method is overall better than the IsNull method.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[gagsbd]

Hi ESquared,

Yes it is a typo.

Wht you mean by OPs?

I will post the query plan later today.

Thanks

 

[ESquared]

OP = original post / original poster. I should have said "OP's" with an apostrophe.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[vbkris]

gagsbd,

none of the clauses that you have is a SARGABLE clause. that could also be an issue.

i would suggest that you try using Dynamic SQL (sp_executesql). However there are a couple of issues with this approach:
a. The user needs to have access to all the objects mentioned in the query.
b. Query plan needs to be recompiled (this can be reduced to an extent using sp_executesql).

The advantages:
a. Unnecessary joins can be removed.
b. SARGABLE clauses (as the dynamic sql will only have the values). Which considerably increases performance.

Hope this helps...

Known is handfull, Unknown is worldfull

 

[ESquared]

vbkris,

what's not sargable about these?

((@CategoryId is Null) or (CategoryId = @CategoryId ))
AND 
((@Code is Null) or (Code = @Code ))
AND
((@City  is Null) or (City  = @City  ))
AND
((@State is Null) or (State = @State ))
AND
((@Zip   is Null) or (Zip   = @Zip   ))

The @Variable IS NULL evaluations can be done once and the others are direct comparisons to columns. Why can't indexes be used?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[vbkris]

hi ESquared,

I am not in front of a system that has SQL Server, but i used to write out queries like this for optional where clauses.

However when i saw the query execution plan i was able to see clustered index scans where there should have been seeks / index scans.

one more issue with variables are only if they are passed as a parameter will the compiler know their value while compiling an execution plan. otherwise it wont.

I will try to simulate this myself once am in front of a SQL Server DB and come back to you guys.

Why i think they are not sargable is because the compiler does not see a simple equal to clause but somthing a little more complex...


Known is handfull, Unknown is worldfull

 

[gagsbd]

Hey Guys,

Thanks for all your feedback.

I approched this problem as described below. I do not know whether it is a good practice.

I created another text fields called searchText, basically in this column,I will be storing the content of all other fileds that are searchable i.e CategoryId ,Code ,City,State ,Zip and
DocumnetText (text).

I am concatenating the values in each of filed with some characters to make it unique, say if the zip is 10036 it will be stored as #zip10036# in the SearchText Fields.

Search text is full text indexed, so when ever I need to do search on any fields I will just run the full text search and no structured query. Say for example if I need search on zip = 10036 I will run a query with

where Contains(SearchText,'#zip10036#')

This one is searching fast enough, but I am wondering if there are any loop holes.

Appreciate your thoughts on this.

Also one another point , I need to restrict the number of records retunred, basically for pagination.

Thanks

 

[aajay]

post full script I might help you.....

 

[gagsbd]

Here is the fukk scriot of my SP.

This SP is on a different table but, the concept is the same


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetJob_DetailsSummaryByAny]
@categoryId int = null,
@keywords nvarchar(255) = null,
@state nvarchar(50)= null,
@city nvarchar(50) = null,
@zip nvarchar(3000) = null,
@emplymentTypeId int = null,
@radius float = null,
@pageIndex int = null,
@pageSize int = null,
@educationTypeId int = null,
@yearsOfExperienceId int = null,
@searchCount int out

AS
BEGIN

SET NOCOUNT ON;

DECLARE @freetext nvarchar(4000);
DECLARE @contains nvarchar(4000);

SET @contains =N'#status1# ';

IF(NOT @keywords is NULL)
SET @contains = @contains + ' AND FORMSOF(INFLECTIONAL,' + @keywords + ')';

IF(NOT @categoryId is NULL)
SET @contains = @contains + ' AND #oc' + convert(varchar,@categoryId) + '#';

IF(NOT @state is NULL)
SET @contains = @contains + ' AND #''state' + @state + '''#'; -- State name can contain space in between

IF(NOT @city is NULL)
SET @contains = @contains + ' AND #city' + @city + '#';

IF(NOT @zip is NULL)
SET @contains = @contains + ' AND (' + @zip + ')';

IF(NOT @emplymentTypeId is NULL)
SET @contains = @contains + ' AND #et' + convert(varchar,@emplymentTypeId) + '#';

IF(NOT @educationTypeId is NULL)
SET @contains = @contains + ' AND #edu' + convert(varchar,@educationTypeId) + '#';
IF(NOT @yearsOfExperienceId is NULL)
SET @contains = @contains + ' AND #yoe' + convert(varchar,@yearsOfExperienceId) + '#';

-- select @contains;
--return

SELECT @searchCount = COUNT(Tracker_Id)


FROM dbo.Job_Details

WHERE
(CONTAINS((SearchText),@contains))


IF isnull(@pageIndex,-1) = -1
BEGIN

SELECT
Tracker_Id AS 'AUJID',
Job_Title AS 'JobTitle',
Posting_Date as 'PostDate',
Work_State as 'State',
Work_City as 'City',
Keywords,
Zip,
EmploymentType_Id as 'EmploymentTypeId'

FROM dbo.Job_Details
WHERE
(CONTAINS((SearchText),@contains))



END
ELSE
BEGIN



WITH JobList AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY Posting_Date DESC) AS 'RowNum',
Tracker_Id AS 'AUJID',
Job_Title AS 'JobTitle',
Posting_Date as 'PostDate',
Work_State as 'State',
Work_City as 'City',
Zip,
EmploymentType_Id as 'EmploymentTypeId'

FROM dbo.Job_Details

WHERE
(CONTAINS((SearchText),@contains))



)

SELECT * FROM JobList
WHERE RowNum BETWEEN @pageIndex AND (@pageIndex + @pageSize);
END



SET NOCOUNT OFF
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

 

[vbkris]

Hi Esquared:
This is the SP that i tried creating

CREATE PROCEDURE [QueryPlan] @eml varchar(500) AS

select top 10 * from TABLE where (@eml is null or EMLCOL = @eml)
GO

exec QueryPlan 'CorrectEmail'

This gives me an index scan. This table has an index on the EML col and around 200000 records. The email id given was unique and available in the table.

Another way in which i tested this:

declare @eml varchar(500)
set @eml='CorrectEmail'
select top 10 * from TABLE where (@eml is null or EMLCOL = @eml)
select top 10 * from TABLE where EMLCOL = @eml

The first query produced an index scan while the next one an index seek.

This is the basis on which i confirmed that such optional parameters are SARG friendly.

Please let me know if i am missing something here...


Known is handfull, Unknown is worldfull

 

[vbkris]

one more add on, when i removed the top 10 from the query the first query became a clustered index scan (as more data returned)...

Known is handfull, Unknown is worldfull

 

[ESquared]

So I wonder if forcing an index seek through query hints could do the trick?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[mrdenny]

I just ran simalar code to what Kris has, but with a slight change.

declare @Email as varchar(100)
set @Email = 'test'
select *
from TABLE
where (@Email is not null and Email = @Email)

This produced a nice index seek on the index which is on the Email column. It would appear that this would be a better option than the

where (@eml is null or EMLCOL = @eml)

technique which has been used above. When using this technique I also tried it with two variables and I still got index seeks on both columns.

declare @Email as varchar(100)
declare @FirstName as int
set @Email = 'test'
set @FirstName= null
select *
from Table
where (@Email is not null and Email = @Email)
	or (@FirstName is not null and FirstName = @FirstName)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

Denny,

I think that doesn't work. If you don't specify email or a firstname at all, you should get all records (but don't) and if you specify both an email and a firstname, you should get only those records that have both (but get records that match either one of the two).

I can't spend the time on it now to try to offer more ideas. Perhaps the LIKE trick--although it has its own problems.

IF @FirstName IS NULL SET @FirstName = '%'

SELECT
...
WHERE FirstName LIKE @FirstName

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[vbkris]

hi guys,

now am completely wondering on what basis SQL Server decides wether a clause is SARGABLE.

The real difference between denny's and my SQL was the operator used. Denny used an AND operator while mine used an OR operator. But like Esquared qouted, the AND query wont work out for this situation.

In theory, SQL Server normalises all SQL statements and breaks them down into simple parts. Any insights on how SQL Server will do this for both the statements and how exactly they differ???

Known is handfull, Unknown is worldfull