interesting parameter sniffing info

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)

interesting parameter sniffing info

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-24 : 07:50:00

from BOL [EDIT]in "CONTAINS predicate": [/EDIT]

quote:


USE AdventureWorks;
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='performance'
SELECT Description 
FROM Production.ProductDescription 
WHERE CONTAINS(Description, @SearchWord);

Because "parameter sniffing" does not work across conversion, use nvarchar for better performance. In the example, declare @SearchWord as nvarchar(30).


USE AdventureWorks;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'performance'
SELECT Description 
FROM Production.ProductDescription 
WHERE CONTAINS(Description, @SearchWord);

Interesting info... i didn't know that.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-24 : 07:58:03

Didn't understand what conversion is involved in the example shown? Also, is it specific to Full-Text searching only?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-24 : 07:59:48

i have no idea.
i've just read it and thought i'd share and see if anyone knows about this
and maybe get some opinions...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-24 : 08:02:16

column Description in Production.ProductDescription table is of type nvarchar(400)

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-24 : 08:06:43

Yes...I got it.
Great idea to share it, I was also not aware of this.

Interestingly, SQL Server 2000 documentation has no such comments. Is it a known issue with 2005 or was it a hidden bug in SQL 2k as well?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-01-24 : 08:25:53

Perhaps it came in with SQL2K SP4 when cross-datatype (and thus "conversion" was required) comparison performance took a hit?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources