Script to create unsigned data types in SQL Server.
I came across this quick example that shows how easily the tiniest little mistake could end up deleting rows from your table. The solution to this bug? Always name & reference your tables, CTE, and subqueries! And now you can play: Spot the Bug™!
So. Our team worked to improve the performance of the company’s two 16-year old desktop C# applications.. I focused my work on the applications and on the database procedures.
I personally made the main application load in 1 second on an Intel i5 with 4GB of ram running around 3ghz.. when it used to take ~10 seconds to load on the same types of computers. Literally 10 times faster!
I also cut the codebase literally in half, coalescing logic that was duplicated across multiple methods. Also fixed all known errors (and found some traps that would have been bad for the company.. such as calculating taxes and shipping incorrectly).
I changed the main internal logic to use awaits and async where appropriate. I also added error trapping (catching & logging logic/network/user exceptions) to almost the entire codebase.
I updated the .NET framework to the latest version.
I found and fixed all possible SQL injections.. the company had had no idea that the injections could even have happened. I made the database calls clean, fast, and error free. Cached static (information that rarely changed) responses so the geographically remote branches only had to pull information once from the main database. And there used to be methods that made the same database calls information repeatedly.. sometimes a single keypress would fire off a chain of duplicate database calls.
I created unit tests.. the CIO of the company had no idea what a “unit test” was.. or how to create & use LINQ in C#. I fixed most of the Array(s) of Objects that they stuffed Controls into because they wanted a “dynamic” interface. (It was horrible!)
The rest of the team and I (mostly 40% me and 60% them) also reworked the database tables to use the proper types. For example: changing bigint or int down to tinyint where appropriate. And tinyint to ints. Datetimes to Date where the “time” part was not needed.
We normalized almost all of the tables.. removed any unused or duplicate indexes.. the applications performances’ became very fast and responsive.
I spent the entire year fixing this mess they had created with multiple so-called ‘programmers‘ over the last 16 years. I worked nearly constantly.. never spent more than a minute or two away from coding. Excluding meetings, sick days, bathroom breaks, and lunch of course. If I was at my desk, then I was working.
I also took on the task with half the salary it should have been because I liked the company. Big Mistake™ on my part.. I blindly ignored the fact that companies can and will replace you with a cheaper programmer on a whim.
And can you guess what they did on the day I fixed the last known bug? They let me “go“. As in, “See yah, now we don’t need yah!”. Didn’t even get a thank you “for your hard work and saving the company thousands of dollars!” 🗯
I was like “Really? How rude..!“
But I also figured, “Okay.. they can pay someone else to keep this application maintained and updated by the newbie programmer they hired next.. the bosses’s son.”
Professional nepotism.. and that’s what is annoying.
Oh well.. I’ve updated my résumé and moved on from that nightmare of a mess we had fixed.
Thanks for reading my vent.
A data warehouse is the main repository of a company’s historical data. Data warehouses can contain a variety of data that presents a coherent picture of the business’s conditions at a point in time. The main factor of using a data warehouse is that analysts can perform complex queries (data mining) on the information without… Continue reading SQL Server: What is Data Warehousing?