Speed problems after migrating from 6.5 to 7.0

[MarkRuse]

I have just finished a port from a database which was 6.5 to 7.0. Performance is great, apart from one query which is working with a view. Here is the SQL.

SELECT ProductCount.OrderID,
CASE WHEN ProductCount.ProductCount = 1
THEN (
SELECT DISTINCT Product.Name FROM Segment, Product
WHERE Segment.OrderID = ProductCount.Orderid
AND Segment.ProductID = Product.ProductID
AND Segment.Deleted = 0
)
WHEN ProductCount.ProductCount > 1
THEN 'Multi-Product'
ELSE 'No Segments'
END AS Product
FROM ProductCount

ProductCount is a view with 6577 rows. a straight select comes back in less than a second. Product is a reference table with 22 rows, and Segment has 38923 rows.

If I run this query on 6.5 it comes back in about 20 seconds. If I run it on 7.0 it seems to take so long, that I have yet waited for it to finish.

Does anybody have any ideas why this is the case ?

Thanks

 

[calahans]

Run your SQL in the SQL Profiler, this should tell you how the query is being parsed. You may need to change an index or some such thing. Personally, I don't like having logic in SQL, I'd rather do that within a language and fire SQL to the db.

Could you move the conditional stuff to a langauge and fire two different SQLs to the db depending on the result?

 

[MarkRuse]

I have run the profiler, and nothing seems amis. If I limit the select to say do the first 700 OrderID's it works instantly, but if I move to the next available OrderID say 703, then it will take forever. If I then select out 703 to 710, it will be instant again. It almost looks as if I am hitting a limit on the number of rows I can select at any one time. I have examined the tempdb, to see if this is the problem, but that looks fine as well.

Does anybody have any ideas ?

 

[ColinM]

You could try running DBCC & Scandisk, just to check nothing has been corrupted.
And have a look at the execution plans for the queries you mentioned in SQL Analyser.

 

[jnicho02]

Hey Rusey!

Firstly, check that Order No 703. Can you do selects on all the tables involved to make sure that it returns something...[tt]SELECT * FROM Segment WHERE ORDER_ID = 703[/tt] etc. it is always possible there is a corruption.

If not, then whenever I see that 'distinct' word I always smell a rat...

[tt](SELECT DISTINCT Product.Name FROM Segment, Product
WHERE Segment.OrderID = ProductCount.Orderid
AND Segment.ProductID = Product.ProductID
AND Segment.Deleted = 0)[/tt]

Basically, you wanted one row back but using the DISTINCT keyword makes SQL Server do an implied GROUP BY and has to hold all of the rows in tempdb.

If you know that the answer is going to be one row, then take the first one returned..

[tt](SELECT min(Product.Name) FROM Segment, Product
WHERE Segment.OrderID = ProductCount.Orderid
AND Segment.ProductID = Product.ProductID
AND Segment.Deleted = 0)[/tt]

This will avoid the implied GROUP BY

Jezzer ;-)

p.s. keep the logic in the SQL query, unless it gets really complicated, it is more efficient.

p.p.s. if this works, a TipMaster vote wouldn't go amiss....