2

We have been working on an application that utilizes a view with several tables in our database. This view is utilized throughout the application, and although significantly more complicated than the example I've posted below, most queries run relatively quickly. However, we received reports from users that one query in particular takes an inordinate amount of time. The query currently in use is similar to the following sample.

The view:

CREATE VIEW [dbo].[LineItemsForReview]
AS
SELECT        reviewer.Id AS UserId, reviewer.FirstName + ' ' + reviewer.LastName AS UserFullName, ali.Id, ali.PurchaseId, ali.PurchaseTypeId, ali.CreatedDate, ali.ModifiedDate, 
                         ali.BudgetFY, ali.Fund, ali.BudgetActivity, ali.DocSource, ali.DocumentNumber, ali.DocumentDate, ali.VendorName, ali.ObligatedAmt, ali.ReceivedAmt, ali.BilledAmt, 
                         ali.LastTransactionDate, ali.IsPriority
FROM            dbo.AccountingLineItem AS ali LEFT OUTER JOIN
                         dbo.Assignment AS a ON ali.Id = a.LineItemId LEFT OUTER JOIN
                         Auth.[User] AS reviewer ON a.AssignedReviewerUserId = reviewer.Id LEFT OUTER JOIN
                         Auth.[User] AS assignor ON a.AssignorUserId = assignor.Id

The query currently in use:

SELECT [UserId]
      ,[UserFullName]
      ,[Id]
      ,[PurchaseId]
      ,[PurchaseTypeId]
      ,[CreatedDate]
      ,[ModifiedDate]
      ,[BudgetFY]
      ,[Fund]
      ,[BudgetActivity]
      ,[DocSource]
      ,[DocumentNumber]
      ,[DocumentDate]
      ,[VendorName]
      ,[ObligatedAmt]
      ,[ReceivedAmt]
      ,[BilledAmt]
      ,[LastTransactionDate]
      ,[IsPriority]
  FROM [dbo].[LineItemsForReview]
  WHERE DocSource = 'Travel' 
and UserId is null

