SQL Performance

[lbarahona]

i have a 3.6 GB database running in MS-SQL Server Enterprise in a 4 Processor server running Raid 5.
It takes about 8 seconds to run and get only 110 rows. This is a query with 5 joins and 1 nested statement.
I am trying to get statistics to find where my performance problem could be. Any ideas of what can i do to speed up the queries? or what performance options should i run to monitor it?

Here is the query i am running:
SELECT tblTariffTLI.tarifftliID, tblTariffTLI.TLInumber,BasisCode,EquipmentType,Equ ipmentSize,tblTariffRate.Rate, tblTariffAmendment.EffectiveDate,
tblTariffAmendment.FileDate, tblTariffAmendment.ExpirationDate, tblTariffAmendment.ThruDate, tblTariffTLILocation.LocationCode ,
tblTariffTLILocation.OriginDestination, B.TariffTLIDetailId , tblTariffTLI.CommodityCode, tblTariffTLI.TariffCommodityID,
tblTariffAmendment.TariffAmendmentID
From tblTariffTLI
INNER JOIN tblTariffTLIDetail B on tblTariffTLI.TariffTLIID = b.Tarifftliid
and b.tarifftlidetailid in (select top 1 tarifftlidetailid from tbltarifftlidetail
where tbltarifftlidetail.tarifftliid = tbltarifftli.tarifftliid and tbltarifftlidetail.EffectiveDate <= '3/16/2004'
order by tbltarifftlidetail.tarifftlidetailid desc)
inner join tblTariffAmendment ON B.TariffAmendmentID = tblTariffAmendment.TariffAmendmentID
INNER JOIN tblTariffTLILocation on b.TariffTLIDetailID = tblTariffTLILocation.TariffTLIDetailID
INNER JOIN tblTariffRate ON B.TariffTLIDetailID = tblTariffRate.TariffTLIDetailID
INNER JOIN tblServiceCombo ON B.ServiceComboID = tblServiceCombo.ServiceComboID
WHERE (tblTariffTLI.TariffCommodityID in (68,1757)
and ((tblTariffTLILocation.OriginDestination = 'O' and tblTariffTLILocation.via = 'N'
and tblTariffTLILocation.LocationCode in ('DESTSFLA','FLAPORTS','ORIGSFLA','USMIAP') )
or ((tblTariffTLILocation.OriginDestination = 'D' and tblTariffTLILocation.via = 'N')
and tblTariffTLILocation.LocationCode in ('PACTBP') )) and (tblTariffAmendment.ExpirationDate >='3/16/2004'
or tblTariffAmendment.ExpirationDate is null) and ServiceContract = 0 ) ORDER BY tblTariffTLI.TLINumber

Thank you

Luis Barahona

[MAK]

1. Do a execution plan on this query and see whether there is any table scan
Create index based on the "where" condition and joins used.

2. It is not the no. of rows returned that determines the speed. It is the total # of rows sql server has to access in order to get these rows.

3. If you believe query plan is fetching the wrong index then hardcode the index by giving table hints (index=indexname)

4. If you think this query need to use all the processor then use MAXDOP option. Check BOL.