Store Procedure

[NotSQL]

Hi,
Im having issues with the performance of this store procedure. does anyone have any ideas how i might improve it?

Declare @Year_Local varchar(4), @Quarter_Local varchar(9), @Month_Local varchar(3), @Silo_Local varchar(2)


--VisitedTargetedSiloAccounts

Select Person_name, MaginusAccount, Account_Name, 'Person Target Visit' as SiloTarget
from V_TimeCard inner join Time_Card_Security
on V_TimeCard.Silo_1 = Time_Card_Security.Silo_Number inner join Arrow_Silo_Description
On V_TimeCard.Silo_1 = Arrow_Silo_Description.Arrow_Silo_ID
Where Person_Target = 1
and V_TimeCard.[YEAR] = @Year_Local
and (@Quarter_Local Is Null Or V_TimeCard.[Quarter] = @Quarter_Local)
and (@Month_Local Is NULL or V_TimeCard.[MONTH] = @Month_Local)
and (@Silo_Local IS NULL or Silo_1 = @Silo_Local)
and IS_MEMBER(Time_Card_Security.Active_Directory_Path) = 1
Group by Person_name, MaginusAccount, Account_Name

Union All

Select Person_name, MaginusAccount, Account_Name, 'Silo Target Visit' as SiloTarget
from V_TimeCard inner join Time_Card_Security
on V_TimeCard.Silo_1 = Time_Card_Security.Silo_Number inner join Arrow_Silo_Description
On V_TimeCard.Silo_1 = Arrow_Silo_Description.Arrow_Silo_ID
Where Silo_Target = 1
and V_TimeCard.[YEAR] = @Year_Local
and (@Quarter_Local Is Null Or V_TimeCard.[Quarter] = @Quarter_Local)
and (@Month_Local Is NULL or V_TimeCard.[MONTH] = @Month_Local)
and (@Silo_Local IS NULL or Silo_1 = @Silo_Local)
and IS_MEMBER(Time_Card_Security.Active_Directory_Path) = 1
Group by Person_name, MaginusAccount, Account_Name

Union ALL

Select Person_name,MaginusAccount, Account_Name, 'Un-Targeted Visit' as SiloTarget
from V_TimeCard inner join Time_Card_Security
on V_TimeCard.Silo_1 = Time_Card_Security.Silo_Number inner join Arrow_Silo_Description
On V_TimeCard.Silo_1 = Arrow_Silo_Description.Arrow_Silo_ID
where Person_Target IS NULL and Silo_Target is null
and V_TimeCard.[YEAR] = @Year_Local
and (@Quarter_Local Is Null Or V_TimeCard.[Quarter] = @Quarter_Local)
and (@Month_Local Is NULL or V_TimeCard.[MONTH] = @Month_Local)
and (@Silo_Local IS NULL or Silo_1 = @Silo_Local)
and IS_MEMBER(Time_Card_Security.Active_Directory_Path) = 1

Group by Person_name, Arrow_Silo_Name,MaginusAccount, Account_Name

Union ALL

Select t1.*, 'Targeted Non-Visit' as SiloTarget
From
(SELECT Sales_Matrix.Sales_Person, TimeCard_Targeted_Lookup.TimeCard_Acct, TimeCard_Targeted_Lookup.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 INNER JOIN
D_Reps ON Sales_Matrix.Sales_Person = D_Reps.Person_name INNER JOIN
Arrow_Silo_Description ON D_Reps.Silo_1 = Arrow_Silo_Description.Arrow_Silo_ID
WHERE Sales_Matrix.[YEAR] = @Year_Local
and (@Quarter_Local Is Null Or Sales_Matrix.Quarter_Name = @Quarter_Local)
and (@Month_Local Is NULL or Sales_Matrix.[MONTH] = @Month_Local)
and (@Silo_Local IS NULL or Silo = @Silo_Local)
and IS_MEMBER(Time_Card_Security.Active_Directory_Path) = 1

GROUP BY Sales_Matrix.Sales_Person, TimeCard_Targeted_Lookup.TimeCard_Acct, TimeCard_Targeted_Lookup.Customer_Name)t1

Left outer join

(Select Person_name, MaginusAccount, Account_Name
from V_TimeCard inner join Time_Card_Security
on V_TimeCard.Silo_1 = Time_Card_Security.Silo_Number inner join Arrow_Silo_Description
on V_TimeCard.Silo_1 = Arrow_Silo_Description.Arrow_Silo_ID
where V_TimeCard.[YEAR] = @Year_Local
and (@Quarter_Local Is Null Or V_TimeCard.[Quarter] = @Quarter_Local)
and (@Month_Local Is NULL or V_TimeCard.[MONTH] = @Month_Local)
and(@Silo_Local IS NULL or Silo_1 = @Silo_Local)
and IS_MEMBER(Time_Card_Security.Active_Directory_Path) = 1

Group by Person_name,MaginusAccount, Account_Name)t2
on t1.Sales_Person =t2.Person_name and t1.TimeCard_Acct=t2.MaginusAccount
where t2.Person_name is null

 

[NotSQL]

Just to add to this i've just broken up the store procedure to on run the first part

Declare @Year_Local varchar(4) = '2010', @Quarter_Local varchar(9)= null, @Month_Local varchar(3)=null, @Silo_Local varchar(2) = null


Select Person_name, MaginusAccount, Account_Name, 'Person Target Visit' as SiloTarget
from V_TimeCard inner join Time_Card_Security
on V_TimeCard.Silo_1 = Time_Card_Security.Silo_Number inner join Arrow_Silo_Description
On V_TimeCard.Silo_1 = Arrow_Silo_Description.Arrow_Silo_ID
Where Person_Target = 1
and V_TimeCard.[YEAR] = @Year_Local
and (@Quarter_Local Is Null Or V_TimeCard.[Quarter] = @Quarter_Local)
and (@Month_Local Is NULL or V_TimeCard.[MONTH] = @Month_Local)
and (@Silo_Local IS NULL or V_TimeCard.Silo_1 = @Silo_Local)
and IS_MEMBER(Time_Card_Security.Active_Directory_Path) = 1
Group by Person_name, MaginusAccount, Account_Name

This still takes over 12 seconds... Any help would be appreciated..

 

[RiverGuy]

What does your execution plan say?