>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?

put this into SSMS

EXEC [dbo].[InvoicesSelectOutstanding] 	@crt_date =GETDATE()

select it and into "Query" menu select
"Analyze Query with Optimization ..." ( this may not be exact syntax )

This suggest statistics and index to optimize the sql script.
If this doesn't optimize the query,
you need to rethink the declarative code.
Remember that SQL engive is complex and not perfect,
on some cases the plan is wrong.