Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query optimization

Status
Not open for further replies.

NotSQL

Technical User
May 17, 2005
205
GB
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top