2019 University of Scouting – Dutch Oven Cooking

Cast Iron Cookware

What is a Dutch Oven?

Dutch oven is a thick-walled cooking pot with a tight-fitting lid. Dutch ovens are usually made of seasoned cast iron; however, some Dutch ovens are instead made of cast aluminium, or ceramic. Some metal varieties are enameled rather than being seasoned (see next section).

Acquiring a Dutch Oven

You can start your Dutch Oven journey at Walmart or Amazon.

Dutch Ovens come in many shapes and sizes. Typically, in Scouting, we use standard 10-inch diameter (4 Quart) and 12-inch (6 Quart) ovens.

You want to make sure you get a flanged lid for putting coals on top. Some dutch ovens are for home use and have dome style lids.

What else do I need?

How much money do you have to spend?

In order of preference, I recommend the following:

  • A Lid Lifter $5-10 (while the right stick can work, a lid lifter is very handy)
  • Charcoal Starter/Chimney $10-20
  • Welding gloves $15-20
  • Dutch Oven Care Kit – Scrubber, Scraper, Oil, Cast Iron Soap $15-50
  • A Boy Scout Firestarter $40

Preparing your Dutch Oven for Use

Great news! Your cast iron cookware is probably pre-seasoned at the factory so you can just dive in and use it. If it is, skip ahead the next part

Seasoning your Dutch Oven

Seasoning is the process of treating the surface of a cooking vessel with heated fat in order to produce a corrosion-resistant and stick-resistant coating.

Food sticks easily to a new bare metal pan; it must either be oiled before use, or seasoned. The coating known as seasoning is initially created by a process of layering a very thin coat of oil on the pan. Then, the oil is polymerized to the metal’s surface with high heat for a time. The base coat will darken with use. This process is known as “seasoning”; the color of the coating is commonly known as its “patina”.

To season a pan (e.g., to season a new pan, or to replace damaged seasoning on an old pan), the following is a typical process:

  1. cleaning the cookware to remove residues from manufacturing or manufacturer-applied anti corrosion coating and expose the bare metal,
  2. applying a thin layer of animal fat or cooking oil (ranging from vegetable oil to lard, including many common food-grade oils), and
  3. heating the cookware to generate the seasoned coating.[5][6][7][8]

If it is not pre-seasoned, a new cast iron skillet or dutch oven typically comes from the manufacturer with a protective coating of wax or shellac, otherwise it would rust. This needs to be removed before the item is used.[9] An initial scouring with hot soapy water will usually remove the protective coating. Alternatively, for woks, it is common to burn off the coating over high heat (outside or under a vent hood) to expose the bare metal surface. For already-used pans that are to be re-seasoned, the cleaning process can be more complex, involving rust removal and deep cleaning (with strong soap or lye,[10] or by burning in a campfire or self-cleaning oven[11]) to remove existing seasoning and build-up. Once the pan has been heated, dried, and thinly layered with oil or fat, it is placed in an oven, grill, or other heating enclosure for the oil to be polymerized onto the metal’s surface. The process of polymerization is dependent on the oil, temperature of the enclosure, and the duration. The precise details of the seasoning process differ from one source to another, and there is much disagreement regarding the correct oil to use. There is also no clear consensus about the best temperature and duration. Lodge Manufacturing uses a proprietary soybean blend in their base coats as stated on their website. Others use lard, or animal fats. Some advocate the use of food-grade flaxseed oil (a drying oil).[12] The temperature recommended for seasoning varies from high temperatures above 260 °C (500 °F) to temperatures below 150 °C (302 °F). Some say that a temperature around the smoke point of the oil or fat should be targeted since this will allow vaporization of the lighter hydrocarbons from the oil, leaving behind heavier molecules for optimal polymerization and carbonization to occur. And, there is also no consensus on the correct duration of heating: from half an hour to an hour is often recommended.

Storing your Dutch Oven

