Optimize this sql 2005 query

could you also provide all of the variables - as you use them
I think we need to see how you are declaring them and what typical values they hold.

Also, while I'm here why are you storing dates as [YYYYMMDD] [varchar](8) ?

do you an execution plan (.sqlplan file) you can attach?

Dates stored as YYYYMMDD as a Legacy that I can't get away from right now.

The number of stores is between 1 and 100 depending on the selection of the user.

The full Stored Procedure...

/****** Object:  StoredProcedure [dbo].[spGet_PeriodToDate_Calculation]    Script Date: 10/22/2013 23:03:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGet_PeriodToDate_Calculation] 
	@vStore varchar(50)
	, @vBegDate varchar(8)
	, @vEndDate varchar(8)
	, @vCalc varchar(20)
	, @Period varchar(20)
	, @CompStores varchar(20)
AS
SET NOCOUNT ON
DECLARE @temp_Stores TABLE ( temp_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY
	, Store int)

IF ISNUMERIC(@vStore) = 0 
    BEGIN
	/* All Stores or store group */
	IF LOWER(@vStore) = 'all stores'
	    BEGIN
		/* Get list of all store numbers */
		INSERT @temp_Stores
			SELECT s.[store] FROM [Stores] s left join calculationdetail cd on s.store = cd.store and cd.id = 'net1'
				where (@compStores != '1' or dateadd(d, -364, Convert(datetime, @vEndDate)) = Convert(datetime,cd.YYYYMMDD))
				and (@compStores != '0' or Convert(datetime, @vEndDate) = Convert(datetime,cd.YYYYMMDD))
	    END
	ELSE
	    BEGIN
		/* Get list of store numbers for store group */
		INSERT @temp_Stores
			SELECT sg.[store] FROM [StoreGroups] sg left join calculationdetail cd on sg.store = cd.store and cd.id = 'net1'
				where [groupname] = @vStore
				and (@compStores != '1' or dateadd(d, -364, Convert(datetime, @vEndDate)) = Convert(datetime,cd.YYYYMMDD))
				and (@compStores != '0' or Convert(datetime, @vEndDate) = Convert(datetime,cd.YYYYMMDD))
	    END
    END
ELSE
    BEGIN
	/* add individual store into table */
	INSERT @temp_Stores
		SELECT @vStore
    END

--set statistics time on
select 
min(cd.yyyymmdd) as yyyymmdd, max(cd.id) as id, isnull(sum(cd.amount),0) as amount, isnull(sum(cd2.amount),0) as comp
from @temp_Stores sg 
	join calculationdetail cd on sg.store = cd.store and cd.id = @vCalc
	left join calculationdetail cd2 on sg.store = cd2.store and dateadd(d, -364, Convert(datetime, cd.YYYYMMDD)) = Convert(datetime,cd2.YYYYMMDD) and cd.id = cd2.id
where cd.yyyymmdd between @vBegDate and @vEndDate 
and (@Period != 'ytd' or datepart(dayofyear, Convert(datetime,cd.YYYYMMDD)) <= datepart(dayofyear, @vEndDate))
and (@Period != 'mtd' or datepart(day, Convert(datetime,cd.YYYYMMDD)) <= datepart(day, @vEndDate))
and (@Period != 'wtd' or datepart(weekday, Convert(datetime,cd.YYYYMMDD)) <= datepart(weekday, @vEndDate))
group by Case @Period 
	when 'ytd' then datepart(month, Convert(datetime,cd.YYYYMMDD)) 
	when 'mtd' then datepart(month, Convert(datetime,cd.YYYYMMDD)) 
	when 'wtd' Then datepart(week, Convert(datetime,cd.[YYYYMMDD])) END

Select allOpen in new window

GetPeriod.sqlplan

Looking at the execution plan, my problem is in determining the compStores rather than the bottom query.

looks to me like the execution plan only captured the top half, so that result would be expected (for that part).

Thanks Jimhorn.

Attached the 2nd query execution plan after making the change suggested by PortletPaul.
GetPeriod2.sqlplan
TimeStatistics.rpt

Also, these indexes are already on the table.


CREATE CLUSTERED INDEX [idx_YYYYMMDD] ON [dbo].[CalculationDetail]
(
      [YYYYMMDD] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


CREATE UNIQUE NONCLUSTERED INDEX [idx_CalculationDetail4] ON [dbo].[CalculationDetail]
(
      [YYYYMMDD] ASC,
      [Store] ASC,
      [ID] ASC,
      [Report] ASC
)
INCLUDE ( [Client], [Amount])
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

OK. Those steps helped the query significantly, but it still takes to long.
This query against 85 stores for Year To Date sales went from taking 71 seconds to 8.6 seconds. I appreciate your help with this.

I'd like it to be much better still.

I have attached the stored procedure as it is (old code commented out) and the execution plan.
GetPeriod3.sqlplan
GetPeriod4.sqlplan

you missed attaching the new code, but it's better if you just add it as a code block, click Code in the toolbar and paste between the 2 tags.

Sorry about the code.
Here it is as it was when sending that.

/****** Object:  StoredProcedure [dbo].[spGet_PeriodToDate_Calculation]    Script Date: 10/23/2013 00:20:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGet_PeriodToDate_Calculation] 
	@vStore varchar(50)
	, @vBegDate varchar(8)
	, @vEndDate varchar(8)
	, @vCalc varchar(20)
	, @Period varchar(20)
	, @CompStores varchar(20)
AS
SET NOCOUNT ON
DECLARE @temp_Stores TABLE ( temp_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY, Store int)

IF ISNUMERIC(@vStore) = 0 
    BEGIN
	/* All Stores or store group */
	IF LOWER(@vStore) = 'all stores'
	    BEGIN
		/* Get list of all store numbers */
		INSERT @temp_Stores
			SELECT s.[store] FROM [Stores] s left join calculationdetail cd on s.store = cd.store and cd.id = 'net1'
				where (@compStores != '1' or dateadd(d, -364, Convert(datetime, @vEndDate)) = Convert(datetime,cd.YYYYMMDD))
				and (@compStores != '0' or Convert(datetime, @vEndDate) = Convert(datetime,cd.YYYYMMDD))
	    END
	ELSE
	    BEGIN
		/* Get list of store numbers for store group */
		INSERT @temp_Stores
			SELECT sg.[store] FROM [StoreGroups] sg left join calculationdetail cd on sg.store = cd.store and cd.id = 'net1'
				where [groupname] = @vStore
				and (@compStores != '1' or dateadd(d, -364, Convert(datetime, @vEndDate)) = Convert(datetime,cd.YYYYMMDD))
				and (@compStores != '0' or Convert(datetime, @vEndDate) = Convert(datetime,cd.YYYYMMDD))
	    END
    END
ELSE
    BEGIN
	/* add individual store into table */
	INSERT @temp_Stores
		SELECT @vStore
    END

--set statistics time on
select 
min(cd.yyyymmdd) as yyyymmdd, max(cd.id) as id, isnull(sum(cd.amount),0) as amount, isnull(sum(cd2.amount),0) as comp
from @temp_Stores sg 
	join calculationdetail cd on sg.store = cd.store and cd.id = @vCalc
	--left join calculationdetail cd2 on sg.store = cd2.store and dateadd(d, -364, Convert(datetime, cd.YYYYMMDD)) = Convert(datetime,cd2.YYYYMMDD) and cd.id = cd2.id
	LEFT JOIN calculationdetail cd2
        ON sg.store = cd2.store
       AND cd.id = cd2.id
       AND cd2.YYYYMMDD = convert(varchar(8),dateadd(d, -364, Convert(datetime, cd.YYYYMMDD, 112)),112)
                                          
where cd.yyyymmdd between @vBegDate and @vEndDate 
and (@Period != 'ytd' or datepart(dayofyear, Convert(datetime,cd.YYYYMMDD)) <= datepart(dayofyear, @vEndDate))
and (@Period != 'mtd' or datepart(day, Convert(datetime,cd.YYYYMMDD)) <= datepart(day, @vEndDate))
and (@Period != 'wtd' or datepart(weekday, Convert(datetime,cd.YYYYMMDD)) <= datepart(weekday, @vEndDate))
group by Case @Period 
	when 'ytd' then datepart(month, Convert(datetime,cd.YYYYMMDD)) 
	when 'mtd' then datepart(month, Convert(datetime,cd.YYYYMMDD)) 
	when 'wtd' Then datepart(week, Convert(datetime,cd.YYYYMMDD)) END

Select allOpen in new window

Would the you say same unnecessary conversion is happening in lines 26 and 35 of the store selection section?

>>Would the you say same unnecessary conversion is happening in lines 26 and 35 of the store selection section?
Yes

Try to minimize all conversions, in both those cases manipulate the variable back to varchar and leave the data unconverted. Look for all such opportunities.

In essence aim for "sargable" predicates:
http://en.wikipedia.org/wiki/Sargable

Those changes helped marginally as you predicated.

I'm looking at the information about "sargable" predicates now.