James,

You should probably have indexes on these columns:

ft.trans_module
ft.trans_submodule
ft.trans_date

In Query Analyzer, check on the menu item Query \ Show Execution Plan

Then as you write a query another page will appear in the output frame. You can hover the mouse over an item to get all the details. In most general terms if you see a Table Scan on any large tables then SQL didn't find a good index to use to support the query.

You can also look at the Index Tuning Wizard to help see indexes the SQL Engine has found useful to speed up queries.

In Query Analyzer, select Tools, Manage Statisics. scroll through the tables and if you wee statistics with names like _WA_ then these are statistical counters SQL has added on the fly to help it run faster. You might consider promoting those to real indexes and then come back and delete the _WA_ statisic.

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