Sql-server – After server move a query doesn’t work anymore

sql server

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 runs Windows 2008 with SQL 2008 Express.

Everything is ok, even a little faster, except just one 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. In the old server it took about 1 minute to be completed.

Here is the query:

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 two views in the last lines into tables and the query works, even if it's a little slower than before. I've migrated the database with its backup/restore function. Could it be an index problem? Any other suggestions?

Best Answer

How big are the tables? Sounds like an index problem. Do you have the same indexes on both servers? Did you rebuild the indexes after restoring?

As TiCL suggested, the best way is to compare the execution plans of both servers.