Frank,
If you can send the execution plan of the query below, I'll take a look at it in my morning
Walter,
>>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
>
> ORDER BY cus_company
>
>drop table #ReceiptsTemp
>
>
>I also tried it with the CTE, but this way was faster.
