Solved

Optimize this sql 2005 query

Posted on 2013-10-22
19
61 Views
Last Modified: 2016-04-01
I'm still working in SQL 2005.
This query runs too long and I need to optimize it. Can anyone help me with it?

I have attached the table and the table variable declaration.

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

Open in new window

Tables.sql
0
Comment
Question by:dgerler
19 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
ID: 39593194
I'm by no means a master at SQL Server query tuning but ...

I see a couple of things here that could be causing you problems.  Without seeing the Execution Plan, it's hard to know for sure.

You don't say how large the tables are and you're joining on non indexes columns (join conditions are on store but indexes are on id).

I believe that this is causing you to have to do scans on the tables for each join since the optimizer doesn't have indexes to seek on for the join conditions - if the tables are large, that could cause significant overhead.

I'd think that is a major contributing factor to your performance issue - do you have an Execution Plan you can show ?

I seem to recall reading somewhere once that table variables also aren't necessarily the fastest thing around either, but I can't find that reference at the moment.

EDIT: Found it!  The following blog from the MS Customer Service team (again, for large numbers of rows) mentions how performance can suffer when using table variables for large numbers of rows.

http://blogs.msdn.com/b/psssql/archive/2010/08/24/query-performance-and-table-variables.aspx
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39593196
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?
0
 

Author Comment

by:dgerler
ID: 39593204
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

Open in new window

GetPeriod.sqlplan
0
 

Author Comment

by:dgerler
ID: 39593237
Looking at the execution plan, my problem is in determining the compStores rather than the bottom query.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39593271
looks to me like the execution plan only captured the top half, so that result would be expected (for that part).
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
ID: 39593285
I'm going to propose a small change that should reduce the number of conversions to/from varchar/datetime:
 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)

Open in new window

this leaves on side of a join condition unchanged but forces the other half back to a varchar for direct comparison as varchar.
(instead of converting both sides to datetime)

I would suggest that approach be adopted wherever it can be.

I also think sjwales has it right regarding indexes and that the joining fields need indexing, which I expect would include that YYYMMDD field.

If you can get the rest of the execution plan it may still be useful.
0
 

Author Comment

by:dgerler
ID: 39593984
Thanks Jimhorn.

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

Author Comment

by:dgerler
ID: 39593991
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]
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
ID: 39594660
You should also cluster the @temp_Stores table on the actual key, Store, and not an identity column.

DECLARE @temp_Stores TABLE ( Store int PRIMARY KEY )

If necessary, make sure you don't insert duplicate values when loading @temp_Stores.

[Btw, worst performance myth of all time: no matter what some "experts" say, an identity column should NEVER be considered a "default" clustering key.  The clustering key should istead always be carefully chosen for that specific table's data, NOT based on some nursey-rhyme-like saying about "identity always makes a good default clustering key".]
0
 

Author Comment

by:dgerler
ID: 39596310
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39596352
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.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
ID: 39596399
there are a set of unnecessary conversions to datetime in the where & group clauses, try this as a replacement:
where cd.yyyymmdd between @vBegDate and @vEndDate 
and (@Period != 'ytd' or datepart(dayofyear, cd.YYYYMMDD) <= datepart(dayofyear, @vEndDate))
and (@Period != 'mtd' or datepart(day,       cd.YYYYMMDD) <= datepart(day,       @vEndDate))
and (@Period != 'wtd' or datepart(weekday,   cd.YYYYMMDD) <= datepart(weekday,   @vEndDate))
group by Case @Period 
 when 'ytd' then datepart(month, cd.YYYYMMDD) 
 when 'mtd' then datepart(month, cd.YYYYMMDD) 
 when 'wtd' Then datepart(week,  cd.YYYYMMDD)

Open in new window

note in the exising code you are drawing datepart directly from the varchar variable @vEndDate; but forcing a convert to datetime on the varchar field YYYMMDD before each datepart. It appears that datepart will accept 'YYYYMMDD' directly.

I'd only expect a marginal improvement by this however.
0
 

Author Comment

by:dgerler
ID: 39597105
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

Open in new window

0
 

Author Comment

by:dgerler
ID: 39597112
Would the you say same unnecessary conversion is happening in lines 26 and 35 of the store selection section?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39599017
>>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

Rules of thumb
Avoid using functions on row values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
0
 

Author Comment

by:dgerler
ID: 39601896
Those changes helped marginally as you predicated.

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now