Dont use views. Views are slow....very slow....
You may perhaps want to try the Actual Execution Plan, so you can see what part of the query is taking the longest, and then you may have to add another index to the table, or switch the main table to a different table in the query.
With regards to your SQL I think one of the ways you could speed it up is placing your WHERE in the Inner Join.
CREATE Procedure getAdminReport
(
@eid varchar(500),
@from_month int,
@to_month int,
@from_year int,
@to_year int,
@desg int= null
)
as
CREATE TABLE #2
(
LIDList Int
)
CREATE INDEX idx1 ON #2 (LIDList)
INSERT INTO #2
SELECT Convert(Int, NullIf(SubString(',' + @eid + ',' , ID ,
CharIndex(',' , ',' + @eid + ',' , ID) - ID) , '')) AS LIDList
FROM tblToolsStringParserCounter
WHERE ID <= Len(',' + @eid + ',') AND SubString(',' +
@eid + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + @eid + ',' , ID) - ID > 0
select EC.eid,(ED.first_name+' '+ED.last_name) as FullName,st.state_name,region.state_name as region,city.state_name as city,ED.designation,ED.employee_id,
sum(Case when DB.cd_type=0 Then 1 Else 0 End) as cnt_clinic,
sum(Case when DB.cd_type=1 Then 1 Else 0 End) as cnt_camp,
sum(Case when DB.activity_type=0 Then 1 Else 0 End) as cnt_spir,
sum(Case when DB.activity_type=1 Then 1 Else 0 End) as cnt_breat,
sum(Case when DB.activity_type=2 Then 1 Else 0 End) as cnt_vitalo,
sum(Case when DB.timing=0 Then 1 Else 0 End) as cnt_morning,
sum(Case when DB.timing=1 Then 1 Else 0 End) as cnt_eve,
sum(Case when DB.timing=2 Then 1 Else 0 End) as cnt_full
from doc_business_data DB
inner join educator_call_dtls EC on DB.call_id=EC.call_id
inner join doctor_master DM on DM.doc_id=DB.doc_id
inner join educator_master ED on ED.eid=EC.eid
AND ED.designation <> 'BM'
AND ED.designation <> 'SM'
AND ED.designation <> 'SuperAdmin'
inner join educator_state_master city on city.state_city_id=ED.city_id
inner join educator_state_master region on region.state_city_id=city.parent_id
inner join educator_state_master st on st.state_city_id = region.parent_id
where EC.eid in (select LIDList FROM #2) AND ED.designation <> 'BM' AND ED.designation <> 'SM' AND ED.designation <> 'SuperAdmin' and
(((year(EC.call_date) > @from_year) OR
What that should do is narrow down the number of records needed in the join. If you can do that with more of the joins this will help a lot!
If you imagine you have 1,000,000 rows, but only 350,000 of them are the ones you are interested in, only joining on the 350,000 will be faster!
Good Luck!