UPDATE when the values are the same
-
Sergiy (9/7/2012)
SQL Kiwi (9/7/2012)
If you don't stop being snarky in your replies
You may notice I just quoted you. Word to word.
And that is exactly the problem. My question to you was genuine. You were just being snarky.
Can you imagine my question was also genuine?
I just did not want to appear aggressive with my question (because of the cultural difference mentioned above), so I used the wording which is approved to be OK.
What on earth have I said to deserve this?
Something like this:
"I am reluctantly going to stop assisting you."
Aren't we both looking for a truth here?
Don't you think you might be wrong on this topic?
Just like you've been wrong on the topic about index fragmentation?
Having watched this thread from day 1...yes, you are being snarky and, at times, aggressive.
You have had numerous genuine responses from people who are trying to help you to understand the behaviour of a 'non-updating update'. You have been given code (and you have quoted back this same code) that shows quite clearly that there is NO ROW MODIFICATION where the object of the update is unchanged.
You have had many more responses, explaining that the locking mechanism operates in exactly the same way in response to the code being executed, regardless of whether the data ends up being changed or not. If a train is scheduled to stop at a station, then that's what it does. This doesn't mean people will get on or off the train
You are battling on in vain, seemingly to try and prove a point that was already disproved, with evidence. Why are you persisting in this; and alienating people who only want to teach or learn, in the process?
If you want to take down Paul for whatever reason,
Don't you think you might be wrong on this topic?
Just like you've been wrong on the topic about index fragmentation?
check back to add a whole list of others who are in agreement with him on this topic. So everyone is wrong - and you are right?
-
Sergiy (9/7/2012)
SQL Kiwi (9/7/2012)
If you don't stop being snarky in your replies
You may notice I just quoted you. Word to word.
And that is exactly the problem. My question to you was genuine. You were just being snarky.
Can you imagine my question was also genuine? I just did not want to appear aggressive with my question (because of the cultural difference mentioned above), so I used the wording which is approved to be OK.
I want to believe you, Sergiy, really I do.
Aren't we both looking for a truth here?
I'm just doing my best to answer each new question you ask.
Don't you think you might be wrong on this topic?
Wrong about what? I have said, over and over again, that I don't know whether the in-memory page is written to or not. A page that is exactly the same afterward, is not marked as dirty, and doesn't get written to disk...why would anyone care?
Just like you've been wrong on the topic about index fragmentation?
I have been wrong many times, and I usually learn something from that. I have no idea what you are talking about regarding index fragmentation though.
If it makes your life happier, I will happily agree that the in-memory page might be written to - but only in the case of a clustered index or a heap. Non-clustered indexes, no.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
MissTippsInOz (9/7/2012)
You have had numerous genuine responses from people who are trying to help you to understand the behaviour of a 'non-updating update'. You have been given code (and you have quoted back this same code) that shows quite clearly that there is NO ROW MODIFICATION where the object of the update is unchanged.
I must have missed that.
Honestly.
Because if I'd see anything what would show CLEARLY "that there is NO ROW MODIFICATION where the object of the update is unchanged" I'd happily accept it.
So far the best Paul could come up with was "we cannot know for sure".
Don't you think you might be wrong on this topic?
Just like you've been wrong on the topic about index fragmentation?
check back to add a whole list of others who are in agreement with him on this topic. So everyone is wrong - and you are right?
Since when the numbers are the proof?
The law of scientific consensus:
At times of high scientific controversy, the consensus is always wrong.
Galileo was the only one which tried to oppose the common knowledge about geocentric model of the Universe.
So, who was wrong and who was right?
Appealing to the majority comes when you can't argue the case but don't want to admit you've been wrong on the matter.
_____________
Code for TallyGenerator -
Sergiy (9/7/2012)
Key word - changes. No matter if it's rolled back - there was a change. IO operation.Even if everything remained like it was before - there was a change made.
Yes; even if no change occurred from a non-updating update, it is counted. How does that help you know whether the in-memory page was written to or not?
If you remove a screw from a wall and then immediately put it back - it's all the same after all.
The operation is rolled back, the operation did not change anything.
But operation was still performed.
Actually 2 operations - do and un-do.
Energy and resources spent.
That's the fact.
You cannot get the proof of it from the screw in the wall (unless you were not careful and damaged something around).
But you can get a proof from you body temperature or electricity meter (if you used an electric screwdriver).
Same with data. To get the proof you need analyse side indicators, like a temperature of the memory chip.
And it will surely indicate that X lock means actual writing, as they say in BOL.
Again, we are all agreed that exclusive locks are taken before any potential modification. How does that help you know whether the in-memory page was written to or not?
Isn't it what the OP question was about?
No it was not. We have been over this before. I am not going to repeat myself.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Sergiy,
If the only point you are trying to make is that non-updating updates do write to a clustered index or heap page (without changing it in any way) I will happily run a test later with a debugger attached to SQL Server to see if there are any calls to write to the page. This is quite a bit of work, however, so I hope you will accept the result whichever way it goes.
I still don't have the faintest idea why you care about this, but if it makes you happy, I am willing to do this for you. I do think you should have tried this for yourself 100 posts ago (as it is only really you that cares).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
SQL Kiwi (9/7/2012)
I have said, over and over again, that I don't know whether the in-memory page is written to or not. A page that is exactly the same afterward, is not marked as dirty, and doesn't get written to disk...why would anyone care?Ask Eugine.
I guess he has already answerred.
Somewhere in the depth of this topic. Page 5 or 6 or so.
When you do such updates on tables with millions of rows on a regular basis you see the difference.
Huge difference.
_____________
Code for TallyGenerator -
Sergiy (9/7/2012)
SQL Kiwi (9/7/2012)
I have said, over and over again, that I don't know whether the in-memory page is written to or not. A page that is exactly the same afterward, is not marked as dirty, and doesn't get written to disk...why would anyone care?Ask Eugine.
I guess he has already answerred.
Somewhere in the depth of this topic. Page 5 or 6 or so.
When you do such updates on tables with millions of rows on a regular basis you see the difference.
Huge difference.
No-one (aside from Celko, and he doesn't count) is disputing that queries should be written to only change rows that need changing. No-one is saying there isn't significant overhead in performing changes that don't change anything. There is. If that is your only concern, we can all agree on that (we never disagreed) and move on.
However, the debate has been about whether SQL Server actually writes to the page or not. Not whether it is inefficient to pass rows along the query plan that don't need updating. Not whether it is inefficient to take so many exclusive locks, latches, or any number of overheads involved in processing rows that do not need processing.
Do you seriously think so many experienced people on here don't know that performing redundant updates is wasteful?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Sergiy (9/7/2012)
MissTippsInOz (9/7/2012)
You have had numerous genuine responses from people who are trying to help you to understand the behaviour of a 'non-updating update'. You have been given code (and you have quoted back this same code) that shows quite clearly that there is NO ROW MODIFICATION where the object of the update is unchanged.
I must have missed that.
Honestly.
Because if I'd see anything what would show CLEARLY "that there is NO ROW MODIFICATION where the object of the update is unchanged" I'd happily accept it.
The page is not marked as dirty, not flushed to disk, and no changes are logged. Are you seriously suggesting that the reason a large non-updating update takes so long is because it follows your idea of writing to the page and comparing it with a previous version?
Galileo was the only one which tried to oppose the common knowledge about geocentric model of the Universe.
So, who was wrong and who was right?
I cannot believe you are comparing yourself with Galileo now.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
...
Galileo was the only one which tried to oppose the common knowledge about geocentric model of the Universe.
So, who was wrong and who was right?
I cannot believe you are comparing yourself with Galileo now.
What I could advise here to Sergiy:
Add Galilei to your user name and be prepared to be condemned for "vehement suspicion of heresy".
:hehe:
And having reviewed some my posts about checking the values for update...
It's good idea for client apps to check if the value is changed prior issuing the data modification statement. That is why ORM's quite often do this. It has nothing to do with row versioning on SQL level. From client prospective, even if the value was changed, by someone else, while user is looking into the previous version of it, it's absolutely fine not to issue update if the value is not changed. Otherwise, if row versioning is implemented, requesting non-updating update will return user some indication of data collision as the old (non-changed) version will try to overwrite newer one.
-
Sergiy (9/7/2012)
Because if I'd see anything what would show CLEARLY "that there is NO ROW MODIFICATION where the object of the update is unchanged" I'd happily accept it.Try this:
USE tempdb;
GO
IF OBJECT_ID(N'dbo.Test', N'U') IS NOT NULL
DROP TABLE dbo.Test;
GO
CREATE TABLE Test
(
ID integer IDENTITY(1,1) NOT NULL,
Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL
);
GO
INSERT dbo.Test
(Name)
VALUES
('Tom');
GO
CHECKPOINT;
DBCC FREEPROCCACHE;
GO
UPDATE dbo.Test
SET Name = 'Tom'
WHERE 1 <> 2;
GO
UPDATE dbo.Test
SET Name = 'TOM'
WHERE 1 <> 2;
GO
SELECT
query =
SUBSTRING
(
dest.[text],
deqs.statement_start_offset / 2 + 1,
CASE
WHEN deqs.statement_end_offset = -1
THEN DATALENGTH(dest.[text])
ELSE ((deqs.statement_end_offset - deqs.statement_start_offset) / 2) + 1
END
),
deqs.last_logical_writes,
deqs.last_logical_reads
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS dest
WHERE dest.[text] LIKE N'UPDATE%';
Output:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Sergiy (9/7/2012)
Because if I'd see anything what would show CLEARLY "that there is NO ROW MODIFICATION where the object of the update is unchanged" I'd happily accept it.In SQL Server 2008 or later:
USE tempdb;
GO
CREATE TABLE Test
(
ID integer IDENTITY(1,1) NOT NULL,
Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL
);
GO
INSERT dbo.Test
(Name)
VALUES
('Tom');
GO
CREATE PROCEDURE dbo.UpdateSame
AS
UPDATE dbo.Test
SET Name = Name;
GO
CREATE PROCEDURE dbo.UpdateMixedCase
AS
UPDATE dbo.Test
SET Name = 'Tom';
GO
CREATE PROCEDURE dbo.UpdateCaps
AS
UPDATE dbo.Test
SET Name = 'TOM';
GO
CHECKPOINT;
DBCC FREEPROCCACHE;
GO
EXECUTE dbo.UpdateSame;
GO
EXECUTE dbo.UpdateMixedCase;
GO
EXECUTE dbo.UpdateCaps;
GO
SELECT
proc_name = OBJECT_NAME(deps.[object_id]),
deps.last_logical_writes,
deps.last_logical_reads
FROM sys.dm_exec_procedure_stats AS deps
WHERE
deps.[object_id] IN
(
OBJECT_ID(N'dbo.UpdateSame', N'P'),
OBJECT_ID(N'dbo.UpdateMixedCase', N'P'),
OBJECT_ID(N'dbo.UpdateCaps', N'P')
)
GO
DROP PROCEDURE
dbo.UpdateSame,
dbo.UpdateMixedCase,
dbo.UpdateCaps;
GO
DROP TABLE
dbo.Test;
Output:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Wow! What a thrilling as well as informative thread this has been! 😎
Sujeet Singh -
SQL Kiwi (9/7/2012)
Try this:
....
I don't think it proves anything.
Logical/physical reads/writes are about pages read/written.
There was never an argument that SQL Server finds pages affected by the UPDATE not changed and therefore no further action is taken.
_____________
Code for TallyGenerator -
SQL Kiwi (9/7/2012)
In SQL Server 2008 or later:I changed the execution part of you script a little bit:
EXECUTE dbo.UpdateSame;SELECT * FROM dbo.Test -- added to check that the value in the record is actually changed
GO
EXECUTE dbo.UpdateCaps;
SELECT * FROM dbo.Test
GO
EXECUTE dbo.UpdateMixedCase;
SELECT * FROM dbo.Test
Here is the outcome:
Outcome IDName
UpdateSame1Tom
UpdateCaps1TOM
UpdateMixedCase1Tom
UpdateCaps 13
UpdateMixedCase03
UpdateSame03
So, despite the value has been changed by UpdateMixedCase from 'TOM' to 'Tom' no logical writes recorded.
Obviously, these statistics cannot be used like that for the case.
_____________
Code for TallyGenerator -
Sergiy (9/9/2012)
So, despite the value has been changed by UpdateMixedCase from 'TOM' to 'Tom' no logical writes recorded. Obviously, these statistics cannot be used like that for the case.If you add a CHECKPOINT between procedure calls, you will see it counts the number of buffer pool pages dirtied by the operation. This is quite different from what the documentation currently says; I'll file a Connect bug to get that clarified. Shame, because I thought it showed how many writes the storage engine thought it had done. Oh well.
Existing documentation:
http://msdn.microsoft.com/en-us/library/ms189741
http://msdn.microsoft.com/en-us/library/cc280701
http://msdn.microsoft.com/en-us/library/cc280646
Connect item:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 121 through 135 (of 159 total)
You must be logged in to reply to this topic. Login to reply