SQL2005 query slow runs in 6 second but in SQL2000 it runs in 1 second or less.

Question

SQL2005 query slow runs in 6 second but in SQL2000 it runs in 1 second or less.

Keith Robinson-407711

SSCarpal Tunnel

Points: 4112
More actions
May 2, 2007 at 3:08 pm

#178269

This query runs in 6 seconds on SQL2005 but it runs in less then a second on SQL2000. Table data is the same, indexes are the same, stats on tables are the same. It is referencing a Full Text Index on the items table in both versions. Also it only runs slow on SQL2005 when using the word "STAPLERS" all other word search working great in both versions. Any idea on why it is slow in SQL2005 with the word 'staplers'??
SELECT
dbo.items.SKU,
COALESCE(dbo.items.postconsumerwaste,0) as PCW,
dbo.items.LongDescription,
dbo.items.SalesCopy,
dbo.items.Price,
dbo.items.ManufactLabel,
dbo.items.Manufactname,
tbl_items_popularity.numsold,
COALESCE(dbo.NewCarton.Discount2,0) as carton
FROM dbo.items WITH (NOLOCK)
LEFT JOIN dbo.NewCarton WITH (NOLOCK) ON dbo.NewCarton.SKU=dbo.items.SKU
INNER JOIN fldsearch WITH (NOLOCK) ON fldsearch.SKU=items.SKU
LEFT JOIN tbl_items_popularity WITH (NOLOCK) ON tbl_items_popularity.SKU=items.SKU
WHERE search=1AND (1=1 AND ( items.SKU LIKE '%STAPLERS%' OR (items.oldSKU LIKE '%STAPLERS%' AND items.oldSKU IS NOT NULL)) OR (FREETEXT(Keywords, 'Stapler') OR FREETEXT(longdescription, 'STAPLERS') OR manufactname LIKE '%STAPLERS%')) ORDER BY MANUFACTNAME ASC, PRICE ASC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mauve SSChampion Points: 11316 More actions · Answer 1

Only true way to look at this is to get the query plan actually run (not estimated) for the different queries and see what is different. Along with a trace.

Since you are using full-text it may be related to the number of occurrances of the term "STAPLERS".

Note that the full-text engine and the functions (e.g., CONTAINS, FREETEXT, etc.) is different between SQL Server 2000 and SQL Server 2005.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Keith Robinson-407711 SSCarpal Tunnel Points: 4112 More actions · Answer 2

I ran the queries and compared their actual execution plans. In SQL 2005 the query is doing a Table Spool (Lazy Spool) and that is where the slow down is coming from. In SQL 2000 it doesn't do the Table Spool. Any suggestions on how to rewrite this or rework it would be great.

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 3

Can you post the query plan as XML? It sounds like you might be missing an index which is why you're getting the work table in tempdb. Possibly in the JOIN criteria? Take a look at the dynamic management views that show missing indexes. Kalen Delany recently wrote a couple of articles on this in SQL Server magazine:

http://www.sqlmag.com/Article/ArticleID/95220/Use_MissingIndex_Groups_for_Query_Tuning.html

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

danschl SSCertifiable Points: 5770 More actions · Answer 4

it is not just the like clause causing issues

several linked server queries take 10 seconds in sql2000

and 5 minutes or more in sql2005

also several queries using temp databases take minutes in 2005 instead of seconds on sql2000

Keith Robinson-407711 SSCarpal Tunnel Points: 4112 More actions · Answer 5

I was able to figure out a solution to this problem. I ended up adding one index in SQL 2005 and brought my query time to under 1 second.