Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: After server move a query doesn't work anymore

    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

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Get the explain plans for both instances and see what is different.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Did you update statistics on the new server, after the move? The version 2000 index statistics are not used by the 2008 query engine.

  4. #4
    Join Date
    Mar 2012
    Posts
    2
    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!!

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you test if this query performs better than the one in your first post?
    Code:
    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
    	LEFT OUTER JOIN V_VIEW1 ON 
    --		V_VIEW1.ID IS NOT NULL AND 
    		V_VIEW1.ID_MODULO = 403721 AND
    		TBL1.TBL1_ID = V_VIEW1.ID
    	LEFT OUTER JOIN V_VIEW2 ON	
    		TBL1.TBL1_ID = V_VIEW2.TBL1_ID AND
    		V_VIEW2.ID_PUNTO = 1044
    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 
    	V_VIEW1.ID IS NOT NULL OR 
    	V_VIEW2.TBL1_ID IS NOT NULL
    	)
    ORDER BY TBL1_NUMERO
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •