2

We have a table, which is partitioned through a date field into separate years.

There is a view over all of these tables (Call)

Schema is as follows:

CREATE TABLE [dbo].[Call_2015](
    [calID] [uniqueidentifier] NOT NULL,
    [calPackageID] [int] NULL,
    [calClientID] [int] NULL,
    [calStartDate] [datetime] NOT NULL,
    [calEndDate] [datetime] NOT NULL,
    [calTimeIn] [char](5) NULL,
    [calTimeOut] [char](5) NULL,
    [calMinutes] [smallint] NULL,
    [calPreferredTimeIn] [char](5) NULL,
    [calPreferredTimeOut] [char](5) NULL,
    [calActualTimeIn] [char](5) NULL,
    [calActualTimeOut] [char](5) NULL,
    [calActualMinutes] [smallint] NULL,
    [calConfirmed] [smallint] NULL,
    [calCarerID] [int] NULL,
    [calRepCarerID] [int] NULL,
    [calOriginalCarerID] [int] NULL,
    [calContractID] [int] NULL,
    [calNeedID] [int] NULL,
    [calMedicationID] [int] NULL,
    [calFrequency] [smallint] NULL,
    [calFromDate] [datetime] NULL,
    [calWeekNo] [smallint] NULL,
    [calAlert] [smallint] NULL,
    [calNoLeave] [smallint] NULL,
    [calTimeCritical] [smallint] NULL,
    [calStatus] [smallint] NULL,
    [calClientAwayReasonID] [int] NULL,
    [calCarerAwayReasonID] [int] NULL,
    [calOutsideShift] [smallint] NULL,
    [calHistoryID] [int] NULL,
    [calInvoiceID] [int] NULL,
    [calWagesheetID] [int] NULL,
    [calReasonID] [int] NULL,
    [calCallConfirmID] [varchar](50) NULL,
    [calCreated] [datetime] NULL,
    [calUpdated] [datetime] NULL,
    [calVariation] [int] NULL,
    [calVariationUserID] [int] NULL,
    [calException] [smallint] NULL,
    [calRetained] [smallint] NULL,
    [calDoubleUpID] [uniqueidentifier] NULL,
    [calDoubleUpOrder] [smallint] NULL,
    [calNeedCount] [smallint] NULL,
    [calNoStay] [smallint] NULL,
    [calCoverCarerID] [int] NULL,
    [calPayAdjustment] [real] NULL,
    [calChargeAdjustment] [real] NULL,
    [calTeamID] [int] NULL,
    [calExpenses] [money] NULL,
    [calMileage] [real] NULL,
    [calOverrideStatus] [smallint] NULL,
    [calLocked] [smallint] NULL,
    [calDriver] [smallint] NULL,
    [calPostcode] [char](10) NULL,
    [calDayCentreID] [int] NULL,
    [calMustHaveCarer] [smallint] NULL,
    [calRoleID] [int] NULL,
    [calUnavailableCarerID] [int] NULL,
    [calClientInformed] [smallint] NULL,
    [calFamilyInformed] [smallint] NULL,
    [calMonthlyDay] [smallint] NULL,
    [calOriginalTimeIn] [char](5) NULL,
    [calLeadCarer] [smallint] NULL,
    [calCallTypeID] [int] NULL,
    [calActualStartDate] [datetime] NULL,
    [calActualEndDate] [datetime] NULL,
    [Table_Year] [int] NOT NULL,
 CONSTRAINT [PK_Call_2015] PRIMARY KEY CLUSTERED 
(
    [Table_Year] ASC,
    [calID] ASC,
    [calStartDate] ASC,
    [calEndDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Call_2015]  WITH CHECK ADD  CONSTRAINT [CK_Call_Year_2015] CHECK  (([Table_Year]=(2015)))
GO

ALTER TABLE [dbo].[Call_2015] CHECK CONSTRAINT [CK_Call_Year_2015]
GO

ALTER TABLE [dbo].[Call_2015]  WITH CHECK ADD  CONSTRAINT [CK_calStartDate_2015] CHECK  (([calStartDate]>=CONVERT([datetime],'01 Jan 2015 00:00:00',(0)) AND [calStartDate]<=CONVERT([datetime],'31 DEC 2015 23:59:59',(0))))
GO

ALTER TABLE [dbo].[Call_2015] CHECK CONSTRAINT [CK_calStartDate_2015]
GO

ALTER TABLE [dbo].[Call_2015] ADD  CONSTRAINT [DF_Call_2015_Table_Year]  DEFAULT ((2015)) FOR [Table_Year]
GO

The update to the table is as follows:

UPDATE Call SET
        calStartDate = CASE 
            WHEN calFrequency = 14 THEN dbo.funDate(@MonthlyDay, MONTH(calStartDate), YEAR(calStartDate))
            WHEN calFrequency IN (15,16) THEN dbo.funMonthlyCallDate(calFrequency, @MonthlyDay, calStartDate)
            ELSE DateAdd(d, @StartDay-1, (calStartDate - datepart(dw,calStartDate)+1)) 
        END,
        calEndDate = CASE 
            WHEN calFrequency = 14 THEN dbo.funDate(@MonthlyDay + @EndDay - @StartDay, MONTH(calStartDate), YEAR(calStartDate))
            WHEN calFrequency IN (15,16) THEN DATEADD(D, @EndDay - @StartDay, dbo.funMonthlyCallDate(calFrequency, @MonthlyDay, calStartDate))
            ELSE DateAdd(d, @StartDay-1+@DayCount, (calStartDate - datepart(dw,calStartDate)+1)) 
        END,
        calTimeIn = @TimeIn,
        calTimeOut = @TimeOut,
        calMinutes = @Minutes,
        calMonthlyDay = @MonthlyDay,
        calClientInformed = Null, 
        calFamilyInformed = Null
    WHERE calPackageID = @PackageID
    AND calClientID = @ClientID
    AND calWeekNo = @WeekNo
    AND (DatePart(dw, calStartDate) = @OriginalDay OR calFrequency IN (14,15,16))
    AND calStartDate BETWEEN @StartDate AND @EndDate
    AND (calInvoiceID = 0 OR calInvoiceID Is Null OR @InvoicesFinalised = 1)
    AND (calWagesheetID = 0 OR calWagesheetID Is Null OR @WagesFinalised = 1)
    AND (calLocked = 0 OR calLocked Is Null)
    AND (Table_Year = YEAR(@StartDate) 
            OR Table_Year =YEAR(@EndDate))

The SP updates a batch of rows dependant of input into @StartDate and @EndDate (updates all rows with a calStartDate between the two)

The problem then comes with the execution plan. There are huge IO costs to the operation, and I've nailed it down to how SQL is dealing with the update.

Currently we have 20 of these tables; partitioned per year. Each update is causing an update of every single table's indexes, regardless of whether the table is actually touched by the update operation or not.

Execution Plan

Below this section it goes on to update, in the exact same manner, every table in the view.

I cannot see why this is, as I have specified the Table_Year (which the table is partitioned on) within the query text. Shouldn't SQL only update the necessary table?

  • Thought about deleting the indexes first, doing the update batch, then recreating the indexes? Although it seems strange that it would update indexes in different tables, its entirely possible. Or possibly deleting the indexes in the first table, do the update and check if its still updating all the other indexes. What does your index on the table look like? – blobbles Aug 4 '14 at 13:33
  • @blobbles all the indexes are in the same table, they're in different partitions. – Aaron Bertrand Aug 4 '14 at 13:35
  • Oh right, then it shouldn't really surprise you that it update the entire index right? I mean, its the same index, just across partitions. It sounds like you are loading data into a data warehouse, most commonly you delete the index on the data warehouse before loading, then recreate it after. Doing a bulk copy (minimally logged) is usually the way to go as well. – blobbles Aug 4 '14 at 13:36
  • Sorry that wasn't clear, I should have said "It sounds like you are doing something similar to data loading a data warehouse." – blobbles Aug 4 '14 at 13:45
  • @blobbles Well, if the indexes are partition-aligned, then SQL Server should be able to only affect the relevant partitions. If they're not partition-aligned, all bets are off. Dropping and re-creating all of the indexes, affecting all partitions, when you are updating rows in only one of the partitions, seems like a lot of extra busy work to me. In the right circumstances, partitioning should provide a way to only affect the relevant partitions, but the indexes have to be partition-aligned for that to work. – Aaron Bertrand Aug 4 '14 at 13:45
2

Below this section it goes on to update, in the exact same manner, every table in the view. I cannot see why this is, as I have specified the Table_Year (which the table is partitioned on) within the query text. Shouldn't SQL only update the necessary table?

The view meets all the partitioning requirements for both Table_Year and calStartDate. The latter column is modified by the UPDATE statement so the query optimizer has to produce a plan that is capable of moving rows between partitions.

In fact, rows could not move between partitions in this case because there is a 1:1 relationship between Table_Year and year-wise values of calStartDate, but the steps involved in that reasoning are far too opaque for the optimizer.

The new value for calStartDate is based on a complex expression that references variables. The query plan will be cached and could be reused when the variables have different values, which is just another factor that means the plan must be very general.

All these considerations lead to a plan that does not feature static partition elimination. It does, however, feature dynamic partition elimination:

On the reading side, the string of Filter operators immediately below the Concatenation are all startup filters. They evaluate their predicate before the subtree is executed. If the predicate evaluates to false, the subtree under the Filter is not executed.

The overall effect is that only tables under the view that could hold qualifying rows (depending on the runtime variable values) are accessed. Notice the execution plan only shows rows being read from one of the base tables, and the Actual Executions property for all other base tables is zero; these operators were not executed at all, at runtime.

In the plan fragment below, the startup Filters ensure that only the green operators execute; the red ones never start at all:

Read-side plan fragment

On the writing side, The normal (not 'startup') Filter just to the right of each Clustered Index Update operator ensures that only changes for the current table are passed on. In the example plan, only one Clustered Index Update (and its associated nonclustered index maintenance operators) receives any rows:

Write-side plan fragment

2

[Note: also answered at answers.SQLPerformance.com.]

These aren't actually partitioned tables, and even if they were, partition elimination wouldn't really work for updating indexes unless all indexes were also partition-aligned.

Since you are using Express Edition and can't actually use partitioning, I have a different approach to recommend: dynamic UPDATEs that only affect the table(s) represented in @StartDate / @EndDate. You'll have to populate the list of parameters twice; once with their data types - this should be easy as I presume these are declared somewhere already.

DECLARE 
  @StartDate DATE = '20140605',
  @EndDate   DATE = '20150201';


DECLARE 
  @SQL NVARCHAR(MAX) = N'',
  @baseSQL NVARCHAR(MAX) = N'UPDATE dbo.[Call_$y$] SET
      calStartDate = CASE 
          WHEN calFrequency = 14 THEN dbo.funDate(@MonthlyDay, MONTH(calStartDate), YEAR(calStartDate))
          WHEN calFrequency IN (15,16) THEN dbo.funMonthlyCallDate(calFrequency, @MonthlyDay, calStartDate)
          ELSE DateAdd(d, @StartDay-1, (calStartDate - datepart(dw,calStartDate)+1)) 
      END,
      calEndDate = CASE 
          WHEN calFrequency = 14 THEN dbo.funDate(@MonthlyDay + @EndDay - @StartDay, MONTH(calStartDate), YEAR(calStartDate))
          WHEN calFrequency IN (15,16) THEN DATEADD(D, @EndDay - @StartDay, dbo.funMonthlyCallDate(calFrequency, @MonthlyDay, calStartDate))
          ELSE DateAdd(d, @StartDay-1+@DayCount, (calStartDate - datepart(dw,calStartDate)+1)) 
      END,
      calTimeIn = @TimeIn,
      calTimeOut = @TimeOut,
      calMinutes = @Minutes,
      calMonthlyDay = @MonthlyDay,
      calClientInformed = Null, 
      calFamilyInformed = Null
  WHERE calPackageID = @PackageID
  AND calClientID = @ClientID
  AND calWeekNo = @WeekNo
  AND (DatePart(dw, calStartDate) = @OriginalDay OR calFrequency IN (14,15,16))
  AND calStartDate BETWEEN @StartDate AND @EndDate
  AND (calInvoiceID = 0 OR calInvoiceID Is Null OR @InvoicesFinalised = 1)
  AND (calWagesheetID = 0 OR calWagesheetID Is Null OR @WagesFinalised = 1)
  AND (calLocked = 0 OR calLocked Is Null)
  AND (Table_Year = YEAR(@StartDate) 
          OR Table_Year =YEAR(@EndDate));',
  @params NVARCHAR(MAX) = N'@MonthlyDay INT,...,@StartDate DATETIME,@EndDate DATETIME';
  -- you'll need to update the params list 
  -- to reflect all of the params coming in

;WITH y AS 
(
  SELECT y FROM 
  (
    -- all valid years to choose from:
    VALUES(2014),(2015),(2016)
  ) AS y(y)
)
SELECT @SQL += REPLACE(@baseSQL, N'$y$', CONVERT(CHAR(4),y))
  FROM y 
  WHERE y >= YEAR(@StartDate) 
    AND y <= YEAR(@EndDate);

EXEC sp_executesql @SQL,
  @params,
  @MonthlyDay,...,@StartDate,@EndDate;
-- you'll also need to update the list of params here

Since you can only span a max of two years (see comments below), you can simplify somewhat by changing the CTE to:

;WITH y AS 
(
  SELECT y = YEAR(@StartDate)
  UNION SELECT YEAR(@EndDate)
)
SELECT @SQL += REPLACE(@baseSQL, N'$y$', CONVERT(CHAR(4),y))
  FROM y;

But note that this will still end up with the same number of queries to execute, and without fixing some of the other things (like the non-sargable clauses against some of the columns), this will still yield the exact same performance as my original version, you just didn't have to type out the actual years. Be very careful about this "optimization" if it is possible for StartDate and EndDate to be more than 365 days apart.

  • The CTE at the bottom, could I replace the VALUES()()() section with incoming parameters? IE YEAR(@StartDate) etc – George.Palacios Aug 4 '14 at 14:26
  • @user3789393 You can do whatever you like, as long as it works the way you expect. I wasn't clear from your requirements whether it was possible for the duration to span more than two years, in which case those two years may not be enough, because you'll probably want to update the tables in between, too. – Aaron Bertrand Aug 4 '14 at 14:28
  • Ah, one complication in that case. The update could theoretically span multiple tables (IE the @ StartDate is 31/12/2014 and @ EndDate is 14/01/2015). The Update will at max span 2 tables under these conditions, however. Would it be worth just adding a check for the YEAR(@ StartDate) <> YEAR(@ EndDate) and having it run the query once for each relevant table? – George.Palacios Aug 4 '14 at 14:32
  • I know that the update could span multiple tables, I just wasn't sure if it was <= 2 or just > 1. If it will span at most 2, then yes you could simplify, but it isn't going to change performance. And no you don't have to check if the years are different in that case, since the UNION will eliminate any duplicates. Again, other than the microseconds you gain from saving keystrokes (at the cost of making the code less flexible), this isn't going to be any faster. – Aaron Bertrand Aug 4 '14 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.