>>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!
Hi Boris,
leaving out the table name is just a hangover from the VMP way of naming fields so that you can tell from the field name what table it comes from. Will adding in the table names speed things up or just make it more readable? Or was there another change you made that I can't spot?
