Strange performance behaviour

Question

Strange performance behaviour

Viewing 10 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Please post the actual execution plans (not estimated) as .sqlplan files for each scenario. If you're inserting 2000 rows into @p3 and then passing that as the argument for @BusinessObjectDataGuids then that's a red flag - there are no statistics on table variables and so the query optimizer will just assume there is one row in the table. (That number may have changed in a more recent version - I'm not sure).

John

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Can you post DDL and sample data for FunctionPermission table? The problem might be related to it and the FOR XML.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

lenamagnus.frisk SSC Enthusiast Points: 150 More actions · Answer 3

Hi John and thank you for your answer,
I will post the actual execution plans. I understand your thought about the problem with statistics and table variables. What would be your recommendation to solve the problem (if this is the problem)?
/ A confused one

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

lenamagnus.frisk - Wednesday, June 13, 2018 7:50 AM
I understand your thought about the problem with statistics and table variables. What would be your recommendation to solve the problem (if this is the problem)?

Impossible to say for sure without seeing the execution plans. It may involve inserting the @BusinessObjectDataGuids list into a temp table at the beginning of your stored procedure, though.

John

Joe Torre SSChampion Points: 10248 More actions · Answer 5

Joe Torre

SSChampion

Points: 10248

June 13, 2018 at 10:12 am

#1993884

Or a staging table

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Are you aware that you'll get 2 result sets when @opMode is NULL?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

lenamagnus.frisk SSC Enthusiast Points: 150 More actions · Answer 7

John Mitchell-245523 - Wednesday, June 13, 2018 8:03 AM
lenamagnus.frisk - Wednesday, June 13, 2018 7:50 AM
I understand your thought about the problem with statistics and table variables. What would be your recommendation to solve the problem (if this is the problem)?
Impossible to say for sure without seeing the execution plans. It may involve inserting the @BusinessObjectDataGuids list into a temp table at the beginning of your stored procedure, though.
John

Hi John,

Here a the two execution plans.

/A confused one

lenamagnus.frisk SSC Enthusiast Points: 150 More actions · Answer 8

Luis Cazares - Wednesday, June 13, 2018 7:39 AM
Can you post DDL and sample data for FunctionPermission table? The problem might be related to it and the FOR XML.

Hi Luis and thank you for your answer,

Here are two files (to create table FunctionPermission and some sample data)

/A confused one

lenamagnus.frisk SSC Enthusiast Points: 150 More actions · Answer 9

lenamagnus.frisk - Thursday, June 14, 2018 1:50 AM
Luis Cazares - Wednesday, June 13, 2018 7:39 AM
Can you post DDL and sample data for FunctionPermission table? The problem might be related to it and the FOR XML.
Hi Luis and thank you for your answer,
Here are two files (to create table FunctionPermission and some sample data)
/A confused one

Sorry - forgot the files...

create table:
/****** Object: Table [dbo].[FunctionPermission] Script Date: 2018-06-14 09:51:18 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[FunctionPermission]( [FunctionPermissionGuid] [uniqueidentifier] ROWGUIDCOL NOT NULL, [SystemFunctionGuid] [uniqueidentifier] NOT NULL, [PermissionTypeGuid] [uniqueidentifier] NOT NULL, [UserGroupRoleGuid] [uniqueidentifier] NULL, [UserAccountGuid] [uniqueidentifier] NULL, [Name] [nvarchar](50) NOT NULL, [ReadPermission] [bit] NOT NULL, [CreatePermission] [bit] NOT NULL, [UpdatePermission] [bit] NOT NULL, [DeletePermission] [bit] NOT NULL, [FilterPermission] [bit] NOT NULL, [ExportPermission] [bit] NOT NULL, [PrintPermission] [bit] NOT NULL, [RightsAssignmentPermission] [bit] NOT NULL, [TranslatePermission] [bit] NOT NULL, CONSTRAINT [PK_FunctionPermission] PRIMARY KEY NONCLUSTERED ( [FunctionPermissionGuid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO

ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_FunctionPermissionGuid] DEFAULT (newid()) FOR [FunctionPermissionGuid] GO

ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_ReadPermission] DEFAULT ((1)) FOR [ReadPermission] GO

ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_CreatePermission] DEFAULT ((0)) FOR [CreatePermission] GO

ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_UpdatePermission] DEFAULT ((0)) FOR [UpdatePermission] GO

ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_DeletePermission] DEFAULT ((0)) FOR [DeletePermission] GO

ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_FilterPermission] DEFAULT ((1)) FOR [FilterPermission] GO

ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_ExportPermission] DEFAULT ((0)) FOR [ExportPermission] GO

ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_PrintPermission] DEFAULT ((1)) FOR [PrintPermission] GO

ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_RightsAssignmentPermission] DEFAULT ((0)) FOR [RightsAssignmentPermission] GO

ALTER TABLE [dbo].[FunctionPermission] ADD CONSTRAINT [DF_FunctionPermission_TranslatePermission] DEFAULT ((0)) FOR [TranslatePermission] GO

ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_PermissionType] FOREIGN KEY([PermissionTypeGuid]) REFERENCES [dbo].[PermissionType] ([PermissionTypeGuid]) ON DELETE CASCADE GO

ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_PermissionType] GO

ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_SystemFunction] FOREIGN KEY([SystemFunctionGuid]) REFERENCES [dbo].[SystemFunction] ([SystemFunctionGuid]) ON DELETE CASCADE GO

ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_SystemFunction] GO

ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_UserAccount] FOREIGN KEY([UserAccountGuid]) REFERENCES [dbo].[UserAccount] ([UserAccountGuid]) ON DELETE CASCADE GO

ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_UserAccount] GO

ALTER TABLE [dbo].[FunctionPermission] WITH CHECK ADD CONSTRAINT [FK_FunctionPermission_UserGroupRole] FOREIGN KEY([UserGroupRoleGuid]) REFERENCES [dbo].[UserGroupRole] ([UserGroupRoleGuid]) ON DELETE CASCADE GO

ALTER TABLE [dbo].[FunctionPermission] CHECK CONSTRAINT [FK_FunctionPermission_UserGroupRole] GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The internal key of the user. Automatically generated.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FunctionPermission', @level2type=N'COLUMN',@level2name=N'UserAccountGuid' GO

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

lenamagnus.frisk - Thursday, June 14, 2018 1:39 AM
John Mitchell-245523 - Wednesday, June 13, 2018 8:03 AM
lenamagnus.frisk - Wednesday, June 13, 2018 7:50 AM
I understand your thought about the problem with statistics and table variables. What would be your recommendation to solve the problem (if this is the problem)?
Impossible to say for sure without seeing the execution plans. It may involve inserting the @BusinessObjectDataGuids list into a temp table at the beginning of your stored procedure, though.
John
Hi John,
Here a the two execution plans.
/A confused one

Well, in the fast one, the number of rows in the table variable is being correctly estimated, whereas in the slow one it isn't. And you are scanning the table variable twice, so the effect is magnified. There doesn't appear to be a good reason to have a CTE (just use the table variable name) and there certainly doesn't seem to be a good reason to join the CTE to itself, causing the two scans. I don't know why the cardinality is being estimated correctly in one plan but not in the other, but you might be interested to read through this.

John