Hi chri,

i checked my query and executedb in read only server 192.168.3.201.

it will executed with in 90 secs in liked server.but in reporting server it executing only 5-15 secs.

The query is shown below.It will executed the same result but will executed faster than char index.

declare @date1 datetime,@date2 datetime;

set @date1='01/20/2010';

set @date2='01/20/2010';

with F3F4count(F3Count,Districtid) as

(

select (select distinct count(distinct br_beneficiaryid) as F3Count from

[192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryvisitrecords bvr

INNER JOIN [192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryregistration br

ON bvr.vr_beneficiaryid=br.br_beneficiaryId where vr_formsfilled like '%f4%'

and convert(varchar,vr_visitDate,101) between @date1 and @date2 and

br.br_DistrictId = districtId ),Districtid

from [192.168.3.201].MASTERDATA.HIHLMain.District

group by districtid

union

select (select distinct count(distinct br_beneficiaryid) as F3Count from

[192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryvisitrecords bvr

inner JOIN [192.168.3.201].[fdhs_server].TELEMONITORING.beneficiaryregistration br

ON bvr.vr_beneficiaryid=br.br_beneficiaryId where vr_formsfilled like '%f3%'

and convert(varchar,vr_visitDate,101) between @date1 and @date2 and

br.br_DistrictId = districtId ),Districtid

from [192.168.3.201].MASTERDATA.HIHLMain.District

group by districtid

)

select Districtid as District,sum(F3count) as [F3 F4 Count] from F3F4count

group by districtid

like the same query the second query is also taking 10 mins in linked server where the same query executed in reporting server is 10 to 20 secs only.

can you increase this query performance.Appreciated.