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

Are you running/testing the performance of this from within SSMS? If you're not, that's where you should start. I usually test each of my subqueries / joins separately to see what the performance is like. For example, I would run the SELECT crt_invfk FROM CashReceipts to see how fast it is. I'll fill in some temporary values if needed. Make sure all of the fields referenced in your WHERE clause have indexes on them. I'll also check to see how many records are returned. Even fast queries are slow if you're returning a ton of records. I'll check to see if there are any ways I can make my query more selective (ex. maybe I can add an additional filter by a date, or a status, etc.)

After testing each of the individual pieces, I'll then selectively comment out portions of the query and/or fields and re-run it to see how much the performance increases. That gives me an idea of which part of the query may be causing problems. Inside of SSMS if you look at the Execution Plan try to find which part of the query is taking the most time. That can also help narrow the problem down.

BTW - ORDER BY is slow. In a lot of cases I've found that if I really need something to be as fast as possible I'll remove the ORDER BY clause and just handle that on the client side.

If you just don't see how to make it faster, you can run the Database Engine Tuner Profiler. You basically just save your query as a stand-alone .SQL file (something you would be able to run directly from within SSMS). Then point the app. at this SQL file and let it analyze it. It will make suggestions about things you can add to make the query faster. I normally start with the index suggestions and apply them, one at a time, then re-test the query. If the index didn't make a big difference I'll remove it and move on to the next one.

There are other things you can look at, but the above should at least give you someplace to start.