As you can see from the above query, we are trying to find accounting line items that have not been assigned for review. When we run this query on our production database (which is more complicated than the example I'm providing), the results return in approximately 3 minutes, 23 seconds. However, when we use the following query, we can get those same results back in 4 seconds.

SELECT [UserId]
      ,[UserFullName]
      ,[Id]
      ,[PurchaseId]
      ,[PurchaseTypeId]
      ,[CreatedDate]
      ,[ModifiedDate]
      ,[BudgetFY]
      ,[Fund]
      ,[BudgetActivity]
      ,[DocSource]
      ,[DocumentNumber]
      ,[DocumentDate]
      ,[VendorName]
      ,[ObligatedAmt]
      ,[ReceivedAmt]
      ,[BilledAmt]
      ,[LastTransactionDate]
      ,[IsPriority]
  FROM [dbo].[LineItemsForReview]
  WHERE DocSource = 'Travel' 
    and UserFullName is null

As you can see when looking at the select statement and the view definition, the change to the predicate now utilizes a concatenated string instead of a single field of int data type. This potential improvement is great news for both our technical team and our end users, but I'd like to understand why this particular change decreases the query time before releasing the change to production.

I have discussed this issue with our data architect and other developers and have not come up with an answer. I've also examined the execution plans on both queries - the largest difference between the two is that the slow query (that looks at the single int field) performs a sort on the column PurchaseId, whereas the query using the concatenated field does not perform that sort.

Question: why is the query faster when searching on a concatenated string field instead of a single field of int data type?

Tables and View from Example:

/****** Object:  Table [Auth].[AdminCodeAccess]    Script Date: 2/27/2017 12:29:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Auth].[AdminCodeAccess](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [OrgRepId] [int] NULL,
    [UserRoleId] [int] NULL,
    [ActivationDate] [datetime] NULL,
    [DeactivationDate] [datetime] NULL,
    [IsPrimary] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] 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
/****** Object:  Table [Auth].[Role]    Script Date: 2/27/2017 12:29:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Auth].[Role](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RoleName] [varchar](50) NOT NULL,
    [ActivationDate] [datetime] NOT NULL,
    [DeactivationDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] 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
/****** Object:  Table [Auth].[User]    Script Date: 2/27/2017 12:29:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Auth].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [NetworkId] [varchar](50) NULL,
    [ExpirationDate] [datetime] NULL,
    [Email] [varchar](250) NULL,
    [NetworkOrg] [varchar](10) NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] 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
/****** Object:  Table [Auth].[UserRoles]    Script Date: 2/27/2017 12:29:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Auth].[UserRoles](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [RoleId] [int] NOT NULL,
    [ActivationDate] [datetime] NOT NULL,
    [DeactivationDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] 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
/****** Object:  Table [dbo].[AccountingLineItem]    Script Date: 2/27/2017 12:29:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AccountingLineItem](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [PurchaseId] [int] NOT NULL,
    [PurchaseTypeId] [int] NULL,
    [CreatedDate] [datetime] NULL,
    [ModifiedDate] [datetime] NULL,
    [BudgetFY] [int] NULL,
    [AccountNumber] [varchar](10) NULL,
    [Fund] [varchar](30) NULL,
    [BudgetActivity] [varchar](15) NULL,
    [DocSource] [varchar](50) NULL,
    [DocumentNumber] [varchar](50) NULL,
    [DocumentDate] [datetime] NULL,
    [VendorName] [varchar](100) NULL,
    [OrgId] [int] NULL,
    [ObligatedAmt] [decimal](19, 4) NULL,
    [ReceivedAmt] [decimal](19, 4) NULL,
    [BilledAmt] [decimal](19, 4) NULL,
    [LastTransactionDate] [datetime] NULL,
    [IsPriority] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] 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
/****** Object:  Table [dbo].[Assignment]    Script Date: 2/27/2017 12:29:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Assignment](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LineItemId] [int] NOT NULL,
    [AssignedReviewerUserId] [int] NULL,
    [AssignorUserId] [int] NULL,
    [AssignmentDate] [datetime] NULL,
    [DeactivationDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] 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
/****** Object:  View [dbo].[LineItemsForReview]    Script Date: 2/27/2017 12:29:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[LineItemsForReview]
AS
SELECT        reviewer.Id AS UserId, reviewer.FirstName + ' ' + reviewer.LastName AS UserFullName, ali.Id, ali.PurchaseId, ali.PurchaseTypeId, ali.CreatedDate, ali.ModifiedDate, 
                         ali.BudgetFY, ali.Fund, ali.BudgetActivity, ali.DocSource, ali.DocumentNumber, ali.DocumentDate, ali.VendorName, ali.ObligatedAmt, ali.ReceivedAmt, ali.BilledAmt, 
                         ali.LastTransactionDate, ali.IsPriority
FROM            dbo.AccountingLineItem AS ali LEFT OUTER JOIN
                         dbo.Assignment AS a ON ali.Id = a.LineItemId LEFT OUTER JOIN
                         Auth.[User] AS reviewer ON a.AssignedReviewerUserId = reviewer.Id LEFT OUTER JOIN
                         Auth.[User] AS assignor ON a.AssignorUserId = assignor.Id

GO
ALTER TABLE [dbo].[AccountingLineItem] ADD  DEFAULT ((0)) FOR [IsPriority]
GO
ALTER TABLE [Auth].[AdminCodeAccess]  WITH CHECK ADD  CONSTRAINT [FK_AdminCodeAccess_User] FOREIGN KEY([UserRoleId])
REFERENCES [Auth].[UserRoles] ([Id])
GO
ALTER TABLE [Auth].[AdminCodeAccess] CHECK CONSTRAINT [FK_AdminCodeAccess_User]
GO
ALTER TABLE [Auth].[UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_UserRoles_Role] FOREIGN KEY([RoleId])
REFERENCES [Auth].[Role] ([Id])
GO
ALTER TABLE [Auth].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_Role]
GO
ALTER TABLE [Auth].[UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_UserRoles_User] FOREIGN KEY([UserId])
REFERENCES [Auth].[User] ([Id])
GO
ALTER TABLE [Auth].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_User]
GO
ALTER TABLE [dbo].[Assignment]  WITH CHECK ADD  CONSTRAINT [FK_Assignment_AccountingLineItem] FOREIGN KEY([LineItemId])
REFERENCES [dbo].[AccountingLineItem] ([Id])
GO
ALTER TABLE [dbo].[Assignment] CHECK CONSTRAINT [FK_Assignment_AccountingLineItem]
GO
ALTER TABLE [dbo].[Assignment]  WITH CHECK ADD  CONSTRAINT [FK_Assignment_User] FOREIGN KEY([AssignedReviewerUserId])
REFERENCES [Auth].[User] ([Id])
GO
ALTER TABLE [dbo].[Assignment] CHECK CONSTRAINT [FK_Assignment_User]
GO
ALTER TABLE [dbo].[Assignment]  WITH CHECK ADD  CONSTRAINT [FK_Assignment_User_Id] FOREIGN KEY([AssignorUserId])
REFERENCES [Auth].[User] ([Id])
GO
ALTER TABLE [dbo].[Assignment] CHECK CONSTRAINT [FK_Assignment_User_Id]
GO

Edit: By request, I've added two query plans to pastetheplan.com.

Slow Query: https://www.brentozar.com/pastetheplan/?id=SJegN-z5e

Fast Query: https://www.brentozar.com/pastetheplan/?id=rkH5sbGqe

In addition, there are indices on columns commonly used in queries and by the engine, and we continue to add indices as needed; however, there are no indices on the tables included in the sample query, besides those created with the PKs. You should be able to see other indices in the execution plan.

  • 1
    Note: the two queries are only logically equivalent if all Auth.User rows actually have both a FirstName and a LastName. Otherwise, the first query brings back all rows with no matching reviewer, and the second with either no matching reviewer, OR with a matching reviewer with a NULL first or last name. Not sure that this impacts the question directly; however, if the ultimate results are different, clearly the performance can be different. – RDFozz Feb 27 '17 at 19:48
  • @RDFozz - Our ETL process ensures that those columns are filled, but we should make change the column definition to not null prior to making this change. Currently, our results are the same from either query, but thanks for the reminder. – floppsb Feb 27 '17 at 20:35
  • Can you add the output of SET STATISTICS IO, TIME ON to the question for both queries? I'm not seeing a big difference between the queries and that could provide clues. – Joe Obbish Mar 1 '17 at 1:10

Your Answer

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

Browse other questions tagged or ask your own question.