Frank,

What I typically do when I run across a query that I believe is running slow, is to see if there are any table scans that are occuring. To see this, click on Query->Display Estimated Execution Plan with the query you are running. Look to see if there are any table scans or look to see the 'Cost' value for certain joins. IF there are table scans or a certain join has a high cost %, then you should be able to know where the problem lies in the poor performance Then you can add an index to the culprit or change a join condition and should see performance gains.

HTH

Bob

>Hi,
>
>I have this query:
>
>

	SELECT [ben_pk]
>	      ,[ben_code]
>	      ,[ben_desc]
>	      ,[ben_typfk]
>	      ,[typ_name]
>	  FROM [BenefitCodes]
>		INNER JOIN [ClaimTypes] on ben_typfk = typ_pk 
>		WHERE typ_pk = '88599CCF-202C-ED45-2F8A-E9C999C62282'
>				AND Ben_pk IN 
>			(SELECT sob_benfk
>				FROM ScheduleOfBenefits t1
>				INNER JOIN PlanCodes ON sob_codfk = cod_pk
>				INNER JOIN Employees ON cod_clifk = emp_clifk
>				WHERE sob_typfk = '88599CCF-202C-ED45-2F8A-E9C999C62282' or (sob_typfk is null and cod_typfk = '88599CCF-202C-ED45-2F8A-E9C999C62282')
>					AND emp_pk = '0626ABC9-2BCB-104D-0139-E321DE7E4E17'
>					AND sob_effective = 
>						(select Max(sob_effective) 
>							from ScheduleOfBenefits t2 
>							where t2.sob_benfk = t1.sob_benfk 
>								and t2.sob_codfk = t1.sob_codfk
>								and t2.sob_effective <= '20120801'))

>
>which is running quite slowly, taking between 25 and 30 seconds to run.
>
>I have 314 records in the BenefitCodes table.
>
>ClaimTypes has 5 records.
>
>ScheduleOfBenefits has 7318.
>
>PlanCodes has 224
>
>Employees has 11134.
>
>Is there a better/faster way to do this?