2019-11-26

What's wrong, Delta? Afraid of a little snow?

Last week, I got the news that my grandfather had died. The news was very bittersweet. I had talked to him a few weeks before, and I could tell he was just so tired of life. He had always been a very active person, but the last few years, his body just failed to keep up. Especially after my grandma died a little over a couple years ago, there just wasn't much for him to do or anyone to do it with. (Not that he was totally alone or that no one visited — he had 24/7 care, and family in the area that would check up on him often.) So, while I'm very sad to have him go, I'm happy he can be reunited with Grandma and other friends that have gone before him.

I'm also very fortunate that I was able to make it out there — and even more fortunate that I was able to make it home.

I have a cousin in the area who was very nice to allow me to come in and give me a place to stay. In talking with him as I made my travel plans, the most convenient flight out from Denver to San Francisco and back was with Delta. Despite having a layover in Salt Lake both ways, the times worked out decently well for the price.

The flight out was pretty uneventful, which in the context of travelling is a good thing. The flight back, however, is where things got interesting. My flight out was scheduled late in the day of the funeral, and we planned to go from the funeral to the airport for me to catch my flight.

Early in the morning, as we were getting ready to leave my cousin's house for the funeral, I got a text message from Delta indicating that there was a change to one of my flights. I checked using the Delta app (once again entering my confirmation number, since the app forgot it — by this time, I had it memorized, which would become very important). The flight from SFO to SLC was unchanged, but the connection from SLC to DEN had been delayed from that day to 6am the next morning, giving me an overnight layover in Salt Lake. Now, the convenient thing about the Delta app is, when you have a delay like this, it gives you a list of alternatives that you can re-book for no extra cost. Scrolling through the list, though, only showed me a list of flights that started the next day. I didn't want to impose on my cousin for another day (plus have to take another day off of work), so I kept scrolling until I finally saw an option that let me leave the same day. It started with the same flight from SFO to SLC, but the next flight was from SLC to Los Angeles, with about a 5-hour layover there until a 2:30am flight back to Denver. It wasn't great, but at least it got me home.

I clicked the button to book the flight. Then, I looked over my itinerary. SFO to SLC, SLC to LAX, and then LAX to DEN, departing at... 7pm the next day? My 5-hour layover in LAX had somehow turned into a 24-hour layover. And the option to reschedule had disappeared from the app.

With the app failing me, I decided to call the customer service number and talk to a human. They put me into a queue with a 20-minute wait, and the recording gave me the option to have them call me back instead of waiting on hold the whole time. I selected that, and we set off on the road for the funeral.

At this point, it's important to know that I have T-Mobile cell service. It's also important to know that, in the area where my cousin lives (and, as it happens, over most of the route between his place and where the funeral was scheduled), T-Mobile's coverage can charitably be described as "not great". Sure enough, about 25 minutes later, my phone alerted me to a voice mail message, which I played back and heard the automated attendant trying to connect me to an agent. They called back as promised, but I had no signal at the time.

So I called again, this time remaining on hold for the now-25-minute wait time, listening to really bad electronic hold music broken only by a recorded message telling me how I could make changes to my reservation using the Delta app. (Yeah, it was the app that got me into this mess in the first place, thanks.) After about 25 minutes, as promised, an agent picked up. I was able to give him my name and confirmation number, and I heard him comment on how strange my reservation looked now. "Let me see what I can do," I heard — right before we entered another T-Mobile dead zone and the call dropped.

This time, I borrowed my cousin's phone (connected to AT&T service, which had better coverage in that area). After another 25-minute wait (which included several more recorded messages telling me how great the Delta app was), I was able to talk to an agent, who noted that Denver was due for some bad weather, causing some flight complications. To his credit, he was able to find me a flight. It was on a New Zealand flight operated by United (shrug), with direct service from SFO to DEN. It was scheduled to leave San Francisco a little later (giving us more time to get there from the funeral), but arriving home at nearly the same time. Sounded like a win-win. After we got off the phone, I checked the Delta app, and it showed my new flight (though, since it was a United flight, without the ability to check in or make a seat selection or any of the great features provided by the "great" Delta app).

After the funeral, as we're heading to the airport, I decide once more to check the app. It still showed me booked on the New Zealand/United flight, with no indication that anything was amiss. For some reason, I decided to do a quick Bing search (hey, I get Rewards points for using Bing, don't judge) on the flight, and it displayed the status: Cancelled. My cousin asked if I wanted to just return to his house, where I could start making calls, but at that point, I figured we could just continue to the airport, and I could talk to the agents there in person to figure things out.

Once there, I went to the Delta desk. I kind of expected the answer that they wouldn't be able to help me after passing me over to United, and in that, I wasn't disappointed. Of course the Delta and United desks were on opposite ends of the terminal, so I had a bit of a walk over to United.

I got a little worried at the United desk, when they couldn't find my reservation in their system, on any flight. Delta's reservation number wasn't helpful, but the ticket number was — once I found it buried under several clicks in that "really great" Delta app. (There was an additional snag in that the computer wouldn't let the agent do anything with my ticket, but he got that straightened out talking to his tech support — something about the ticket using my full middle name where he tried searching by just my initial.) After a few minutes, though, he was able to put me on a United flight straight from SFO to DEN. He handed me the boarding pass, which showed a departure time of about 1:30pm, about three hours prior. But, not to worry; that was just this flight's original time; it had been delayed until 5:30, which gave me a fair amount of time to get through security and to the flight.

It turns out, this flight's plane had some mechanical issues, so they had replaced it with another plane from Chicago that unfortunately wasn't going to get into San Francisco until after 4. Well, unfortunate for the people who were originally booked on that flight, but fortunate for me, I suppose. I kind of felt bad as I sat in the waiting area, listening to people complain about being there for four hours, while I show up less than an hour prior.