After use Dutch ovens are typically cleaned like other cast-iron cookware: with boiling water, and a soft brush or sponge. Where possible, a cleaned and freshly oiled Dutch oven should be stored in a clean, dry location with the lid ajar or off to promote air circulation and to avoid the smell and taste of rancid oil. If the Dutch oven must be stored with the lid on, a paper towel or piece of newspaper should be placed inside the oven to absorb any moisture.

Cooking in a Dutch Oven

The Dutch Oven Dude’s advice about Cooking temperature is excellent.

Recipes

Here are some links to Dutch Oven Recipes:

Master Chef Contest Winners

Drum roll, please … find the winners of the Scouting magazine Dutch Oven Master Chef Contest below. Click on each winning recipe to see photos and find instructions on how to make these on your next campout.

SAVORY Category

SWEET Category

Resources

Here are some links to useful Dutch Oven articles:

Byron’s Dutch Oven Cooking

Apparently, Byron’s Dutch Oven Cooking site (without a doubt the best resource for Dutch Oven cooking) has been removed from the internet. There are some pages archived in the Internet Way Back Machine here.

The entire site has been reconstituted into a PDF file, downloadable here.

2019 University of Scouting – Winter Camping Fun

Troop Camping in the Winter Months

Rules

1st Rule:  Be prepared for any weather in any extreme.

2nd Rule:  Plan for cold, wet, windy weather.  (And be grateful when it’s not.)

3rd Rule:  Plan for the Scouts to be under-prepared for extreme (especially wet) conditions.

Principles

There are three key principles to keep in mind when planning a winter campout:

  1. Stay dry – wear waterproof outer layer; change clothes before going to sleep; wet clothes + cold weather = uncomfortably cold Scout, and serious risk of Hypothermia.
  2. Dress in layers – wear a wicking base layer (polyester), insulating middle layer (fleece or wool), and water/wind resistant outer layer (ideally Gore-Tex material).
  3. Avoid cotton material – it absorbs moisture and dries too slowly, and wet clothes draw heat away from the body at an alarming rate.

Safety

Stay Warm, Stay Dry, Stay Hydrated

Frostbite

Hypothermia

Sun/wind burn

Make sure that you have a good knowledge of the signs of frostbite and hypothermia. You should be able to recognize it in others and in yourself. Tell someone right away if you or another scout is showing signs of cold-related problems.

Keep out of the wind if you can. A rain fly for a tent can be pitched to serve as a wind break. The wind chill factor can often be considerable and can result in effective temperatures being much lower than nominal.

Bring extra WATER. It’s easy to get dehydrated in the winter. You aren’t visibly sweating, so you don’t think to drink water, but since the air is so dry, you lose a LOT of water through breathing. Drink lots of water!

Clothing

Polyester materials are intended to wick sweat away from the skin (e.g., Under Armor T-Shirts).  Sweat wicking material is often disguised under other material names such as: nylon, polypropylene, capilene, spandex, and lycra. If it is made with more than 40% cotton, it is NOT a satisfactory wicking material.

The key to cold weather camping is to stay warm and dry. Bring both light and heavy weight clothing in order to “layer” if the weather is cold. Scouts should remove layers if they start to overheat and sweat. For base layer (i.e., underwear, socks, t-shirt), bring at least one change per full day of camping.

Everyone must be dry by sundown. No wet (sweaty) bodies or wet inner clothing.

Dress in layers, the trapped air helps keep you warm, and you can shed layers if you warm up.

STAY DRY!! If you get wet, make sure you change into dry clothes as soon as possible. In order to do that, you must have more than 1 article of clothing with you. For example, 3 pairs of wool socks, 2 pairs of pants, etc.

NO COTTON clothing as your primary clothing. NO JEANS!  (This is especially true in the snow or icy cold rain.)

Make sure you have snow pants, nylon wind pants, or wool pants, and polypropylene or wool long underwear.

Sleeping

Dress right while sleeping. Change into clean, dry clothes before bed. Your body makes moisture and your clothes hold it in – by changing into dry clothes you will stay warmer and it will help keep the inside of your sleeping bag dry. Wearing wool socks and long underwear (tops and bottoms) in the sleeping bag is OK.

