Heavy performace slowdown after migrating from SQL Server ..

Hi,

after migrating from SQL Server 2005 to SQL Server 2008 I was faced with a
brainteaser that led to a lot of discussions in my team.

Let me sketch the problem:
There is a large database with a varchar(1000) column – say 20 millions
rows. I want to do a fulltext search for ‘“abc”’ in that column. Both, SQL
Server 2005 and SQL Server 2008 do a well job in that case – even if SQL
Server 2005 seems to work a little bit faster but that’s not the point.
Now the situation changes dramatically if I start to search for ‘“abc” AND
NOT “d*”’. If I look into the produced query plan SQL Server 2008 does the
following concurrently:
- Querying the fulltext for every row/document_id containing “abc”
- Querying the fulltext for every row/document_id NOT containing a word
starting with “d”

The first query runs fast since it’s rather a lookup producing a relatively
small resultset. The second query runs very slow and produces a very large
resultset that gets shrinked not until a merge join between both results
leading again to a small result set. So this is a bad query plan since doing
the second query (“d*”) just on the small result set from the first query
would have been more appropriate.
The whole query takes about 10 times longer than the same query in SQL
Server 2005.

It seems that you don’t have any possibility to instruct SQL Server 2008 to
work with other query plans or to speed it up in another way.

Has anyone experienced similar problems with SQL Server 2008 or could even
help?


Thank you very much for your help &
Kind Regards,

Martin

You can't expect fast response for large results sets.

Using Scowl (assuming you have the same distribution or words as they do), I
see that words beginning with the letter d occur 5% of the time. Assuming a
single work in your varchar(1000) columns this means that I can expect about
1 million rows to have words starting with the letter d. This is a lot of
words to have to gather up and filter through.

If this is really a business requirement I think that an inverted file index
is a better fit for something like this. At least to filter out the d*.





"arthur_d" wrote:

> Hi,
>
> after migrating from SQL Server 2005 to SQL Server 2008 I was faced with a
> brainteaser that led to a lot of discussions in my team.
>
> Let me sketch the problem:
> There is a large database with a varchar(1000) column – say 20 millions
> rows. I want to do a fulltext search for ‘“abc”’ in that column. Both, SQL
> Server 2005 and SQL Server 2008 do a well job in that case – even if SQL
> Server 2005 seems to work a little bit faster but that’s not the point.
> Now the situation changes dramatically if I start to search for ‘“abc” AND
> NOT “d*”’. If I look into the produced query plan SQL Server 2008 does the
> following concurrently:
> - Querying the fulltext for every row/document_id containing “abc”
> - Querying the fulltext for every row/document_id NOT containing a word
> starting with “d”
>
> The first query runs fast since it’s rather a lookup producing a relatively
> small resultset. The second query runs very slow and produces a very large
> resultset that gets shrinked not until a merge join between both results
> leading again to a small result set. So this is a bad query plan since doing
> the second query (“d*”) just on the small result set from the first query
> would have been more appropriate.
> The whole query takes about 10 times longer than the same query in SQL
> Server 2005.
>
> It seems that you don’t have any possibility to instruct SQL Server 2008 to
> work with other query plans or to speed it up in another way.
>
> Has anyone experienced similar problems with SQL Server 2008 or could even
> help?
>
>
> Thank you very much for your help &
> Kind Regards,
>
> Martin