After server move a query doesn't work anymore

Question

After server move a query doesn't work anymore

alessandro 86007

SSC Rookie

Points: 41
More actions
March 4, 2012 at 4:58 pm

#251887

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

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004706 More actions · Answer 1

More likely, it's a statistics problem. Did you rebuild all of your statistics with "FULL SCAN" after the server move?

One other trick you might want to try especially since you moved from MSDE. Try adding OPTION(MAXDOP 1) to the end of your query. I know it sounds crazy but if the code is using parallelism now that it's on a new "fancy" server, it's sometimes less expensive to not have parallelism divy up the job and reassemble it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

alessandro 86007 SSC Rookie Points: 41 More actions · Answer 2

thanks for the answer

I've tried to rebuild statistics and to add the option you mentioned.

unfortunately nothing has changed....

🙁

Jeff Moden SSC Guru Points: 1004706 More actions · Answer 3

The only way we're going to be able to help is if you take the steps outlined in the article at the second link in my signature. Take the time... it'll be worth it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Alessandro, are you sure that this query contains the right predicates?

Usually, you want to surround OR conditions in parentheses.

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

-- Do the following conditions really need to be logically ORed to each of the above?

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

-- Gianluca Sartori

alessandro 86007 SSC Rookie Points: 41 More actions · Answer 5

Gianluca, I think your suggestion is really in the right direction.

I have to check as I still don't know very well the application but adding parenhtesis leaving out just the first condition makes the query really very fast!

Grazie amico!

😉