Hi,

I have a stored procedure defined:

ALTER PROCEDURE [dbo].[InvoicesSelectOutstanding]
(
	@crt_date date
)
AS
	SET NOCOUNT ON;
;WITH cte as 
 (SELECT ivd_invfk, SUM(ivd_amount) as inv_total FROM InvoiceDetails
WHERE ivd_invfk NOT IN (SELECT crt_invfk 
							FROM CashReceipts 
									INNER JOIN Deposits on crt_depfk = dep_pk 
							WHERE dep_date <= @crt_date
						UNION ALL
						SELECT rdz_invfk 
							FROM receiptdetailszipx
								INNER JOIN Receipts ON rdz_rctfk = rct_pk
							WHERE rct_date <= @crt_date
						UNION ALL
						SELECT rdi_iinfk 
							FROM ReceiptDetailsInbound
								INNER JOIN Receipts ON rdi_rctfk = rct_pk
							WHERE rct_date <= @crt_date
						UNION ALL
						SELECT rdo_oinfk 
							FROM ReceiptDetailsOutbound
								INNER JOIN Receipts ON rdo_rctfk = rct_pk
							WHERE rct_date <= @crt_date
						UNION ALL
						SELECT rda_cwbfk 
							FROM ReceiptDetailsAWBs
								INNER JOIN Receipts ON rda_rctfk = rct_pk
							WHERE rct_date <= @crt_date
						)
 GROUP BY ivd_invfk )
	SELECT [inv_awbfk],
		[inv_awbnumber],
		[inv_cancelled],
		[inv_ccufk],
		[inv_consignee],
		[inv_cusfk],
		[inv_cusnumber],
		[inv_cwbfk],
		[inv_date],
		[inv_goodsvalue],
		[inv_insurance],
		[inv_number],
		[inv_pieces],
		[inv_pk],
		[inv_printed],
		[inv_shpfk],
		[inv_type],
		[inv_weight]
		,cus_company
		,cus_lastname
		,cus_firstname
		,cus_number
		,cnt_code
		,cte.inv_total
		,awb_number
		,asatdate = @crt_date
	FROM [dbo].[Invoices]
		INNER JOIN cte on invoices.inv_pk = cte.ivd_invfk
		INNER JOIN Customers on inv_cusfk = cus_pk
		INNER JOIN AirWayBills on inv_awbfk = awb_pk
		INNER JOIN Countries ON cus_cntfk = cnt_pk
	WHERE inv_date <= @crt_date
		AND inv_printed = 1
		AND inv_cancelled is null
--		AND cus_credit = 1
	ORDER BY cus_company

This takes quite a while to run (47 seconds).

If I uncomment the second to last line (AND cus_credit = 1) it runs in under 2 seconds.

The execution plan seems to tell me that everything is OK and I have indexes on all the joining and where fields.

Any suggestions on how to speed this up?