Can any one suggest how i might improve the performance of this store procedure? It seem to take a while to run and im sure there is a better way of writing it...
Declare
@Year_Local varchar(4),
@Quarter_Local varchar(9),
@Month_Local varchar(3),
@Person_name_Local varchar(100)
Select t1.Sales_Person, t1.Customer_Name, t2.Meeting_Category, t2.Meeting_Type, ISNULL(t2.NoOfVisits,0) as NoOfVisits
From
(Select Sales_Person,TimeCard_Acct, Customer_Name
From Sales_Matrix inner join TimeCard_Targeted_Lookup
on Sales_Matrix.AccountName =TimeCard_Targeted_Lookup.Maginus_Targ_Acct
inner join Time_Card_Security
On Sales_Matrix.Silo = Time_Card_Security.Silo_Number
where [YEAR] = @Year_Local
and (@Quarter_Local Is Null Or Quarter_Name = @Quarter_Local)
and (@Month_Local Is NULL or [MONTH] = @Month_Local)
and IS_MEMBER(Time_Card_Security.Active_Directory_Path) = 1
and Sales_Person = @Person_name_Local
Group By Sales_Person,TimeCard_Acct, Customer_Name)t1
Left outer join
(Select Person_name, MaginusAccount,Account_Name, Meeting_Type,Meeting_Category, COUNT(S_Key) as NoOfVisits, Person_Target,Targeted_Account
From dbo.V_TimeCard inner join Time_Card_Security
on V_TimeCard.Silo_1 = Time_Card_Security.Silo_Number
Where [YEAR] = @Year_Local
And (@Quarter_Local Is Null Or [Quarter] = @Quarter_Local)
And (@Month_Local Is NULL or [MONTH] = @Month_Local)
And Meeting_Category <>''
And Silo_1 in ('1','2','3','4','5','6','7','8','98','99')
AND IS_MEMBER(Time_Card_Security.Active_Directory_Path) = 1
AND Person_name = @Person_name_Local
Group by Person_name,MaginusAccount, Account_Name, Meeting_Type,Meeting_Category, Person_Target,Targeted_Account)t2
on t1.TimeCard_Acct =t2.MaginusAccount and t1.Sales_Person =t2.Person_name
Declare
@Year_Local varchar(4),
@Quarter_Local varchar(9),
@Month_Local varchar(3),
@Person_name_Local varchar(100)
Select t1.Sales_Person, t1.Customer_Name, t2.Meeting_Category, t2.Meeting_Type, ISNULL(t2.NoOfVisits,0) as NoOfVisits
From
(Select Sales_Person,TimeCard_Acct, Customer_Name
From Sales_Matrix inner join TimeCard_Targeted_Lookup
on Sales_Matrix.AccountName =TimeCard_Targeted_Lookup.Maginus_Targ_Acct
inner join Time_Card_Security
On Sales_Matrix.Silo = Time_Card_Security.Silo_Number
where [YEAR] = @Year_Local
and (@Quarter_Local Is Null Or Quarter_Name = @Quarter_Local)
and (@Month_Local Is NULL or [MONTH] = @Month_Local)
and IS_MEMBER(Time_Card_Security.Active_Directory_Path) = 1
and Sales_Person = @Person_name_Local
Group By Sales_Person,TimeCard_Acct, Customer_Name)t1
Left outer join
(Select Person_name, MaginusAccount,Account_Name, Meeting_Type,Meeting_Category, COUNT(S_Key) as NoOfVisits, Person_Target,Targeted_Account
From dbo.V_TimeCard inner join Time_Card_Security
on V_TimeCard.Silo_1 = Time_Card_Security.Silo_Number
Where [YEAR] = @Year_Local
And (@Quarter_Local Is Null Or [Quarter] = @Quarter_Local)
And (@Month_Local Is NULL or [MONTH] = @Month_Local)
And Meeting_Category <>''
And Silo_1 in ('1','2','3','4','5','6','7','8','98','99')
AND IS_MEMBER(Time_Card_Security.Active_Directory_Path) = 1
AND Person_name = @Person_name_Local
Group by Person_name,MaginusAccount, Account_Name, Meeting_Type,Meeting_Category, Person_Target,Targeted_Account)t2
on t1.TimeCard_Acct =t2.MaginusAccount and t1.Sales_Person =t2.Person_name