A friend of mine, with an interesting sense of humor, once asked me, “Do you know how you can eat a whole cow?” I knew there was going to be some “smart” type of answer, but I didn’t come up with anything and just told him I gave up. Then he revealed it, “one quarter pounder or T-bone steak at a time”. Now, when you think about it, it is easy to agree that probably most of us have eaten at least a whole cow over our lifetime, one piece at a time. Although it’s funny to think about it now, but what he mentioned to me all those years ago really became one of my common approaches to any big or complex problem.
Basically, attack a big obstacle or problem in small bits or chunks.
In my IT career, I find this to be really useful principle to live by. Many times IT professionals are challenged with big tasks, that could seem overwhelming. But, if we break it down into smaller pieces, it can actually be quite manageable. For example, many times DBA’s are given tasks like building and migrating a SQL Server environment or perhaps just updating a really large table. Now, in either case, we could apply this principle. And the reason I mention the latter is because of a recent personal experience. One of our system administrators wrote a query to update a table in a third-party piece of software. However, whenever that code ran, it was updating all the records in that large table and user performance suffered (i.e. user processes were being blocked by the update). So, I told him to apply this principle.
Updating a Large Table in Small Bits or Chunks
In order to illustrate how this can be done, we will first create a large table. Here’s a simple way to create a table with 10 million records:
SELECT TOP 10000000
IDENTITY(INT,1,1) AS N,
GETDATE() AS D
FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
With the third-party piece of software our company was using, the administrator had to update a path in every record. So, he was using a basic UPDATE statement that touched all the rows. So, I suggested something like the following to break the UPDATE statement into smaller pieces:
DECLARE @int INT
SELECT @int = 1
SET ROWCOUNT 100000
WHILE @int > 0
SET D = '1/1/2000'
WHERE D <> '1/1/2000'
SET @int = @@ROWCOUNT
SET ROWCOUNT 0
Now, I want to issue a disclaimer, because after SQL Server 2012, Microsoft says that the SET ROWCOUNT (http://msdn.microsoft.com/en-us/library/ms188774.aspx) won’t affect DELETE, INSERT, and UPDATE statements. So, although I have liked doing things this way for years, I would advise against it for new development. But, I just wanted to include it for reference purposes.
Here’s the recommended way you could do it now using UPDATE TOP (http://msdn.microsoft.com/en-us/library/ms189463.aspx):
WHILE EXISTS (SELECT * FROM dbo.Test WHERE D <> '12/31/2012')
UPDATE TOP (100000) dbo.Test
SET D = '12/31/2012'
WHERE D <> '12/31/2012'
Now, this is just a basic example of how this could be done. This methodology helped the users to not experience performance problems, and that system administrator to get his work done.
So, whenever you are faced with a big or complex task, remember to attack it in small chunks or bits!