How can I turn off parallelism for a single query?

I changed servers from a 2 xeon system to a 4 dual core system.
With the exact same database on each server, an update statement takes
3 minutes on the new server...and 2 seconds on the old server.
I already set the MAXDOP on the new system to 4 to mimic the old
system...I've also updated stats, rebuilt indexes, etc.
So I want to try running the update statement without parallelism to
see if it still takes 3 minutes.
Does anyone know the exact syntax to turn off parallelism each time
this update statement is run?
Here's the update statement. If I run just the select statements
separately that are used in the udpate statement...they all return in 1
second...so it looks like SQL server is finding the data that is needs
to update quickly.
But the update statement is taking minutes. It should only take 3
seconds. I've already dropped and recreated the indexes as well.
Anyone ever seen this before?
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )I would check the query plan of both versions and see if there are any
difference. Since the select is running fast and the update is
running slow, I would check for blocking (select needs a shared lock.
Update needs an exclusive lock. That makes a big difference). I would
also check for triggers in the updated table. In any case if you want
to check if the problem is due to parallelism, you can add at the end
of the query the option maxdop:
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )
option (maxdop 1)
Adi
mchi55@.hotmail.com wrote:
> I changed servers from a 2 xeon system to a 4 dual core system.
> With the exact same database on each server, an update statement takes
> 3 minutes on the new server...and 2 seconds on the old server.
> I already set the MAXDOP on the new system to 4 to mimic the old
> system...I've also updated stats, rebuilt indexes, etc.
> So I want to try running the update statement without parallelism to
> see if it still takes 3 minutes.
>
> Does anyone know the exact syntax to turn off parallelism each time
> this update statement is run?
> Here's the update statement. If I run just the select statements
> separately that are used in the udpate statement...they all return in 1
> second...so it looks like SQL server is finding the data that is needs
> to update quickly.
> But the update statement is taking minutes. It should only take 3
> seconds. I've already dropped and recreated the indexes as well.
> Anyone ever seen this before?
> Update ClientContacts
> SET StatusID = (select statusID from status where module = 'Contact'
> AND
> status.description = 'Current' and status.active = 1) where ContactID
> IN
> ( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
> Orders O ON CC.ContactID = O.contactID OR
> CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
> Inner join OrderCandidates OC ON
> OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
> OrderCandidates
> where OrderCandidates.stageid = (select stageID from stages
> where stages.stagename = 'Assigned/Placed' AND
> stages.active = 1) ) )|||On 22 Jan 2007 00:32:00 -0800, mchi55@.hotmail.com wrote:
>Does anyone know the exact syntax to turn off parallelism each time
>this update statement is run?
Add this to the end of the UPDATE command itself:
OPTION ( MAXDOP 1 )
Roy Harvey
Beacon Falls, CT