June 5, 2010 at 3:20 am
Hi
In SQL Server 2005 I have a sample table with 4 columns and 195433 records. 3 columns is full text indexed.
When i run the following query in SQL 2005 the execution completes in 4 seconds.
use test
declare @find varchar(50);
set @find= 'area1 | area2 | area3';
select * from dbo.tablename1 where (@find=' " " ') OR contains (addr, @find)
Later, i restored the above table in SQL server 2008 and rebuild the index.
When i run the above query it takes 32 seconds for the execution to complete.
if i replace the following
select * from dbo.tablename1 where (@find=' " " ') OR contains (addr, @find)
TO
select * from dbo.tablename1 where (' "" ' = ' " " ') OR contains (addr, @find)
Then result is displayed in 3sec in SQL server 2008
Can someone please tell me why it takes more time for execution in SQL server 2008.
Is there any issues relating to variable execution in SQL server 2008
Both the instaces of SQl(2005 & 2008) are installed on the same machine
I have attached the execution plan for SQl server 2005 & 2008.
Thank you
June 6, 2010 at 8:19 am
firstly... please gather fresh performance stats on SS2K8 affected objects, then try again.
secondly... if issue persists please trace both SS2K5 and SS2K8 versions of the query.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 8, 2010 at 2:49 am
The simple answer is that the query is a poor one, and SQL Server 2005 happens to come up with a less disastrous execution plan than 2008. Try writing the query like this:
-- NOTE the full-text search condition is Unicode
DECLARE @find NVARCHAR(50);
SET @find = N'area1 | area2 | area3';
IF @find = N' " " '
SELECT T.id, T.name, T.email, T.area, T.addr
FROM dbo.tablename1 T;
ELSE
SELECT T.id, T.name, T.email, T.area, T.addr
FROM CONTAINSTABLE(dbo.tablename1, addr, @find) FTQ
JOIN dbo.tablename1 T
ON T.id = FTQ.;
It's still not perfect, but it will perform a lot better.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply