RE: Heavy performance slowdown after migrating from SQL Server 200

Tech-Archive recommends: Fix windows errors by optimizing your registry

I guess you're right :-)

What I'm, really wondering about is, why SQL Server 2008 queries every part
as it was a single query on its own without any linkage. I would have assumed
that the query plan optimizer would have produced a query plan first querying
for "abc" and then look into that result set for every item not containing
"d*". particularly as the fulltext is now part of the relational database so
that the approved query plan optimization could be used.

Maybe I should just wait for further versions.

"Hilary Cotter" wrote:

Its a different algorithm in 2008 as I am sure you know:)

While the new algorithm works best for most use cases in SQL 2008 you seem
to have stumbled open a poor case.

Could you post the execution plans?

"arthur_d" wrote:

Thanks for your reply.

The remaining question is why SQL Server 2005 is performing the same query
in just a fraction of that time that SQL Server 2008 needs. If it is rather a
structural problem it should be slow regardless of version.


"Hilary Cotter" wrote:

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
.

Relevant Pages

  • Re: Wanted: Discussion on MSSQL Internals Interview Qustions
    ... Developer for Microsoft's SQL Server team? ... Other inputs the query optimizer uses are the schema ... The C# dataset caching can only cache results from a single query. ... I don't consider these alternatives to stored procedures, ...
    (microsoft.public.sqlserver.programming)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Re: Extremely Slow Table
    ... In this case, the table is the road, and the query plan is the car. ... The optimizer makes it choice of plan from the statistics SQL Server ...
    (comp.databases.ms-sqlserver)