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?