James,

On another item...

SELECT INTO #TEMP

is generally a bad thing on a high use server because it creates more contention in tempdb.

It's better to use:

create table #temp ( ... )
insert into #temp
   select ...

You shouldn't put results into tempdb just to pull them out in the next step. Do you see this is a read-from-table + write-to-temp + read-from-temp operation? Why do the write-to-temp + read-from-temp when just the first read-from-table can do the task?

You want to always make sure you are always only pulling the data of interest instead of the whole table. Your first select below didn't show the where clause that your second one does. If you are only interested in records in a certain date range with certain statuses make sure your WHERE clause is limiting the result set appropriately.

>>Does just having a database table indexed on the field you are using speed up a join with another table that is indexed on the joining field speed it up automatically or do you have to pick the index you want to use.
>>
>>i.e.
>>
>>select l.order_date, i.partnumber
>>From
>
>
>Sorry I hit the tab and I think it sent this.
>
>If I Wanted to join the Fulfillment_Transaction table to the orders table
>
>FROM
>orders o
>
>INNER JOIN
>
>Fulfillment_Transaction ft
>
>ON o.orders_ID = ft.trans_key01
>
>
>could I use the
>IX_Fulfillment_Transaction_key01
>nonclustered
>located on PRIMARY
>trans_key01
>
>OR
>
>Could I speed up
>
>SELECT
> trans_key01
>FROM
> Transaction_Fulfillment
>INTO
>
> #TEMP
>WHERE
> ft.trans_module = 'ORDERS'
>AND
> ft.trans_submodule = 'SHIPPED'
>AND
> ft.trans_date between @startdate and @enddate
>
>USING the IX_Fulfillment_Transaction_key01 index.