The plane from Chicago ended up being a little delayed; and, after we boarded, they had an issue with the door seal that required a maintenance crew to give it a once-over and fill out some paperwork, further delaying take-off. Then, while I didn't have a window seat and couldn't confirm this, it sure felt like we taxied around to every runway before finally taking off at a little after 6:30pm.

But finally, 2 hours later, despite a snowstorm that was just kicking into high gear, we touched down in Denver, and I was as good as home — at just about the same time I was originally scheduled on my first Delta flight from Salt Lake. As if nothing happened.

And good thing, too; the storm dropped snow all through the night, with nearly 500 flights cancelled at Denver the next day. If I hadn't made it home that night, it could've easily taken me a couple days to get home, right in the middle of the Thanksgiving travel rush.

2019-10-04

Entity Framework 6, SQL, and nullable strings

I ran into an issue that appears to be caused by Microsoft attempting to protect me from myself. Although, truth be told, it wouldn't have been an issue if things were a little better designed.

Imagine, if you will, a SQL Server database with a table of transactions. One of the fields on this table is a CorrelationId. It's a text field that is populated by a different system to tie transactions together (for example, two sides of a transfer from one customer to another). This field always gets populated on new transactions; the uncorrelated ones will just be the only one with a given CorrelationId. However, this system is not new; it was converted to replace an older system that did not have a defined CorrelationId. So, although the five million or so transactions created by this system have a CorrelationId, there are 12 million "legacy" records that have a CorrelationId of NULL.

So, say, for a given transaction, you want to find all correlated transactions. In SQL Server, you might use a simple query like this:

SELECT *
FROM dbo.TransactionTable
WHERE CorrelationId =
(SELECT CorrelationId FROM dbo.TransactionTable WHERE Id = @TransactionId)

And this would work, for the most part (except for legacy records, since SQL will fail to match on the NULL value — but we can ignore this for now). If you took this query into SQL Management Studio and looked at the execution plan, you'd see a nice thin line from the index seek on the CorrelationId, showing that it found and processed a tiny number of matching records, resulting in a very quick response.

However, if you were trying to do this programmatically from a C# application using Entity Framework 6, you might write some code like:

var query = from txn in transactionTable.Entities
where txn.Id == transactionId
join txn2 in transactionTable.Entities on txn.CorrelationId equals txn2.CorrelationId
select txn2;

The problem is, in C# code, null values are equal to another; while in SQL, "null" is considered "unknown", and doesn't equal itself. (The theory is, you can't know if one "null", or unknown value, equals another "null"; so equality tests between "null" and "null" are false.) Instead of leaving it up to the programmer to explicitly code for this condition, Entity Framework "helpfully" writes the join clause that it gives to SQL server in this manner:

JOIN [dbo].[TransactionTable] AS [Extent2] ON (
([Extent1].[CorrelationId] = [Extent2].[CorrelationId])
OR
(([Extent1].[CorrelationId] IS NULL) AND ([Extent2].[CorrelationId] IS NULL))
)

The extra check for IS NULL on both sides has two unfortunate side effects in this case:

  1. If the transaction is one of the legacy records, it will return a positive match on all 12 million other legacy records with a null CorrelationId.
  2. If the transaction has a CorrelationId, because of the IS NULL, SQL Server will investigate the 12 million null values in the CorrelationId index, resulting in a big fat line from the index seek in the execution plan, and a return time of a couple seconds or more.

The really annoying part is that there doesn't appear to be a way to stop this. Even if you explicitly add a check for a not-equal-to-null on your target table, Entity Framework still wraps the equality test with checks for IS NULL. The result is almost comical. For instance, adding txn2.CorrelationId != null either in the join statement or as a where clause, results in this (with contradictory statements highlighted):

[Extent2].[CorrelationId] IS NOT NULL
AND (
([Extent1].[CorrelationId] = [Extent2].[CorrelationId])
OR
(([Extent1].[CorrelationId] IS NULL) AND ([Extent2].[CorrelationId] IS NULL))
)

Even trying to break up the work into two statements didn't help. This code:

var corrId = from txn in transactionTable.Entities where txn.Id == transactionId select txn.CorrelationId;
var txns = from txn in transactionTable.Entities where txn.CorrelationId == corrId select txn;

Resulted in this SQL:

WHERE ([Extent1].[CorrelationId] = @p__linq__0)
OR
(([Extent1].[CorrelationId] IS NULL) AND (@p__linq__0 IS NULL))

Granted, this is a really bad situation to be in to begin with. Indexes on text fields tend to perform poorly, and having such a huge number of null values in the index is likewise unhelpful. A better design would be to rip the text field off into another table, or somehow otherwise convert it into an integer that would be easier to index (something we've had to do in other tables on this very same project, where we've had more control of the data).

I'm willing to bet that Microsoft's translation goes completely unnoticed in over 99% of the cases where it occurs. And, if I had the time to make a design change (with all of the necessary changes to all points that hit this table, some of which I don't have direct control over), it could have been resolved without fighting Entity Framework. Even just populating all of the legacy transactions' CorrelationId with random, unique garbage would've solved the problem (though with a lot of wasted storage space that would've made the infrastructure team cry).

In the end, it was solved by creating stored procedures in the datbase to do correlated transaction lookups (where the behavior could be controlled and expected), and having C# code exectue those directly (bypassing EF6) to get transaction IDs. Standard Linq queries would then use those IDs, instead of trying to search the CorrelationId.

This whole exercise was prompted by a script that I had to run to get a bunch of data from a decent number of transactions. It took nearly eleven hours to complete, finishing close to 1am after I started it. If I had time to go through this debugging and implement the fix, it turns out I could've gotten it done in about a third of the time.