Put on tomorrow’s t- shirt and underwear at bedtime. That way you won’t be starting with everything cold next to your skin in the morning.  Put tomorrow’s clothes in your bag with you to take up space in the bag and to warm them for the morning.

Put a couple of long-lasting hand warmers into your boots after you take them off. Your boots will dry out during the night.

Don’t sleep directly on the ground. Get a closed cell foam pad to provide insulation between your sleeping bag and the ground. A foam pad cushions and insulates. The air pockets are excellent in providing good insulation properties. Use more than one insulating layer below you – it’s easy to slide off the first one.

No cots! Better to lay on 30F earth instead of -10F air.

If in tents, leave the tent flaps/zippers vented a bit, it cuts down on interior frost.

Drain your bladder before you go to bed. Having to go in the middle of the night when it is 5 degrees out chills your entire body. Drink all day but stop one hour before bed.

Food

Bring extra food that doesn’t need to be heated or cooked. Granola bars, trail mix, etc.

Keep a pot of hot water available for cocoa or Cup-a-Soup – these warm from the inside.

Always eat hot meals (breakfast, lunch, & dinner.) Dutch ovens are the best – they keep the food hot longer. It doesn’t need to be fancy DO cooking. Meals should be 1-pot meals to keep cleanup to a minimum. Don’t get too fancy with the meals – it’s hard to chop onions & carrots at -10F with gloves on. Prep all meals at home in the warmth of the kitchen.

Shelter the cooking area from wind (walls of tarps, etc.)

Fill coffee/cook pots with water before bed. It’s hard to pour frozen water, but easy to thaw it if it’s already in the pot.

Eat a high-energy snack before bed, then brush your teeth. The extra fuel will help your body stay warm. Take a Snickers bar to bed and eat it if you wake up chilly in the night.

Tip

Always bring a bit more than what you think you’ll need – water, food, clothes.

Winter Camping Games and Fun

Broom Hockey:  play hockey on a lake or pond using brooms for hockey sticks and a tennis ball for a puck.

Water Machine Contest:  a water machine is simply a old burlap bag or other porous material (tarp).  Gather snow in the bag or on a tarp, gather the top or the corners and tie off the too.  Then hang the bag or tarp with the snow in it near a fire.  Put a pot or No. 10 can below to catch.  Have Scouts start from scratch by gathering wood and building a tire as well as gathering snow.  This promotes teamwork and gives everyone in the Patrol something to do.  The first  patrol to “make” a quart (or gallon) of water wins.  The water machine is also an excellent technique for maintaining a continual water supply while winter camping.

Snow Golf:  the same as miniature golf, except that the fairways are snow covered and the greens are packed down areas with a tin can buried in the snow for the hole.  The golf balls are hockey pucks hit with old golf clubs. 

Learn the Basics of Winter Photography: sponsor a winter photography contest by Patrols or individuals.

Exploring:  no phase of Scoutcraft can better form motive for a long hike than exploring the Woods in Winter.  Exploring (and mapping) a given tract of woodland will prove rich in all around Scout training.  It will furnish instruction, recreation and exercise.  It will involve not only technical practice in surveying and map-making but also cooking, camping and woodcraft in general.  The instructive side will be interesting in itself and one may rest assured that the games, stunts and story-telling contests around the campfire will have unusual energy.  

Patrol Animals Snow Sculpture Contest:  The actual carving of statuary is another fascinating pastime.

Mystery games like Murder in the Dark and Mafia are a great option when you’re sitting around the campfire at night. No cards or table needed and the darkness and night noises will help make the game more mysterious.

Corn Hole, Ladder Toss, Bocce Ball are all good choices for no snow winter camping. Anything that keeps the gloves on and keeps the Scouts active will be enjoyable.

Winter Camping in Middle Tennessee

Expect the Scouts to wear jeans and a hoodie in cold weather.  This is plenty to get them from the front door to the bus stop in cold weather, it should be fine for Scout camp too, right?

