Efficient Update statement? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Efficient Update statement?

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-10-29 : 12:38:41

Is this the most efficient way to change Date time values from

2013-08-15 12:00:00.000

to

2013-08-15 00:00:00.000

UPDATE TableName
SET Dateofbusiness = DATEADD(DAY, 0, DATEDIFF(DAY, 0, dateofbusiness))
WHERE DATEPART(hh, DateOfBusiness) != 0

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-29 : 13:32:28

UPDATE dbo.TableName
SET DateOfBusiness = CAST(DateOfBusiness AS DATE);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-11-04 : 08:44:32

I just discovered that some of our clients are on SQL2005. :( I tried to check version, but on the SQL2005 box, it still fails. Any other ideas that would work for 2005+?

IF @@Version LIKE '%2005%'
BEGIN
UPDATE TablelName
SET Dateofbusiness = DATEADD(DAY, 0, DATEDIFF(DAY, 0, dateofbusiness))
WHERE DATEPART(hh, DateOfBusiness) != 0
and UniqueID = 1
END
ELSE
BEGIN
UPDATE TableName
SET DateOfBusiness = CAST(DateOfBusiness AS DATE)
WHERE DATEPART(hh, DateOfBusiness) != 0
END

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-04 : 09:19:45

As you found out, DATE data type is available only in SQL 2008 and later.

Are you trying to find a different way to remove the time portion because you have performance problems? If you do have performance problems, it is likely not because of the computation time involved in removing the time portion. Almost always, bulk of the resources are spend in retrieving and rearranging data than in computations such as the one you are doing. So while using DATEADD(dd,DATEDIFF(dd,0,dateofbusiness),0) uses two functions as opposed to CAST(dateofbusiness as DATE) which uses only one, you would be hard-pressed to find any performance improvement. I would just use the method that works for all the versions of SQL Server that are of interest to you, and stick with that. Makes the code simpler and more readable.

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-11-04 : 11:00:20

I put the second part in dynamic sql to get past the compile errors I was getting on the 2005 machine. Thanks for the input.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-04 : 11:12:20

Dynamic SQL makes the code hard to read and maintain, not to mention potential for SQL injection and security risks (which may or may not be the case depending on how you structure your code). Also, the performance gain if any that you may have gained by using CAST instead of the DATEADD/DATEDIFF approach probably would be more than negated by the dynamic SQL.

What I was suggesting was to simply use:

...
SET DateOfBusiness = DATEADD(dd,DATEDIFF(dd,0,dateOfBusiness),0)
....

That will work correctly on all versions of SQL Server, and the performance will be just fine. If you have poor performance, it is not going to be made better by trying to change the part of the code that truncates the time portion.

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-11-04 : 11:25:39

I get ya. But we have hundreds of thousands of records to update of historical data. It takes approx 1 hour 40 min to update one table's records using the DATEADD method. It takes the same table 12 minutes to update using the DATE option. Many more of our clients are on 2008+, so if I can find a way to speed it up for the majority, that is what I am trying to do. Once the historical records are all updated, I am using the DATEADD method for new records added going forward.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-04 : 12:22:59

That is a real surprise to me. I didn't think it would make that much of a difference. To be honest, I am still not convinced; the time difference you see may be because in one case the data had to be retrieved from disk and in the second case, the data was already in the cache. You can test if that is the case by running both queries twice.

SET STATISTICS TIME ON;
update table1 set DateOfBusiness = DATEADD(dd,DATEDIFF(dd,0,dateOfBusiness),0);
update table1 set DateOfBusiness = CAST(dateOfBusiness as DATE);
update table1 set DateOfBusiness = DATEADD(dd,DATEDIFF(dd,0,dateOfBusiness),0);
update table1 set DateOfBusiness = CAST(dateOfBusiness as DATE);

Might be a moot point since you probably have already updated the data you need to. Besides, I might be wrong, which has been known to happen!! ;)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-04 : 12:43:22

I did a test, and which is making me less and less convinced that you are able to get a speed up of more than 3 times simply by switching to CAST. Here is the experiment:

CREATE TABLE #tmp (dt DATETIME);
GO

SET NOCOUNT ON;
INSERT INTO #tmp SELECT GETDATE();
 go 100000

SET STATISTICS TIME ON;

UPDATE #tmp SET dt = CAST(dt AS DATE);
UPDATE #tmp SET dt = DATEADD(dd,DATEDIFF(dd,0,dt),0);
UPDATE #tmp SET dt =  CAST(dt AS DATE);
UPDATE #tmp SET dt = DATEADD(dd,DATEDIFF(dd,0,dt),0);

The result is

SQL Server parse and compile time: 
   CPU time = 6 ms, elapsed time = 6 ms.

 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 148 ms.

 SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 154 ms.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 105 ms.

 SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 83 ms.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-11-04 : 14:16:24

The WHERE clause will like cause a bigger performance hit than the DATEADD computations (which are simple integer math). Worse, the WHERE clause is not accurate, as the time portion could be:
00:23:17.997, for example. The new WHERE below likely won't help performance, but at least it's accurate.


UPDATE TableName
SET Dateofbusiness = DATEADD(DAY, 0, DATEDIFF(DAY, 0, dateofbusiness))
WHERE
    DATEDIFF(MS, DATEADD(DAY, 0, DATEDIFF(DAY, 0, dateofbusiness)), dateofbusiness) <> 0

Btw, SQL Server has a built-in mechanism to prevent actually updating a column to the same value, you could try letting that take care of it by removing the WHERE clause.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-04 : 14:40:49

Ah, I had not seen the WHERE clause in the original query that JAdAuto posted. Thanks Scott.

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-11-04 : 16:23:11

In this specific case, the hour will always be 12 or 0. ALWAYS, which is why I am checking Hour only. Time comes from the other system as 12:00:00.000 and we want 00:00:00.000 for this one field. In my initial tests, I had a backup of the table as is. There were about 300K+ records to update. I ran the DATEADD test, noted time and number of records updated. Then I dropped table and copied my backup into the original table name. Then I ran CAST as DATE test and noted the time and number of records to update (same count). Would this create a cache issue with false time comparisons?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources