The following query runs very efficient without the last line. "or @testschedule = 1". This seems to confuse SQL. Within the 'where' claus there are many conditions to be met but if you selected just one of the conditions rather than all of them it also slows the query. Would anyone have any ideas of optimizing this.

USE [AMFMMODS]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


DECLARE @SchedType varchar
DECLARE @ScheduleWeek datetime
DECLARE @Jobno varchar(20)
DECLARE @TestSchedule bit, @DeliveryOptionChange int, @Weekly bit, @DocHandle int

select
ri.ReceiptItemID,
CASE ri.MailListID WHEN 0 THEN ri.Quantity ELSE
COALESCE( rli.Quantity, 0 ) END + CASE WHEN @SchedType = 'GC' AND ri.ProdID <> 218 AND ri.MailListID = 0 THEN 5 ELSE 0 END,
CASE WHEN ri.MailListID = 0 OR EXISTS( Select 1 FROM Shop_Orderprocess_Recipientlist_Item WITH (NOLOCK)

WHERE MailListID = ri.MailListID AND RecipientType = 3 ) THEN 0 ELSE 1 END,
COALESCE( sv.vers_ab, 'NoVersion') As vers_ab, ri.DeliveryOptionID, COALESCE( rli.WeekToMail, 1 )
from Shop_Orderprocess_Receipt r WITH (NOLOCK)
INNER JOIN Shop_Orderprocess_Receipt_item ri WITH (NOLOCK)
ON r.ReceiptID = ri.ReceiptID
INNER JOIN Shop_Shopper ss WITH (NOLOCK)
ON ss.intShopperID = r.intShopperID
INNER JOIN Shop_Product sp
ON sp.ProdID = ri.ProdID
LEFT OUTER JOIN (select MailListID, WeekToMail, count(*) As Quantity
FROM Shop_Orderprocess_RecipientList_item WITH (NOLOCK) WHERE MailTo = 1 GROUP BY MailListID, WeekToMail ) rli

ON ri.MailListID = rli.MailListID AND ( ( rli.WeekTomail = dbo.fn_GetOrderWeek( r.CompletedDate, @ScheduleWeek) AND @Weekly = 1 ) OR @Weekly = 0 OR @TestSchedule = 1)
LEFT OUTER JOIN vw_ScheduleVersions sv WITH (NOLOCK)
ON sv.ReceiptItemID = ri.ReceiptItemID

WHERE

(
@TestSchedule = 0
AND EXISTS( select 1 FROM ScheduleCodes WITH (NOLOCK) WHERE sp.ScheduleCode = SchedID
AND SchedCode = @SchedType )
AND ( ( r.CompletedDate < @ScheduleWeek AND @Weekly = 1)
OR ( @Weekly = 0 AND ri.ScheduledDate IS NULL
AND convert( datetime, convert( char, r.CompletedDate, 101 ) ) <= convert( datetime, convert( char, @ScheduleWeek, 101 ) ) ) )
AND (ri.MailListID = 0 AND ri.ScheduledDate is null OR ri.MailListID <> 0 AND rli.MailListID IS NOT NULL)
AND ( ri.DeliveryOptionID IN (3, 5) AND ri.MailListID <> 0 OR ri.DeliveryOptionID IN (2) )

AND r.Testorder = 0
AND ri.CancelledDate IS NULL
AND ss.TerminatedDate IS NULL
)

--OR @TestSchedule = 1