Except it’s not.  Encourage them to pack and wear warmer clothing, in layers, and to at least bring a waterproof layer in case it rains, sleets, or snows over the campout.  Also, be certain to build a fire, not a pretty fire that’s nice to look at, a warm fire that can heat up cold hands and cold, wet bodies.

Winter Camping “up North”

If you’re camping in the snow, wear snow pants over your regular clothing

Bring extra hand covering – mittens are warmer than gloves.

Bring 2 changes of socks per day.

Fill a couple of Nalgene water bottles with warm water and sleep with one between your legs (warms the femoral artery) and with one at your feet. Or use toe/hand warmers. Toss them into your sleeping bag before you get in. Some of the toe/hand warmers will last 8 hours.

Sleeping

Use a sleeping bag that is appropriate for the conditions. Two +20F sleeping bags, one inside the other will work to lower the rating of both bags.

Use a bivvy sack to wrap around your sleeping bag. You can make a cheap version of this by getting an inexpensive fleece sleeping bag. It isn’t much more than a blanket with a zipper, but it helps lower the rating by as much as 10 degrees.

Use a sleeping bag liner. There are silk and fleece liners that go inside the sleeping bag. They will lower your sleeping bag’s rating by up to 10 degrees. Or buy an inexpensive fleece throw or blanket and wrap yourself in it inside the sleeping bag.

Most cold weather bags are designed to trap heat. The proper way to do this is to pull the drawstrings until the sleeping bag is around your face, not around your neck. If the bag also has a draft harness, make sure to use it above the shoulders and it snugs up to your neck to keep cold air from coming in and warm air from going out.

Don’t burrow in – keep your mouth and nose outside the bag. Moisture from your breath collecting in your bag is a quick way to get really cold. Keep the inside of the bag dry.

A zipped-up coat pulled over the foot of a sleeping bag makes an extra layer of insulation.

Winter Camping Kit List

Bedding

  • Sleeping bag – warm bag, ideally rated as a “zero degree” bag or better
  • Wool or fleece blankets – to put over and under sleeping bag as extra insulation if sleeping bag is not rated as “zero degree” (adds 10-20 degrees of warmth)
  • Pillow (optional)
  • Ground pad – either foam pad or Therm-A-Rest pad
  • Stuff sack for sleeping bag (preferably waterproof sack)

Clothing

  • At least 3 polyester underwear
  • At least 3 pairs of heavy socks with liner socks.  NOT cotton sweat socks.
  • At least 3 polyester base shirts – long or short sleeve (worn against skin)
  • 1-2 insulating fleece pullovers or wool sweaters
  • Hooded sweat shirt and sweat pants to sleep in (this can be cotton for sleeping at night)
  • Insulated coat/ jacket that is wind/water resistant – suitable for camping environment
  • Winter stocking cap that covers entire head and ears
  • Balaclava or ski mask to cover head and face (optional)
  • Bandana or handkerchief
  • 2 pair warm gloves or mittens (outer material should be wind/water resistant)
  • Winter boots (with adequate insulation and waterproof material)
  • Extra Pair of footwear – sneakers are OK as supplement to winter boots; Crocks/sandals are NOT appropriate footwear for winter camping
  • Rain gear (poncho or water-resistant pants/top shell)

Other Items

  • Back pack or duffel bag for personal gear
  • Garbage bags to store your clothes (and keep them dry)
  • Wash kit – Soap, wash cloth, towel, comb, deodorant, toothbrush, toothpaste, sunscreen, lip balm, toilet paper, feminine hygiene products
  • Mess kit – drinking cup, bowl, plate, and utensils with your name on it
  • Boy Scout Handbook (in a plastic, zip-lock bag)
  • Water bottle or canteen
  • Headlamp (preferred), or Flashlight, with extra batteries
  • Compass (optional)
  • Camping chair (optional)
  • Sunglasses
  • Personal first-aid kit
  • Medicines and medical supplies (Must co-ordinate with adult leader)
  • Toboggan/sled (Optional)
  • Pocket knife (Optional)
  • Whistle

Troop Provided Items

  • Tents
  • Cooking equipment, food and cleaning supplies
  • First-aid kit
  • Snow shovel
  • Rope, twine
  • Ground sheet, tarps for under tents
  • Fire starter, fuel, firewood, etc.
  • Toilet paper
  • Duct tape
  • Trash bags
  • Rubber gloves
  • Cards

Resources

Winter Camping Tips, Checklists and Preparation

Winter Camping Games and Fun

Quickly Processing One Million Transactions in Azure SQL Database

I’ve had to solve an interesting problem this week. I started the week with an SSIS package that ran in 2 minutes. It extracts a million transaction records from DB2 to a file, uploads the file to Azure Blob Storage and BULK IMPORT’s the file into an Azure SQL Database staging table. All in 2 minutes. This was acceptable performance and faster than any of the other options we were considering.

Each of the DB2 records represents an Insert, Update or Delete to the base table in DB2. I get the Transaction Type (M – Merge (Insert/Update) or D – Delete), and a time stamp in addition to the columns from the row.

So a typical set of rows might look like this:

Id (Identity)Field1Field2Field3Field4TrxTypeRowCreatedTimeStamp
11394000112478577941M11:37:31.650
21394000122478577920M11:37:32.070
31394000132478577926M11:37:32.185
41394000122478577921M11:37:32.205
5139400013247857794M11:37:32.265
61394000122478577929D11:37:32.391
71394000122478577918M11:37:33.392

In the example above, the rows are all for the same document (See Field1:  value 13940001).  Rows with Field2= 1, 2, 3 were added.  Then row 2 and 3 were changed (Id 4, 5).  Then row 2 was deleted (Id 6) and a new row 2 was inserted (Id 7).

Here is the definition of the source table in the Azure SQL Database:

CREATE TABLE ETLSource(
       Field1 [numeric](12, 0) NULL,
       Field2 [smallint] NULL,
       Field3 [int] NULL,
       Field4 [smallint] NULL,
       Field5 [nvarchar](1) NULL,
       [UpdateType] [nchar](1) NOT NULL,
       [RowCreated] [datetime2](7) NOT NULL,
       [Id] BIGINT IDENTITY NOT NULL
) WITH (DATA_COMPRESSION = PAGE)
GO

CREATE UNIQUE CLUSTERED INDEX ETLSourcePK ON ETLSource (Id) WITH (DATA_COMPRESSION = PAGE);
CREATE UNIQUE NONCLUSTERED  INDEX ETLSourceIdx1 ON ETLSource (UpdateType, RowCreated, Field1, Field2) WITH (DATA_COMPRESSION = PAGE);
CREATE UNIQUE NONCLUSTERED  INDEX ETLSourceIdx2 ON ETLSource (Field1, Field2, UpdateType, RowCreated) WITH (DATA_COMPRESSION = PAGE);
GO

And here is the definition of the target table in the Azure SQL Database:

CREATE TABLE ETLTarget(
       Field1 [numeric](12, 0) NULL,
       Field2 [smallint] NULL,
       Field3 [int] NULL,
       Field4 [smallint] NULL,
       Field5 [nvarchar](1) NULL,
       [BatchDate] [datetime2](7) NULL
) WITH (DATA_COMPRESSION = PAGE)
GO

CREATE CLUSTERED INDEX ETLTargetPK ON ETLTarget (Field1, Field2) WITH (DATA_COMPRESSION = PAGE);
GO

At first, I tried a cursor. I know how to write them and it was easy enough to create a cursor that looped through the rows and used either a DELETE statement or a MERGE statement to deal with each one. Here’s what that looked like:

DECLARE @BatchDate DATETIME2(7) = SYSUTCDATETIME();

DECLARE @Field1 NUMERIC(12, 0)
DECLARE @Field2 SMALLINT
DECLARE @Field3 INT
DECLARE @Field4 SMALLINT
DECLARE @Field5 NVARCHAR(1)
DECLARE @UpdateType	CHAR(1)
DECLARE @RowCreated	DATETIME2(7)

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT
	   Field1 
	  ,Field2 
	  ,Field3 
	  ,Field4 
	  ,Field5 
	  ,UpdateType
	  ,RowCreated
    FROM ETLSource
    ORDER BY id

