April 7, 2016 at 11:33 am
Hello All,
This SP is running in our prod environment very frequently throughout the day and it's taking on an average 15-20 seconds to execute.
I am looking for any advice to optimize this SP better way.
I have attached scripts, execution plan and query details. Thanks all for you time and please let me know if more details are required
April 7, 2016 at 12:05 pm
I'm not understanding the following. Is [GDML.eResults.UserStore] a database with an odd 3 part name containing periods, or is this a reference to a remotely linked database/table?
[GDML.eResults.UserStore].[dbo].[vw_aspnet_UserProfiles]
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 7, 2016 at 12:20 pm
So it's the first query in your proc taking all the time, the part that does the insert into #TempUsers. Quite frankly that plan does not look too terrible and it's hard for me to say what the problem is. Here's some food for thought:
I noticed that the lob logical reads is 135072 and I can't figure out where what's causing that; high lob reads happens when an image, file or something like that is stored in a column. That said, 135K is not astronomical.
It appears that you have a nonclustered index that's being used but there's no DDL for in your script: PK__aspnet_U__1788CC4D1FCDBCEB. That index is being used but is missing the aspnet_users > username column which appears to be causing a Key lookup. Key lookups will slow you down but I don't know for sure if that's the issue either.
Lastly, I noticed that you have indexes named "dta_..." that's an index created by the data tuning advisor. Don't use that - it's no good.
-- Itzik Ben-Gan 2001
April 7, 2016 at 12:54 pm
The aspnet_profile table stores a couple ntext columns (*shudder*), so that's where the LOB reads come from.
Since the execution time is entirely CPU, and the number of reads are pretty low, I suspect the biggest time sink is probably going to be the compute scalar between the last nested loop join and the top operator.
That's doing some pretty heavy parsing of those ntext columns, and the plan's only estimating that it has to do that for 3 rows, but it's actually doing it for 381.
I'll have to do some quick tests to confirm that suspicion, but I'd take a look at the text parsing to see if it can be simplified at all.
Cheers!
April 7, 2016 at 1:16 pm
Eric M Russell (4/7/2016)
I'm not understanding the following. Is [GDML.eResults.UserStore] a database with an odd 3 part name containing periods, or is this a reference to a remotely linked database/table?[GDML.eResults.UserStore].[dbo].[vw_aspnet_UserProfiles]
That's the name of database.
April 7, 2016 at 3:12 pm
Confirmed that the parsing there is quite inefficient; if the strings involved are long enough, then doing that parsing can certainly take many seconds.
Here's a quick demo showing how that parsing fares compared to a less complicated version. Just for fun I included one that converts to XML and pulls the values between tags that way; I'm not sure that one will work reliably or at all for your data, but I included it because it was the fastest of the methods.
SET NOCOUNT ON;
CREATE TABLE #test (PropertyValuesString nvarchar(max));
GO
--Generate a string with the appropriate tags, prefixed with
--a string of a random number of up to 1000 'j' characters.
INSERT INTO #test
SELECT REPLICATE('j',RAND(CHECKSUM(NEWID()))*1000)+'<ProfileApplication>'+CONVERT(nvarchar(max), NEWID())+'<RequisitionStartDate>'+CONVERT(nvarchar(max), GETDATE())+'</RequisitionStartDate>'+CONVERT(nvarchar(max), NEWID())+'</ProfileApplication>';
GO 381
--Variable for redirecting output, minimizing SSMS overhead
DECLARE @nvarchar_bucket nvarchar(max);
SET STATISTICS TIME ON;
'##########
OLD METHOD
##########';
--The current method of parsing
SELECT @nvarchar_bucket=profileapplication,@nvarchar_bucket=SUBSTRING
(SUBSTRING
(P.ProfileApplication,
CHARINDEX('RequisitionStartDate', P.ProfileApplication),
CHARINDEX('</RequisitionStartDate',P.ProfileApplication) -
CHARINDEX('RequisitionStartDate',P.ProfileApplication)
),
CHARINDEX('>', SUBSTRING(P.ProfileApplication,
CHARINDEX('RequisitionStartDate', P.ProfileApplication),
CHARINDEX('</RequisitionStartDate',P.ProfileApplication) -
CHARINDEX('RequisitionStartDate', P.ProfileApplication))) + 1,
LEN(SUBSTRING(P.ProfileApplication,
CHARINDEX('RequisitionStartDate',P.ProfileApplication),
CHARINDEX('</RequisitionStartDate',P.ProfileApplication) -
CHARINDEX('RequisitionStartDate',P.ProfileApplication))))
FROM
(
SELECT SUBSTRING(SUBSTRING(P.PropertyValuesString, CHARINDEX('ProfileApplication', P.PropertyValuesString), CHARINDEX('</ProfileApplication',
P.PropertyValuesString) - CHARINDEX('ProfileApplication', P.PropertyValuesString)), CHARINDEX('>', SUBSTRING(P.PropertyValuesString,
CHARINDEX('ProfileApplication', P.PropertyValuesString), CHARINDEX('</ProfileApplication', P.PropertyValuesString) - CHARINDEX('ProfileApplication',
P.PropertyValuesString))) + 1, LEN(SUBSTRING(P.PropertyValuesString, CHARINDEX('ProfileApplication', P.PropertyValuesString),
CHARINDEX('</ProfileApplication', P.PropertyValuesString) - CHARINDEX('ProfileApplication', P.PropertyValuesString)))) AS ProfileApplication
FROM #test P
) P;
'##########
NEW METHOD
##########';
--A more efficient method
SELECT @nvarchar_bucket=ProfileApplication, @nvarchar_bucket=SUBSTRING(ProfileApplication,start_pos,end_pos-start_pos)
FROM
(
SELECT SUBSTRING(PropertyValuesString,start_pos,end_pos-start_pos)
FROM #test
CROSS APPLY
(SELECT CHARINDEX('ProfileApplication>',PropertyValuesString)+LEN('ProfileApplication>'))start_pos(start_pos)
CROSS APPLY
(SELECT CHARINDEX('</ProfileApplication',PropertyValuesString))end_pos(end_pos)
) P (ProfileApplication)
CROSS APPLY
(SELECT CHARINDEX('RequisitionStartDate>',ProfileApplication)+LEN('RequisitionStartDate>')) start_pos(start_pos)
CROSS APPLY
(SELECT CHARINDEX('</RequisitionStartDate',ProfileApplication)) end_pos(end_pos);
'##########
XML METHOD
##########';
SELECT @nvarchar_bucket=y.value('/ProfileApplication[1]','nvarchar(max)'),
@nvarchar_bucket=y.value('/ProfileApplication[1]/RequisitionStartDate[1]','nvarchar(max)')
FROM #test t
CROSS APPLY
(SELECT CAST(PropertyValuesString as XML))x(y)
SET STATISTICS TIME OFF;
DROP TABLE #test;
If the aspnet_profile table is written to infrequently and the SELECTs doing the parsing are run often, then it might be worth considering paying the parsing penalty on write instead of read by persisting the parsed data (through a computed column, indexed view, or trigger that writes to a table).
In the meantime, you could get a more direct test of how much time is taken by the parsing by running the slow SELECT, and then running the same SELECT, but with the parsed column replaced by another column from aspnet_profile (maybe LastUpdatedDate?).
Something like this (not tested, since I don't have these tables and views):
select
U.UserId,
U.UserName,
SUBSTRING
(SUBSTRING
(P.ProfileApplication,
CHARINDEX('RequisitionStartDate', P.ProfileApplication),
CHARINDEX('</RequisitionStartDate',P.ProfileApplication) -
CHARINDEX('RequisitionStartDate',P.ProfileApplication)
),
CHARINDEX('>', SUBSTRING(P.ProfileApplication,
CHARINDEX('RequisitionStartDate', P.ProfileApplication),
CHARINDEX('</RequisitionStartDate',P.ProfileApplication) -
CHARINDEX('RequisitionStartDate', P.ProfileApplication))) + 1,
LEN(SUBSTRING(P.ProfileApplication,
CHARINDEX('RequisitionStartDate',P.ProfileApplication),
CHARINDEX('</RequisitionStartDate',P.ProfileApplication) -
CHARINDEX('RequisitionStartDate',P.ProfileApplication))))
AS RequisitionStartDate
from Accounts.RequisitionAccountAccess RAA with (nolock)
inner join [GDML.eResults.DataStore].[Admin].[AllowedAccountUserAccess] AS AAUA with(nolock)
on ((RAA.AccountID = AAUA.AccountID AND RAA.AddressID = AAUA.AddressID )
OR(RAA.AccountID = AAUA.AccountID AND AAUA.AddressID = 0))
inner join [GDML.eResults.UserStore].[dbo].[aspnet_Users] AS U with(nolock)
ON U.UserId=AAUA.UserId
INNER JOIN [GDML.eResults.UserStore].[dbo].[vw_aspnet_UserProfiles] AS P with(nolock)
ON U.UserId = P.UserId
where
RAA.RequisitionID=@RequisitionID;
--Compare to this
select
U.UserId,
U.UserName,
P.LastUpdatedDate
from Accounts.RequisitionAccountAccess RAA with (nolock)
inner join [GDML.eResults.DataStore].[Admin].[AllowedAccountUserAccess] AS AAUA with(nolock)
on ((RAA.AccountID = AAUA.AccountID AND RAA.AddressID = AAUA.AddressID )
OR(RAA.AccountID = AAUA.AccountID AND AAUA.AddressID = 0))
inner join [GDML.eResults.UserStore].[dbo].[aspnet_Users] AS U with(nolock)
ON U.UserId=AAUA.UserId
INNER JOIN [GDML.eResults.UserStore].[dbo].[vw_aspnet_UserProfiles] AS P with(nolock)
ON U.UserId = P.UserId
where
RAA.RequisitionID=@RequisitionID
If the time quite drastically reduces, then that will be additional confirmation that the parsing is primarily responsible for the duration and CPU utilization.
Cheers!
April 7, 2016 at 3:57 pm
Here is the view definition reformatted. If I didn't know better I'd swear this is XML. Would XML shredding be better?
CREATE VIEW [dbo].[vw_aspnet_UserProfiles]
AS
SELECT
P.UserId,
P.LastUpdatedDate,
U.UserName,
SUBSTRING(SUBSTRING(P.PropertyValuesString,
CHARINDEX('ProfileName', P.PropertyValuesString),
CHARINDEX('</ProfileName', P.PropertyValuesString) - CHARINDEX('ProfileName', P.PropertyValuesString)),
CHARINDEX('>', SUBSTRING(P.PropertyValuesString,
CHARINDEX('ProfileName', P.PropertyValuesString),
CHARINDEX('</ProfileName', P.PropertyValuesString) - CHARINDEX('ProfileName', P.PropertyValuesString))) + 1,
LEN(SUBSTRING(P.PropertyValuesString,
CHARINDEX('ProfileName', P.PropertyValuesString),
CHARINDEX('</ProfileName', P.PropertyValuesString) - CHARINDEX('ProfileName', P.PropertyValuesString)))) AS Name,
SUBSTRING(SUBSTRING(P.PropertyValuesString,
CHARINDEX('ProfileApplication', P.PropertyValuesString),
CHARINDEX('</ProfileApplication', P.PropertyValuesString) - CHARINDEX('ProfileApplication', P.PropertyValuesString)),
CHARINDEX('>', SUBSTRING(P.PropertyValuesString,
CHARINDEX('ProfileApplication', P.PropertyValuesString),
CHARINDEX('</ProfileApplication', P.PropertyValuesString) - CHARINDEX('ProfileApplication', P.PropertyValuesString))) + 1,
LEN(SUBSTRING(P.PropertyValuesString,
CHARINDEX('ProfileApplication', P.PropertyValuesString),
CHARINDEX('</ProfileApplication', P.PropertyValuesString) - CHARINDEX('ProfileApplication', P.PropertyValuesString)))) AS ProfileApplication,
SUBSTRING(SUBSTRING(P.PropertyValuesString,
CHARINDEX('ParentUserId', P.PropertyValuesString),
CHARINDEX('</ParentUserId', P.PropertyValuesString) - CHARINDEX('ParentUserId', P.PropertyValuesString)),
CHARINDEX('>', SUBSTRING(P.PropertyValuesString,
CHARINDEX('ParentUserId', P.PropertyValuesString),
CHARINDEX('</ParentUserId', P.PropertyValuesString) - CHARINDEX('ParentUserId', P.PropertyValuesString))) + 1,
LEN(SUBSTRING(P.PropertyValuesString,
CHARINDEX('ParentUserId', P.PropertyValuesString),
CHARINDEX('</ParentUserId', P.PropertyValuesString) - CHARINDEX('ParentUserId', P.PropertyValuesString)))) AS ParentUserId,
SUBSTRING(SUBSTRING(P.PropertyValuesString,
CHARINDEX('First', P.PropertyValuesString),
CHARINDEX('</First', P.PropertyValuesString) - CHARINDEX('First', P.PropertyValuesString)),
CHARINDEX('>', SUBSTRING(P.PropertyValuesString,
CHARINDEX('First', P.PropertyValuesString),
CHARINDEX('</First', P.PropertyValuesString) - CHARINDEX('First', P.PropertyValuesString))) + 1,
LEN(SUBSTRING(P.PropertyValuesString,
CHARINDEX('First', P.PropertyValuesString),
CHARINDEX('</First', P.PropertyValuesString) - CHARINDEX('First', P.PropertyValuesString)))) AS FirstName,
SUBSTRING(SUBSTRING(P.PropertyValuesString,
CHARINDEX('Last', P.PropertyValuesString),
CHARINDEX('</Last', P.PropertyValuesString) - CHARINDEX('Last', P.PropertyValuesString)),
CHARINDEX('>', SUBSTRING(P.PropertyValuesString,
CHARINDEX('Last', P.PropertyValuesString),
CHARINDEX('</Last', P.PropertyValuesString) - CHARINDEX('Last', P.PropertyValuesString))) + 1,
LEN(SUBSTRING(P.PropertyValuesString,
CHARINDEX('Last', P.PropertyValuesString),
CHARINDEX('</Last', P.PropertyValuesString) - CHARINDEX('Last', P.PropertyValuesString)))) AS LastName,
SUBSTRING(SUBSTRING(P.PropertyValuesString,
CHARINDEX('DefaultReport', P.PropertyValuesString),
CHARINDEX('</DefaultReport', P.PropertyValuesString) - CHARINDEX('DefaultReport', P.PropertyValuesString)),
CHARINDEX('>', SUBSTRING(P.PropertyValuesString,
CHARINDEX('DefaultReport', P.PropertyValuesString),
CHARINDEX('</DefaultReport', P.PropertyValuesString) - CHARINDEX('DefaultReport', P.PropertyValuesString))) + 1,
LEN(SUBSTRING(P.PropertyValuesString,
CHARINDEX('DefaultReport', P.PropertyValuesString),
CHARINDEX('</DefaultReport', P.PropertyValuesString) - CHARINDEX('DefaultReport', P.PropertyValuesString)))) AS ReportName,
SUBSTRING(SUBSTRING(P.PropertyValuesString,
CHARINDEX('RequisitionStartDate', P.PropertyValuesString),
CHARINDEX('</RequisitionStartDate', P.PropertyValuesString) - CHARINDEX('RequisitionStartDate', P.PropertyValuesString)),
CHARINDEX('>', SUBSTRING(P.PropertyValuesString,
CHARINDEX('RequisitionStartDate', P.PropertyValuesString),
CHARINDEX('</RequisitionStartDate', P.PropertyValuesString) - CHARINDEX('RequisitionStartDate', P.PropertyValuesString))) + 1,
LEN(SUBSTRING(P.PropertyValuesString,
CHARINDEX('RequisitionStartDate', P.PropertyValuesString),
CHARINDEX('</RequisitionStartDate', P.PropertyValuesString) - CHARINDEX('RequisitionStartDate', P.PropertyValuesString)))) AS RequisitionStartDate,
SUBSTRING(SUBSTRING(P.PropertyValuesString,
CHARINDEX('ViewSensitiveOrders', P.PropertyValuesString),
CHARINDEX('</ViewSensitiveOrders', P.PropertyValuesString) - CHARINDEX('ViewSensitiveOrders', P.PropertyValuesString)),
CHARINDEX('>', SUBSTRING(P.PropertyValuesString,
CHARINDEX('ViewSensitiveOrders', P.PropertyValuesString),
CHARINDEX('</ViewSensitiveOrders', P.PropertyValuesString) - CHARINDEX('ViewSensitiveOrders', P.PropertyValuesString))) + 1,
LEN(SUBSTRING(P.PropertyValuesString,
CHARINDEX('ViewSensitiveOrders', P.PropertyValuesString),
CHARINDEX('</ViewSensitiveOrders', P.PropertyValuesString) - CHARINDEX('ViewSensitiveOrders', P.PropertyValuesString)))) AS ViewSensitiveOrders
FROM
dbo.aspnet_Profile AS P
INNER JOIN dbo.aspnet_Users AS U
ON U.UserId = P.UserId;
April 7, 2016 at 10:09 pm
First, awesome job of posting what we need to help. Thanks for taking the time.
What do the following queries return, please... (not sure I did the 2nd query right because can't test without data)
SELECT AccountID
,AddressID
,TheCount = COUNT(*)
FROM Accounts.RequisitionAccountAccess AS RAA
WHERE RAA.RequisitionID = '0100024363'
GROUP BY AccountID, AddressID
;
-------------------------------------------------------------------------------
SELECT AAUA.UserId
,UserCount = COUNT(AAUA.UserId)
FROM Accounts.RequisitionAccountAccess AS RAA
JOIN [GDML.eResults.DataStore].[Admin].[AllowedAccountUserAccess] AS AAUA
ON (
(RAA.AccountID = AAUA.AccountID AND AAUA.AddressID = RAA.AddressID)
OR (RAA.AccountID = AAUA.AccountID AND AAUA.AddressID = 0)
)
WHERE RAA.RequisitionID = '0100024363'
GROUP BY AAUA.UserId
;
-------------------------------------------------------------------------------
SELECT COUNT(*)
FROM dbo.aspnet_Profile
GROUP BY UserId
HAVING COUNT(*) > 1
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2016 at 1:01 am
1st thing falling into my eye:
inner join [GDML.eResults.DataStore].[Admin].[AllowedAccountUserAccess] AS AAUA with(nolock)
on ((RAA.AccountID = AAUA.AccountID AND RAA.AddressID = AAUA.AddressID )
OR(RAA.AccountID = AAUA.AccountID AND AAUA.AddressID = 0))
needs to be
inner join [GDML.eResults.DataStore].[Admin].[AllowedAccountUserAccess] AS AAUA with(nolock)
on RAA.AccountID = AAUA.AccountID
AND (RAA.AddressID = AAUA.AddressID OR AAUA.AddressID = 0)
_____________
Code for TallyGenerator
April 8, 2016 at 1:27 am
CREATE TABLE [Admin].[AllowedAccountUserAccess](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[UserID] [uniqueidentifier] NOT NULL,
[AccountID] [bigint] NOT NULL,
[AddressID] [bigint] NOT NULL,
CONSTRAINT [PK_AllowedAccountUserAccess] 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
BRRRRR...
Should be
CONSTRAINT [PK_AllowedAccountUserAccess] PRIMARY KEY NONCLUSTERED ([ID] ASC)
CONSTRAINT UC_AllowedAccountUserAccess UNIQUE CLUSTERED (AccountID, AddressID, [UserID])
Question mark about uniqueness.
_____________
Code for TallyGenerator
April 8, 2016 at 1:31 am
from Accounts.RequisitionAccountAccess RAA with (nolock)
inner join [GDML.eResults.DataStore].[Admin].[AllowedAccountUserAccess] AS AAUA with(nolock)
on ((RAA.AccountID = AAUA.AccountID AND RAA.AddressID = AAUA.AddressID )
OR(RAA.AccountID = AAUA.AccountID AND AAUA.AddressID = 0))
inner join [GDML.eResults.UserStore].[dbo].[aspnet_Users] AS U with(nolock)
ON U.UserId=AAUA.UserId
INNER JOIN [GDML.eResults.UserStore].[dbo].[vw_aspnet_UserProfiles] AS P with(nolock)
ON U.UserId = P.UserId
useless join:
inner join [GDML.eResults.UserStore].[dbo].[aspnet_Users]
vw_aspnet_UserProfiles has it inside and returns everything you need in the query.
Join the view directly to AAUA
_____________
Code for TallyGenerator
April 8, 2016 at 8:25 am
Thanks all for your inputs. I will try all recommendation on our test environment and share my outcomes with you guys.
April 8, 2016 at 8:34 am
EasyBoy (4/8/2016)
Thanks all for your inputs. I will try all recommendation on our test environment and share my outcomes with you guys.
I could really use the output from the queries to help me understand your data. The initial part of the query is returning 426 rows and then every join after that is using 381 row... some of which manifest as 381 seeks for each table and I don't believe that's necessary. That's why I need the outputs.
The view contains a lookup on aspnet_users which is why Sergiy posted it as a useless join and he's right for the query posted. It also contains the aspnet_profile table, which is the source of most of the slowness because of that view.
The problem is, I wouldn't use that view at all and you've also duplicated what the view does for RequisitionStartDate but on the view itself. That's going to be a problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply