>>>>>>Thanks!
>>>>>>
>>>>>>I can't attach it here and neither can I post it even though I surround it with pre tags. :(
>>>>>
>>>>>Frank,
>>>>>ZIP the .SQLPLAN and attach it as a file here.
>>>>
>>>>See attached.
>>>
>>>That looks like the estimated executionplan as its missing the actual numbers. Can you post the performed execution plan?
>>>
>>>Can you also execute it with the RECOMPILE option? Depending on the parameters passed it might reuse an execution plan that is optimal for one value, but not for another.
>>>
>>>
>>>Walter,
>>
>>I've attached the Actual Execution Plan.
>>
>>Will research the RECOMPILE option and try it out.
>
>Yes, this shows the problem. There is simply too much I/O going on. Too many rows being retrieved from the database.
>
>I suspect the problem is in the NOT IN(), in combination with the UNIONs in the subquery.
>I'd try to rewrite the query to use
>
>

WHERE NOT EXISTS (SELECT 1 FROM CashReceipts INNER JOIN Deposits on crt_depfk = dep_pk AND  crt_invfk = ivd_invfk)  
>AND NOT EXISTS (SELECT 1 FROM receiptdetailszipx INNER JOIN Receipts ON rdz_rctfk = rct_pk AND rdz_invfk = ivd_invfk) 
>AND NOT EXISTS (SELECT 1 FROM ...) 
>AND NOT EXISTS (SELECT 1 FROM ...)

>
>Walter,

Thanks for the idea Walter, but it didn't make any difference to the speed.

Here's what I tried, did I understand you correctly?

USE [IBC]
GO
/****** Object:  StoredProcedure [dbo].[InvoicesSelectOutstanding]    Script Date: 12/06/2013 11:29:51 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO











ALTER PROCEDURE [dbo].[InvoicesSelectOutstanding]
(
	@crt_date date
)
WITH RECOMPILE
AS
	SET NOCOUNT ON;

create table #ReceiptsTemp
(ivd_invfk uniqueidentifier,
inv_total numeric(18,2))

insert into #ReceiptsTemp
 SELECT ivd_invfk, SUM(ivd_amount) as inv_total 
	FROM InvoiceDetails
	WHERE NOT EXISTS (SELECT 1 
						FROM CashReceipts 
								INNER JOIN Deposits on crt_depfk = dep_pk AND crt_invfk = ivd_invfk
						WHERE dep_date <= @crt_date)
		AND NOT EXISTS (SELECT 1 
							FROM receiptdetailszipx
								INNER JOIN Receipts ON rdz_rctfk = rct_pk AND rdz_invfk = ivd_invfk
							WHERE rct_date <= @crt_date)
		AND NOT EXISTS (SELECT 1 
							FROM ReceiptDetailsInbound
								INNER JOIN Receipts ON rdi_rctfk = rct_pk AND rdi_iinfk  = ivd_invfk
							WHERE rct_date <= @crt_date)
			AND NOT EXISTS (SELECT 1 
							FROM ReceiptDetailsOutbound
								INNER JOIN Receipts ON rdo_rctfk = rct_pk AND rdo_oinfk  = ivd_invfk
							WHERE rct_date <= @crt_date)
		AND NOT EXISTS (SELECT 1 
							FROM ReceiptDetailsAWBs
								INNER JOIN Receipts ON rda_rctfk = rct_pk AND rda_cwbfk  = ivd_invfk
							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
		,#ReceiptsTemp.inv_total
		,awb_number
		,asatdate = @crt_date
	FROM [dbo].[Invoices]
		INNER JOIN #ReceiptsTemp on invoices.inv_pk = #ReceiptsTemp.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

drop table #ReceiptsTemp

I also tried it with the CTE, but this way was faster.