linq the wrong way
Scene: It's late. You're supposed to go on vacation in a couple of days, but you're not quite done yet. Bleary eyed and shaking with caffeine you get to the last bit of code you need to refactor. Whew, this will be a cinch! "It's just some stupid LINQ query," you say to yourself as you crack your knuckles and tear off down Fuckup Lane.
Wait a minute, you think, just before you commit your change into the verdant green fields of the holy land of Git. You remember the table column is DateTime?, but your variable in the where clause is DateTime. Whew, almost made mistake there. Patting yourself on the back, you quickly wrap the table column in the Where clause in a CDate, compile, test, commit, and deploy for the tester.
The next morning testing declare your code pure and good. Praise be to LINQ and all that is holy and good in the world! The next night you deploy to production and the following morning one thousand users start triggering that LINQ query 100 times a minute against the table with 100 million rows. Goodbye vacation!
For those not in the know, LINQ stands for Language Integrated Query. Microsoft (praise Gates the most Holy of holies) to allow queries to be be written in .Net Languages against things such as databases, XML, and in memory objects to simplify data access with consistent syntax across different data types. This simplicity provides a low barrier to entry, so low in fact, that you don't even have to know what you're doing. This comes in handy when you're querying a database. In no time at all you'll be scanning full tables for absolutely no reason and not even know it!
Personally, I blame my misuse of LINQ on VB. My job at my company morphed over last year from building a new C# web app, to also maintaining the legacy WPF VB system that the new website enhanced. Switching between languages is easy for a lot of developers, but not for me. Switching between JavaScript and HTML used to raise my internal temperature to a swelter and swell my body with hives. I got over that, but C# to VB has been nerve wracking. Add to that a monstrous over a decade old system, a business process I'm just beginning to understand in all its complexity, and two concurrent projects that pull at the inner intestines of this clunky ugly leviathan and I won't even go on about the turn around time they wanted. Of course, I was oblivious to any of these considerations and did what any developer worth their salt and convinced they're going to get fired or laid off at any time if they didn't try and please everyone who dropped an email in their inbox and replied with "Cool. Got it."
So finally to the technical part. I'll make this as short as possible.
DON'T DO THIS!
context.FuckingBigTableMillionsBaby.Where(Function(x) CDate(x.StartDate) = ParamDate1 AndAlso x.PersonID = ParamPerson).ToList()
In case you missed it:
context.FuckingBigTableMillionsBaby.Where(Function(x) CDate(x.StartDate) = ParamDate1 AndAlso x.PersonID = ParamPerson).ToList()
You do this, then forget that nice index the DBA added to speed things up and protect their precious table from barbarians like you. CDate just knocked that index out and now is going to potentially molest every StartDate in every row in this massive huge bloated table.
The real joy in making a mistake like this is that you might not be quick to find it. While the queries are running slower, they might still be too small to catch the eye of the DBA, and larger queries unrelated to your work are getting hobbled enough to cause locks and timeouts for other operations. Even the third set of eyes examine your code with a fine tooth comb might miss it. CDate is everywhere and its ilk are everywhere in the code.
Now, this isn't the only way you go cause this issue. Just try some other function on a table column. I'll wait...actually I won't. I'm not ever here. I'm at work. Waiting. Waiting for the Grand Poobah of developers to gather the rest of the team. They'll sneak up behind me while I look up the Difference Between And and AndAlso for the tenth time and beat me with their Hate Sticks.