OPEN cur

FETCH NEXT FROM cur INTO 
	 @Field1 
    , @Field2 
    , @Field3 
    , @Field4 
    , @Field5 
    , @UpdateType
    , @RowCreated

WHILE @@fetch_status = 0
BEGIN

    IF @UpdateType = 'D'
    BEGIN
	   DELETE FROM dbo.ETLTarget
	   WHERE Field1 = @Field1
		  AND Field2 = @Field2;
    END
    IF @UpdateType = 'M'
    BEGIN
	   --Merge the changes that are left
	   MERGE ETLTarget AS target 
	   USING (
		  VALUES(
		    @Field1 
		  , @Field2 
		  , @Field3 
		  , @Field4 
		  , @Field5 
		  )
	   ) AS source (
		Field1 
	   , Field2 
	   , Field3 
	   , Field4 
	   , Field5 )
	   ON (target.Field1 = source.Field1
		  AND target.Field2 = source.Field2)
	   WHEN MATCHED
		  THEN UPDATE
			 SET target.Field3 = source.Field3
			    ,target.Field4 = source.Field4
			    ,target.Field5 = source.Field5
			    ,target.BatchDate = @BatchDate
	   WHEN NOT MATCHED BY target
		  THEN INSERT (
			   Field1 
			 , Field2 
			 , Field3 
			 , Field4 
			 , Field5
			 , BatchDate)
		  VALUES (@Field1 
			 , @Field2 
			 , @Field3 
			 , @Field4 
			 , @Field5 
			 , @BatchDate);
    END;

    FETCH NEXT FROM cur INTO 
		@Field1 
	   , @Field2 
	   , @Field3 
	   , @Field4 
	   , @Field5 
	   , @UpdateType
	   , @RowCreated
END

CLOSE cur
DEALLOCATE cur

Unfortunately, this solution was TERRIBLY slow. Cursors are notorious for being slow. This one worked fine for 1,000 transaction rows, but, after running for an hour and only processing a small portion of the million rows, I killed it and went looking for a set-based alternative.

Next, I tried a set-based MERGE statement. This was problematic because it kept complaining that multiple source records were trying to change the same target record. This complaint made sense when I realized that a row might be inserted and updated in the same day so it would have two source transactions. So I needed to get rid of the extras. It turns out that I really only care about the latest change. If it’s an insert or update, MERGE will insert or update the target row appropriately, if it’s a delete, MERGE can handle that too. But, how to select only the most recent row for each key? The standard de-duplication CTE query served as a model. Here is the final statement that worked:

WITH sourceRows AS (
    SELECT *, RN  = ROW_NUMBER() OVER (PARTITION BY
	   Field1, Field2
	   ORDER BY Field1, Field2, RowCreated DESC)
    FROM ETLSourceStagingTable)

INSERT INTO ETLSource (
      Field1 
    , Field2 
    , Field3 
    , Field4 
    , Field5
    , UpdateType
    , RowCreated)
SELECT       
      Field1 
    , Field2 
    , Field3 
    , Field4 
    , Field5
    , UpdateType
    , RowCreated 
FROM sourceRows
WHERE RN = 1
ORDER BY RowCreated;

Note the introduction of a Staging Table. The SSIS package now uses BULK INSERT to load the Staging Table from the file in Blob Storage. The query above is used to load only the relevant rows (the most recent) into the ETLSource table. The Staging Table has the same structure as the ETLSource table, without the Id column. And has an index on it like this:

CREATE INDEX ETLSourceStagingTableSort ON ETLSourceStagingTable
(Field1, Field2, RowCreated DESC) WITH (DATA_COMPRESSION = PAGE)

The use of the Staging Table and the CTE query above meant that of my original 7 rows in the example above, only three are relevant:

Id (Identity)Field1SequenceField3Field4TrxTypeRowCreatedTimeStampRelevant
11394000112478577941M11:37:31.650YES
21394000122478577920M11:37:32.070
31394000132478577926M11:37:32.185
41394000122478577921M11:37:32.205
5139400013247857794M11:37:32.265YES
61394000122478577929D11:37:32.391
71394000122478577918M11:37:33.392YES

Now, I just needed to craft the MERGE statement properly to work. When I did, this is what I had:

MERGE ETLTarget AS target USING (
    SELECT 
	     Field1 
	   , Field2 
	   , Field3 
	   , Field4 
	   , Field5
	   , UpdateType
    FROM ETLSource
    ) AS source (Field1 
	   , Field2 
	   , Field3 
	   , Field4 
	   , Field5
	   , UpdateType)
ON (target.Field1 = source.Field1
    AND target.Field2 = source.Field2)
WHEN MATCHED AND source.UpdateType = 'M'
    THEN UPDATE
	   SET target.Field3 = source.Field3
		  ,target.Field4 = source.Field4
		  ,target.Field5 = source.Field5
		  ,target.BatchDate = @BatchDate
WHEN MATCHED AND source.UpdateType = 'D'
    THEN DELETE  
WHEN NOT MATCHED BY TARGET AND source.UpdateType = 'M'
    THEN INSERT (Field1 
	   , Field2 
	   , Field3 
	   , Field4 
	   , Field5
	   , BatchDate)
    VALUES (Field1 
	   , Field2 
	   , Field3 
	   , Field4 
	   , Field5
	   , @BatchDate);

Which was fine for a small data set, but crawled on a big one, so I added batching so the merge only had to deal with a small set of rows at once. Since the clustered PK is an identity column, and since I truncate ETLSource before loading it, I am guaranteed that the Id column will be values 1…n where n is the total number of rows. So, I initialize an @rows variable right after inserting the rows into ETLSource:

SET @rows = @@rowcount;

Next, I create a while loop for each batch:

DECLARE @batchSize INT = 10000;
DECLARE @start INT = 1;
DECLARE @end INT = @batchSize;

WHILE (@start < @rows)
BEGIN

    MERGE ETLTarget AS target USING (
    ...;

    SET @start = @start + @batchSize;
    SET @end = @end + @batchSize;
END

Then I add the @start and @end to the MERGE statement source:

MERGE ETLTarget AS target USING (
    SELECT 
	     Field1 
	   , Field2 
	   , Field3 
	   , Field4 
	   , Field5
	   , UpdateType
    FROM ETLSource
    WHERE id BETWEEN @start AND @end
    ) AS source

And this worked!!! I was able to process a million rows in 1 minute. Yay!

Then I tried 10 million rows. Ugh. Now the MERGE was only processing 10,000 rows per minute. Crap. What changed? Same code. Same data, just more of it. A look at the query plan explained it all. Here it is with 1 million rows:

And here it is with 10 million rows:

The ETLTarget table has 80 million rows in it. When I had 10 million rows in the ETLSource table, the query optimizer decided that it would be easier to do an Index SCAN instead of a SEEK. In this case, however, the SEEK would have been A LOT faster.

So how do we force it to use a Seek? It turns out the optimizer has no idea how many rows were processing in a batch, so it bases its optimization on the entire table. We could use the Loop Join hint a the end of the merge statement:

	MERGE	...	 , @BatchDate)
	   OPTION (LOOP JOIN);

But most folks like to avoid these kinds of hints. So we needed another way. Someone suggested putting in a TOP clause in the Source SELECT statement. That worked. Here’s how the MERGE looks now:

MERGE ETLTarget AS target USING (
    SELECT TOP 10000
	     Field1 
	   , Field2 
	   , Field3 
	   , Field4 
	   , Field5
	   , UpdateType
    FROM ETLSource
    WHERE id BETWEEN @start AND @end
    ORDER BY id
    ) AS source

With this in place, I was able to process the 10 million rows in 10 minutes. Woohoo! Just to be sure, I re-ran the process with a thousand, a million and 10 million rows and it was consistent. I was able to process a million rows a minute.