domenica 4 marzo 2012 23:36Hi
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?
Thanks in advance!
Tutte le risposte
lunedì 5 marzo 2012 02:20Have you looked at the estimated query plan to see what is happening?
lunedì 5 marzo 2012 10:44
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
My Blog: Http://SQL.RichardDouglas.co.uk
giovedì 8 marzo 2012 22:13Thanks 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!!
- Contrassegnato come risposta KJian_ lunedì 12 marzo 2012 05:16