>Try materialize cte into a temp table. It often helps.
>

Thanks, I'll try that, although I thought some time ago you had suggested using a CTE to speed up another query.

>Also, make sure to provide aliases for ALL columns in your queries when you use several tables. Your last select statement has columns without aliases, so it's hard to see which table they belong to.
>

You've mentioned this to me before but the standard in this database is the first three letters of each field indicates what table it's from so that is unnecessary.



>>>>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?
>>>
>>>What if you put AND cus_credit > -1 instead of commenting the line?
>>
>>That didn't make any difference, thanks. FYI: cus_credit is a boolean field
>>
>>Is there any way to post the execution plan here for someone to look at?