Advent of Code 2017 Day 5–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is day 5 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1. This is going to be a crazy looping item, since it will move through the list, relative to the current spot, and incrementing items, I know this won’t be good in SQL.

Still. Worth solving.

Let’s load the data. I’ll use a table, but first, I’ll also add an identity. This will help me number instructions and figure out what the next one is.

( InstructionKey INT IDENTITY(1,1)
, Instruction INT)

There are issues with identities, but this is a great trick:

SELECT d.Instruction FROM dbo.Day5 AS d
-- reusable code
BULK INSERT Day5V FROM 'e:\Documents\GitHub\AdventofCode\2017\Day5\Input.txt' WITH (ROWTERMINATOR='\n')

Now I can get to work. Here’s the logic I used.

I wanted to first set some starting points. I have a counter (0 based, increment first). This determines how many times I jump around. I also need to track the current instruction key and the next key. And, of course, I need the instruction value.

The identity is the array index, or the instruction key (which place am I in). In this case, I’ll try to follow this logic.

Get the end (out of bounds, which is the max + 1). I loop until I get an jump outside of the end range. The loop does these items:

  • Get the current instruction jump
  • Set the next location to be the current key + the current jump
  • Update the current jump to increment by 1
  • Set the current instruction key to the next key
  • loop

This seems to be what I need. On the test set, this worked fine. When I first set this up, I used this code:

         @CurrentInstructionKey INT = 1 ,
         @Instruction INT ,
         @NextInstructionKey INT ,
         @counter INT = 0;
SELECT @end = MAX(InstructionKey) + 1
FROM dbo.Day5 AS d;

-- SELECT [end] = @end;

WHILE @CurrentInstructionKey < @end
     SET @counter = @counter + 1;
     SELECT @Instruction = Instruction
     FROM Day5
     WHERE InstructionKey = @CurrentInstructionKey;
     SELECT @NextInstructionKey = @CurrentInstructionKey + @Instruction;
     UPDATE dbo.Day5
     SET Instruction = Instruction + 1
     WHERE InstructionKey = @CurrentInstructionKey;
     SET @CurrentInstructionKey = @NextInstructionKey;
--PRINT @CurrentInstruction

SELECT Counter = @counter ,
        [current] = @CurrentInstruction;

When I ran this, it chugged for some time. I bet in Python or C#, which would solve quickly with arrays. With updates, it’s slow. Like minutes slow for 1074 rows.

However, it worked.

Part II

In this part, this instructions are almost the same, but based on the current instruction value, we either increase or decrease the value. Not a big change. Our new update looks like:

UPDATE dbo.Day5
SET Instruction = Instruction + CASE
                                     WHEN @Instruction >= 3 THEN
WHERE InstructionKey = @CurrentInstructionKey;

This also works, albeit slowly. I left this around 5:30 and went to the gym.

One of the easier puzzles.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.