October 12, 2021 at 8:43 pm
I working on SQL server 2012 I face issue update statement take too much time
and it is very slow
my query is
update t set t.Revision_ID = r.rec_id
FROM extractreports.dbo.TempROHS t
INNER join Z2urlSystem.zsrc.Local_URL l (NOLOCK) ON isnull(t.ZURLSource1,'N/A') = l.local_url
INNER join Z2urlSystem.zsrc.Online_URL o (NOLOCK) ON ISNULL(t.OnlineSource1,'N/A') = o.url
INNER join Z2urlSystem.zsrc.Revision r (NOLOCK) ON l.rec_id = r.local_id AND o.Rec_ID = r.online_id
WHERE t.[Status] IS NULL
and execution plan as below :
https://www.brentozar.com/pastetheplan/?id=rJfvx1mSK
Table [zsrc].[Local_URL]
CREATE TABLE [zsrc].[Local_URL](
[rec_id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[local_url] [nvarchar](2048) NOT NULL,
CONSTRAINT [Key_Rec_ID] PRIMARY KEY NONCLUSTERED
(
[rec_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]
Table [zsrc].[Online_URL]
CREATE TABLE [zsrc].[Online_URL](
[Rec_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[nvarchar](500) NOT NULL,
CONSTRAINT [PK_Rec_ID_oL] PRIMARY KEY CLUSTERED
(
[Rec_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]
Table Revision
CREATE TABLE [zsrc].[Revision](
[rec_id] [bigint] NOT NULL,
[online_id] [bigint] NOT NULL,
[local_id] [bigint] NOT NULL,
CONSTRAINT [PK_Rec_ID] PRIMARY KEY CLUSTERED
(
[rec_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]
Table extractreports.dbo.TempROHS
CREATE TABLE extractreports.dbo.TempROHS
(
ID INT IDENTITY ,
ZURLSource1 NVARCHAR(3000) ,
OnlineSource1 NVARCHAR(3000) ,
Revision_ID BIGINT ,
[status] NVARCHAR(300)
)
sample data extractreports.dbo.TempROHS
ID ZURLSource1 OnlineSource1 Revision_ID STATUS
14254 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N1.pdf N/A NULL NULL
14255 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N2.pdf N/A NULL NULL
14256 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N3.pdf N/A NULL NULL
14257 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N4.pdf N/A NULL NULL
14258 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N5.pdf N/A NULL NULL
14259 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405N6.pdf N/A NULL NULL
14260 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405NA.pdf N/A NULL NULL
14261 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405NB.pdf N/A NULL NULL
14262 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405NC.pdf N/A NULL NULL
14263 https://source.z2data.com/2021/10/4/15/41/58/982175/6302W22405ND.pdf N/A NULL NULL
October 12, 2021 at 9:31 pm
looking at it, to me it appears that it is slow because you are looking at a LOT of data to review and then update a single row. Mind you that is all ESTIMATED, not actual.
It appears you have no FK constraints set up (guessing here based on what you posted), but those function calls on your JOINs are really hurting performance as you are turning that into a row based operation which you can see on the execution plan:
Estimated Number of Executions
108576
Estimated Number of Rows
1
it is running that one at a time for a total of 108576 for one of the 2 tables where you are using functions in the join. The other tables in the JOIN appear to be suspicious too as they are only estimating 1-2 rows coming back.
My GUESS is that SQL is guessing wrong and coming up with bad estimates which is resulting in tempdb spill and possibly other issues causing slowness. Could be blocking as well causing the slowness. I would try your query on a test system and let it run to completion (capturing the actual execution plan) so you can see:
A - the actual plan
B - the actual execution time on a mostly unused system
Something that MAY help (may not) is pulling the data out of the tables into temp tables or table variables rather than relying on NOLOCK which can give bad results (missing or duplicate). NOLOCK MAY be the appropriate answer to your specific use case and I am not recommending removing it, just might want to evaluate other options.
You also appear to be using cross-database queries which I've seen cause performance issues. You may benefit from having all of the data on the same database. What I mean is you could clone the tables from Z2urlSystem to extractreports that you need to do that update on and then use the tables in extractreports and remove the cross database queries.
A statistics update may help as well.
Just some things you can try; I would test some things on a test/dev environment and move to live once you have determined why it is slow and corrected the issue. I would start by getting it running on test so you can see an actual execution plan even if it means waiting 10 hours for it to complete. At least then you can see the estimated vs actual execution plans to determine if statistics is an issue which is a "quick win" in most cases.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 12, 2021 at 10:43 pm
so can you help me on code
and tell me what statement update should be to run faster
October 13, 2021 at 12:05 am
you didn't supply all the indexes of the tables - table revision has another index (IDX_Online_Id)
just as a glance I would suggest trying the following changes
Table revision - Index IDX_Online_ID - add column local_id as an include column - would remove the need for a KEY lookup
table local_url - index Key_rec_ID - convert to clustered index (its non clustered at the moment and table is a HEAP) - would remove the need for a RID lookup
October 13, 2021 at 6:02 am
i make all indexes and still issue exist
can we rewrite these statement or what
October 13, 2021 at 7:08 am
Remove all the ISNULL with some other construct.
ISNULL is not SARGable and will cause RBAR which is what your seeing.
October 13, 2021 at 5:05 pm
thank you for reply
can you write these statement below by joining using id
without join by url text
are there are any way to do that
update t set t.Revision_ID = r.rec_id
FROM extractreports.dbo.TempROHS t
INNER join Z2urlSystem.zsrc.Local_URL l (NOLOCK) ON t.ZURLSource1 = l.local_url
INNER join Z2urlSystem.zsrc.Online_URL o (NOLOCK) ON t.OnlineSource1 = o.url
INNER join Z2urlSystem.zsrc.Revision r (NOLOCK) ON l.rec_id = r.local_id AND o.Rec_ID = r.online_id
WHERE t.[Status] IS NULL
October 13, 2021 at 6:48 pm
If that works for you great.
If not look at coalesce instead of isnull or some other construct.
You know your own data better than anyone here so you will need to verify any changes in a dev/test environment.
October 31, 2021 at 1:28 pm
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply