After moving a query doesn't work anymore...

Question

Hi

I need some help for a problem that's driving me crazy!

I've moved an ASP+SQLServer application from an old server to a new one.
The old one was a Windows 2000 server with MSDE, and the new one is a Windows 2008 with SQL 2008 Express.
Everything is ok, even a little faster, except just one damned function whose asp page gives a time out. 
I've tryed the query within that page in a management query windows and it never ends, while in the old server it took about 1 minute to be completed.
The query is this one:


SELECT DISTINCT TBL1.TBL1_ID, REPLACE(TBL1_TITOLO, CHAR(13)+CHAR(10), ’ ’), COALESCE(TBL1_DURATA, 0), TBL1_NUMERO, FLAG_AUDIO 
FROM SPOT AS TBL1 
INNER JOIN CROSS_SPOT AS CRS ON CRS.TBL1_ID=TBL1.TBL1_ID 
INNER JOIN DESTINATARI_SPOT AS DSP ON DSP.TBL1_ID = TBL1.TBL1_ID 
WHERE DSP.PTD_ID_PUNTO = 1044 
AND DSP.DSP_FLAG_OK = 1 AND TBL1.FLAG_AUDIO_TESTO=1 AND TBL1.FLAG_AUDIO_GRAFICO=’A’ AND CRS.CRS_STATO>2 
OR TBL1.TBL1_ID IN (SELECT ID FROM V_VIEW1 WHERE ID IS NOT NULL AND V_VIEW1.ID_MODULO = 403721) 
OR TBL1.TBL1_ID IN (SELECT TBL1_ID FROM V_VIEW2 WHERE V_VIEW2.ID_PUNTO = 1044)  
ORDER BY TBL1_NUMERO 


I've tried to transform the 2 views in last lines into tables and the query works, even if a little slower than before.
I've migrated the db with it's backup/restore function. Could it be and index problem?
Any suggestions?


Thanks in advance!


Alessandro

Answer 1

Have you looked at the estimated query plan to see what is happening?

Answer 2

If you haven't already you should make sure that you have updated the statistics of all the tables. This will help the optimizer make better choices when compiling query plans.

As Glen has said, take a look at the estimated query plan to see if there are an obvious bottlenecks and try to tune those. One of the problems you will have in Express edition is that it does not support parallelism. i.e. you will not be able to make use of more than one core.

As an aside I noticed that you are not prefixing the object names with their schema. It's a best practice to do this as then SQL Server will not have to perform a lookup to check the ownership.

---

If you find this helpful, please mark the post as helpful,
If you think this solves the problem, please propose or mark it an an answer.

Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

Richard Douglas
My Blog: Http://SQL.RichardDouglas.co.uk
Twitter: @SQLRich

Answer 3

Thanks a lot for your replies. I got it!
I've added a couple of queries to fill 2 temporary tables with the rows needed for the two "OR IN" at the end, and now it works!!
:)