July 13, 2014 at 7:22 am
Hey,
I have a question regarding the locking behavior of indexed views. We have a 3rd party application and something like the following table:
CREATE TABLE [Person].[Person](
[BusinessEntityID] int NOT NULL,
[FirstName] varchar(20) NOT NULL,
[MiddleName] varchar(20) NULL,
[LastName] varchar(20) NOT NULL,
[Quantity] int NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED
(
[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Also the 3rd party application uses an indexed view, which is based on the following query and has the same structure as the base table:
SELECT [BusinessEntityID],
[FirstName],
[MiddleName],
[LastName],
SUM([Quantity]) AS [SUMQuantity]
FROM [Person].[Person]
GROUP BY [BusinessEntityID], [FirstName], [MiddleName], [LastName]
The result is, that the indexed view has nearly the same data / entries as the base table. The indexed view is often queried like this:
SELECT SUM(SUMQuantity) FROM [vPerson] WITH(UPDLOCK, NOEXPAND) WHERE [BusinessEntityID] = 45 AND [FirstName] = 'test'
The base table is also queried very often with update locks (UPDLOCK). Because the indexed view is nearly just a copy of the base table and there is no performance gain (read), I would like to drop the indexed view.
My fear is that I'll then experience more locks / blocks, because now the queries are seperated on two objects. What do you think about that?
Best regards
July 14, 2014 at 2:28 am
Quick thought, a solution might be replacing the group by with the over clause
😎
SELECT [BusinessEntityID],
[FirstName],
[MiddleName],
[LastName],
SUM([Quantity]) OVER (PARTITION BY [BusinessEntityID], [FirstName], [MiddleName], [LastName]) AS [SUMQuantity]
FROM [Person].[Person]
July 14, 2014 at 5:53 am
Hi,
thanks for your response, but the problem is that I can't change the way our 3rd party application is building the statements or change the statement itself. My question was more about the indexed view and the locking behaviour. Could you help me at this part?
Best regards!
July 15, 2014 at 10:02 am
Hey,
do you have any ideas on that or do you need further information?
Best regards!
July 16, 2014 at 10:48 am
vip.blade (7/15/2014)
Hey,do you have any ideas on that or do you need further information?
Best regards!
I think we would need more information about what the application is trying to do, especially why there would be a need for UPDLOCK hints. A proper isolation level setting and good transaction management should solve most concurrency issues. Using lock hints in ad hoc queries can create a confusing tangle that can be hard to sort out when the database doesn't behave as expected.
All that being said, your indexed view contains precalculated aggregate values. Querying an indexed view for such data usually outperforms calculating aggregates on the fly. You will almost certainly see increased contention on the underlying table if you drop the view and start calculating aggregate values on the fly - a lot more reads will be required and the locks will be applied to many more rows at a time.
Jason Wolfkill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply