Wednesday, August 10, 2016

Bulk upsert into SQL Server

I recently worked through a situation where a bulk upsert (update if a matching record already exists; otherwise insert a new record) to SQL Server implemented using Entity Framework 6 was producing the correct results, but running very slowly. 

I was able to improve the performance of the bulk upsert by about 600x (!!!) by replacing the Entity Framework implementation with a an new approach based on a stored procedure taking a table-valued parameter.

Entity Framework implementation – very slow

The original code, using Entity Framework 6 (altered to use a table / object type of “Employee” instead of the actual type I was working with):

DateTime now = DateTime.Now;
using (MyCustomContext context = new MyCustomContext())
{
    foreach (Employee employee in employeeData)
    {
        // Get the matching Employee record from the database, if there is one.
        Employee employeeRecord = context.Employees
            .Where(e => e.EmployeeID == employee.EmployeeID)
            .SingleOrDefault();

        bool isNewRecord = false;
        if (employeeRecord == null)
        {
            // We don't have a record in the database for this employee yet, 
            // so we'll add a new one.
            isNewRecord = true;
            employeeRecord = new Employee()
            {
                EmployeeID = employee.EmployeeID,
                CreationDate = now
            };
        }

        employeeRecord.ModificationDate = now;
        employeeRecord.FirstName = employee.FirstName;
        // (Set the remaining attributes...)

        if (isNewRecord)
        {
            context.Employees.Add(employeeRecord);
        }

        context.SaveChanges();
    }
}

That approach was taking around 300 seconds per 1000 records inserted.  It was bogging down my application in situations where I needed to insert/update tens of thousands of records at once.

Stored Procedure + Table-Valued Parameter implementation – fast!

In my testing, the revised approach below, replacing the Entity Framework implementation with a stored procedure taking a table-valued parameter, was able to upsert the same 1000 records in 0.5 second – a huge improvement!

I needed to add a custom table type with columns matching the Employees table:

CREATE TYPE EmployeesTableType AS TABLE
(
    [EmployeeID] [int] NOT NULL,
    [CreationDate] [datetime] NOT NULL,
    [FirstName] [nvarchar(100)] NOT NULL,
    /* More fields here... */
);

Next, the stored procedure to perform the upsert, taking input of an instance of the custom EmployeesTableType, and using the SQL Server MERGE command:

CREATE PROCEDURE dbo.UpsertEmployees
    @UpdateRecords dbo.EmployeesTableType READONLY
AS
BEGIN
    MERGE INTO Employees AS Target
    USING @UpdateRecords AS Source
    ON Target.EmployeeID = Source.EmployeeID
    WHEN MATCHED THEN
        UPDATE SET Target.EmployeeID = Source.EmployeeID,
            Target.FirstName = Source.FirstName,
            /* More fields here... */
    WHEN NOT MATCHED THEN           
        INSERT (EmployeeID,
            FirstName,
            /* More field names here... */
            )
        VALUES (Source.EmployeeID,
            Source.FirstName,
            /* More field values here... */
            );
END

Finally, here’s the C# code I used to execute the stored procedure:

public static void UpsertEmployeeData(List<Employee> employees)
{
    string connectionString = MyCustomMethodToGetConnectionString();
    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandText = "dbo.UpsertEmployees";
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter parameter = command.Parameters.AddWithValue("@UpdateRecords",
                CreateUpdateRecordsSqlParam(employees));  // See implementation below
            parameter.SqlDbType = SqlDbType.Structured;
            parameter.TypeName = "dbo.EmployeesTableType";

            command.ExecuteNonQuery();
        }
    }
}

private static DataTable CreateUpdateRecordsSqlParam(IEnumerable employees)
{
    DataTable table = new DataTable();
    table.Columns.Add("EmployeeID", typeof(Int32));
    table.Columns.Add("FirstName", typeof(string));
    // More fields here...

    foreach (Employee employee in employees)
    {
        table.Rows.Add(employee.EmployeeID,
            employee.FirstName,
            // More fields here...
    }

    return table;
}

Ideally, this post will serve as a useful reference the next time I, or you, need to code up a bulk upsert into SQL Server!

Credit to this StackOverflow answer by Ryan Prechel which was the primary inspiration for this approach.

Wednesday, July 13, 2016

C# Reflection: Setting a private enum-type member variable with a private inner enum type value

Today, while creating a C# unit test, I had a situation where I needed to set the value of a private variable on the class under test.  The variable was an enum-type variable, where the enum type itself was a private inner type defined in the class under test.

(I won’t in this post get into why I ended up landing on doing this, instead of some other solution such as refactoring the actual class under test in order to avoid the need to do so, as that would be a separate discussion.)

This post describes the technique using reflection that I ended up using to accomplish this.

For example, given a class defined like:

class Card
{
    private Suit _suit;

    private enum Suit
    {
        Hearts,
        Diamonds,
        Clubs,
        Spades
    }
}

Given an instance of that class named card, I was able to set the value of that object's private _suit variable by doing:

using System.Reflection;

...

// Get a reference to the _suit member variable.
FieldInfo suitField = card.GetType().GetField("_suit", BindingFlags.NonPublic | BindingFlags.Instance);

// Get a reference to the Spades member of the Suit enum.
object spades = card.GetType().GetNestedType("Suit", BindingFlags.NonPublic).GetField("Spades").GetValue(card);

// Set the value of _suit on our card object to Spades.
suitField (card, spades);

This kind of reflection hackery probably isn’t a best practice in most situations! Still, I thought it might be helpful to record here as an aid for those rare situations where it does make sense to manipulate this kind of enum-type private variable for unit testing purposes.

Thursday, May 26, 2016

Solution: HTTP POST via curl has “data” value unexpectedly truncated

Problem

When using the curl command-line utility to manually sent an HTTP POST to a server, the “data” value specified in the message is unexpectedly truncated when the server receives it. 

For example, given this command line:

curl --request POST "https://www.myserver.example.com/api/submit" --header "Content-Length:115" --header "Accept-Language:en-us" --header "Host:www.myserver.example.com" --header "Accept:image/jpeg, application/x-ms-application, image/gif, application/xaml+xml, image/pjpeg, application/x-ms-xbap, application/x-shockwave-flash, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, */*" --header "User-Agent:Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1)" --header "Connection:Keep-Alive" --header "Cookie:ASP.NET_SessionId=some_sesson_token_here;" --header "Referer:https://www.myserver.example.com/" --header "Content-Type:application/x-www-form-urlencoded; Charset=UTF-8" --data "PrimaryID=719&SecondaryID=27483&email1=someone@example.com&email2=someone2@example.com&message=Visit+my+site+at+http://mysite.example.com&TertiaryID=1738242&subject=Subject+text+goes+here"

The server (an ASP.NET MVC application) received the message, but the “message” parameter was assigned a value of “Visit my site at http” – the “://mysite.example.com” portion of the value was missing.

The subsequent included parameters in the “--data” value, such as TertiaryID, were also completely missing their values, according to the server.

Solution

The problem is the Content-Length header value (from earlier in the command line).  As written, it has a value of 115, so the server is truncating the data value after 115 characters (which happened to be just after the “http” in the “message” parameter in this example).

The solution is to either set the Content-Length value to the actual length of the data value, or to just omit the Content-Length header entirely.

Thanks to my colleague Kevin for pointing that out and saving my sanity!  I had originally approached this problem assuming that the “://” was the problem – that curl wasn’t sending it correctly, and/or that the server was refusing it (possibly for security reasons) – but that turned out to be just a red herring, based on the fact that the incorrect Content-Length value just happened to fall near that substring’s position.

Friday, April 15, 2016

Vigil RPG (“Premium” iPhone Game) – Lifetime Sales Stats

Sometime in mid-2013, I had a hankering to play a particular kind of RPG on my iPhone. I wanted a game with these features:

  • Turn-based combat.
  • Portrait orientation, and thus playable with one hand. (e.g. while eating with the other hand.)
  • A single protagonist/hero. One thing I don't like about party-based RPGs is that typically, a couple of your party members need be KO’ed before you feel like the team is actually in any real danger. This doesn't tend to happen against non-boss enemies in most games, and thus those games often end up feeling uninteresting for long stretches.
  • Interesting decision-making in combat -- even vs. non-boss enemies -- something beyond the typical RPG trope of "do basic attacks / target enemy elemental weaknesses / heal self when injured / repeat."
  • No hard-to-use on-screen virtual D-pad for character movement. Give me a way to move my character that’s designed especially for a touchscreen, not one based on a traditional physical controller’s tactile D-pad!
  • A combat system built around LOW numbers and visible enemy HP / stats, so I can calculate that if, for example, that enemy has 9 HP left, then I can perfectly finish it off by doing my 4 and 5 HP attacks respectively over the next 2 rounds.
  • FAST combat. No waiting on long combat animations; no wading through multiple menus to kick off a combat round. This is my phone; let me whip it out when I’ve got 30 seconds, and actually accomplish something quickly.
  • No save points. Why not just always keep my game saved automatically?  (Even mid-combat!)
  • Game designed with a goal of fun, not of corporate revenue generation! Absolutely no IAPs or premium currencies or ads or stamina timers.

I couldn't find that game on the App Store.

So... I decided to write it myself!

After spending most of my evenings between 10:00pm and midnight (after my day job, spending time with my family, getting the kids into bed, and daily chores) for about 18 months designing and writing the game – learning the Objective-C programming language and the whole MacOS / iOS development ecosystem along the way – Vigil RPG was released in November 2014!

Here’s Vigil RPG’s combat screen, which illustrates the realization a lot of the points noted above that I wanted to achieve with the game.  You can check out more screen shots and info about the game at the Vigil RPG website!

 

Lifetime App Store Sales Stats

I don't really have any reason to keep them private, and I thought it might be insightful for other #indiedev folks and industry observers, so without further ado, here are the lifetime sales statistics to date for Vigil RPG (iOS)!  According to my developer account at iTunes Connect:

2016-04-15 12_27_17-iTunes Connect

  • Released November 2014 at a price of US $2.99
  • 354 paid copies sold, almost entirely at $2.99, with a few at $1.99 in a "birthday sale" in November 2015
  • Total gross sales: US $1004
  • About 70% of the lifetime sales of Vigil RPG came in the first 30 days after release.
  • Vigil RPG got about ten 5-out-of-5-star community reviews on the App Store (and no 0-through-4-star reviews) immediately after release; it’s gotten zero community reviews since then.  (Vigil RPG has no “review nag” prompts, which was an intentional design decision.)
  • The second big spike in sales was after the 4-out-of-5-star TouchArcade review (which I was thrilled with, and found to be extremely on-point and fair – much respect to the reviewer, Shaun Musgrave). TouchArcade was the only major site to do a review.
  • The little spike in November 2015 was the beginning of the $1.99 sale.  Sales dropped off again rapidly even though I left the price at $1.99 for a while.
  • Outside of the initial release and $1.99 sale periods, Vigil RPG sold at a rate of roughly 1 copy per week.
  • Net proceeds after Apple's cut: US $707
  • 3 x $US 99 of Apple annual developer licenses to develop the game and keep it live on the App Store = $297. Net proceeds after Apple dev license fees: $410
  • Other misc. operating costs -- State of Michigan incorporation fees for Aggro Magnet Games LLC, web hosting for http://aggromagnetgames.com -- of around $100 to date.  Bottom line proceeds to date: About $310
  • 122 free copies redeemed (promo codes sent to review sites; a few free giveaways to try and drum up visibility and community interest)
  • I didn’t bother trying to keep any stats on piracy rates, but at least one site out there (fairly readily findable via Google search) has the binary of the game posted for free download.

Given a very very rough estimate of about 600 hours spent creating the game, $310 in net profit works out to a wage of about $0.50/hour.  Not exactly enough to quit the ol’ day job!  (Fortunately, I already have a day job which I love!)

I am, however, honestly totally fine with that performance. I made an intentional decision up front for my goal for the Vigil RPG project to be to "make the game I wanted to play" – with no design compromises being made for the sake of monetization.  So no IAPs, no ads, no other typical "freemium" features (or “anti-features,” as the case may be) such as premium currencies or stamina timers.

 

$0.99 Sale

Consistent with my initial goal for Vigil RPG of prioritizing fun over profits, as of today, for the first time ever, the App Store price for Vigil RPG is reduced to $0.99!  I’m hopeful that this will allow more people to enjoy the game – assuming there’s a segment of folks out there who are interested in iPhone RPGs, and are unwilling or unable to buy the game at the $2.99 price point, but will go ahead and pick it up for $0.99.

The main reason I didn't just cut the price all the way down to $0.00 (free) was that admittedly there's somewhat more cachet in being able to say "The game I made is for sale on the App Store!" than "I made a game and I'm giving it away on the App Store since no one was really buying it!" 

It would also be nice if Vigil RPG’s proceeds would at least cover the annual $99 that Apple requires to keep it listed on the App Store.  To that end, I might bump the price back to the original $2.99 at some point if sales at the $0.99 price point don’t generate much increased volume relative to the 1 sale/week or so of the $2.99 price.

 

“Buy It Now!”

Hopefully this detailed peek into one game’s iOS App Store performance was helpful, or at least mildly interesting!

If you’d like read more about the gameplay of Vigil RPG, you can do so on the Vigil RPG website.  Or, you can check out the full 5-to-10-hour adventure firsthand via Vigil RPG on the App Store if you’ve got an iOS device, and can scrape together enough loose change to join the exclusive club of premium iOS game owners!

You can also hit me up with any questions you’ve got on Twitter at @AggroMagnetGame, or below in the comments!

Wednesday, April 06, 2016

New PC 2016: Things were not cool for a while there

One of the things I did to verify that my newly-built home PC was working well was to download and run a CPU temperature monitoring program, and leave that open in the secondary monitor while running programs in the primary monitor.

Unfortunately, this pretty quickly turned up problems. The CPU, an Intel Core i7-4790K, would get dangerously hot when running certain games. The game Cities: Skylines exhibited the worst symptoms: After running the game for just a minute or two, although the game itself would run great, the CPU temperature (as reported by the temperature monitor program) would shoot up to nearly 100 degrees C!  That’s close to the point where the PC will shut itself off to avoid damage, and much hotter than I would expect.

I thought the problem might be due to my having done a poor job applying the thermal paste to my CPU and/or installing the stock heatsink incorrectly, so I removed the heatsink, carefully cleaned off the old thermal paste, applied new thermal paste, and reinstalled the heatsink.  After doing that, though, the CPU temperatures while playing Cities were still extremely hot.

At this point, on the advice of some of the friendly folks at the Gamers With Jobs community, I decided to throw some hardware at the problem, in the form of a US $29 Cooler Master Hyper 212 EVO heatsink! 

I’d previously never bothered with “premium” heatsinks, since I don’t overclock my systems (valuing rock-solid stability over an incremental speed boost).  In this case, though, it seemed like the best option to protect the $340 investment I’d made in my nice CPU.

I’m very happy to report that it worked perfectly!  With the 212 EVO installed (replacing the stock Intel heatsink, and with another fresh application of thermal paste), CPU temperatures while playing Cities: Skylines dropped from nearly 100 C down to the mid-40s C!

One caveat that I discovered with the 212 EVO though is that it fastens to the motherboard from both sides, effectively pressure-clamping itself down onto the CPU.  Therefore, I needed to unscrew my motherboard from the case to install the new heatsink.  If you’re doing a build that includes a premium heatsink, I suggest installing the heatsink and CPU onto the motherboard before screwing the motherboard into the case!

A final additional purchase I made was a cheap ($8) case exhaust fan, since the case I used didn’t come with one.  I didn’t want the air inside my case warming up over periods of long computer use. 

CPU cases evidently come with quite a few variants of screw mounting hole spacings.  The distance between screw holes is not, as it turns out, the size of fan that you should order!  I found the chart on this quietpc.com page very useful (and accurate!) in translating the space between mounting holes that I measured on the back of my case to the size of fan that I needed to order.