>>I'm using the following in a Stored Procedure to pull out some data for a report. Unfortunately it's running too slowly and timing out when I call the Dataset.Fill().
>>
>>
SELECT [inv_awbfk],
>> [inv_cancelled],
>> [inv_cusfk],
>> [inv_cusnumber],
>> [inv_date],
>> [inv_goodsvalue],
>> [inv_insurance],
>> [inv_number],
>> [inv_pieces],
>> [inv_pk],
>> [inv_printed],
>> [inv_weight]
>> ,cus_company
>> ,cus_lastname
>> ,cus_firstname
>> ,cus_number
>> ,cnt_code
>> ,inv_total = (SELECT SUM(ivd_amount) FROM InvoiceDetails WHERE ivd_invfk = inv_pk)
>> ,awb_number
>> ,asatdate = @crt_date
>> FROM [dbo].[Invoices]
>> 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_pk NOT IN (SELECT crt_invfk
>> FROM CashReceipts
>> INNER JOIN Deposits on crt_depfk = dep_pk
>> WHERE dep_date <= @crt_date)
>> ORDER BY inv_number
>>
>>Any suggestions for speeding this up?
>>
>>I've got indexes on all the joined fields.
>>
>>Is there a way for me to post the execution plan here?
>
>
>Based on Naomi's suggestion, try this:\
>
>SELECT [inv_awbfk],
> [inv_cancelled],
> [inv_cusfk],
> [inv_cusnumber],
> [inv_date],
> [inv_goodsvalue],
> [inv_insurance],
> [inv_number],
> [inv_pieces],
> [inv_pk],
> [inv_printed],
> [inv_weight]
> ,cus_company
> ,cus_lastname
> ,cus_firstname
> ,cus_number
> ,cnt_code
> ,Test.inv_total
> ,awb_number
> ,asatdate = @crt_date
> FROM [dbo].[Invoices]
> INNER JOIN Customers on inv_cusfk = cus_pk
> INNER JOIN AirWayBills on inv_awbfk = awb_pk
> INNER JOIN Countries ON cus_cntfk = cnt_pk
> INNER JOIN (SELECT ivd_invfk
> , SUM(ivd_amount) AS inv_total
> FROM InvoiceDetails
> GROUP BY ivd_invfk) Test2 ON ivd_invfk = inv_pk)
> WHERE inv_pk NOT IN (SELECT crt_invfk
> FROM CashReceipts
> INNER JOIN Deposits on crt_depfk = dep_pk
> WHERE dep_date <= @crt_date)
> ORDER BY inv_number
>
No change in the speed. I'm going to look at the indexes as you posted in another reply
