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


Why not use a tablename before the field name?
That way query is VERY unreadable.

 on ben_typfk = typ_pk ?????

Which is which?


Try this (change the fields when neccessary)

SELECT  ??????????.[ben_pk]
      , ??????????.[ben_code]
      , ??????????.[ben_desc]
      , ??????????.[ben_typfk]
      , ??????????.[typ_name]
FROM [BenefitCodes]
INNER JOIN [ClaimTypes] on  ??????????.ben_typfk =  ??????????.typ_pk 
INNER JOIN (SELECT sob_benfk
            FROM ScheduleOfBenefits t1
	    INNER JOIN PlanCodes ON  ??????????.sob_codfk =  ??????????.cod_pk
	    INNER JOIN Employees ON  ??????????.cod_clifk =  ??????????.emp_clifk
	    WHERE COALESCE( ??????????.sob_typfk, ??????????.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') Tbl1

       ON [BenefitCodes].Ben_pk = Tbl1.sob_benfk
WHERE ??????????.typ_pk = '88599CCF-202C-ED45-2F8A-E9C999C62282'

NOT TESTED!