-- select [dbo].[FormatBytes]( 13241322.567567, 'bytes' ); -- select [dbo].[FormatBytes]( 132413225.567567, 'bytes' ); -- select [dbo].[FormatBytes]( 1324132255.567567, 'bytes' ); -- select [dbo].[FormatBytes]( 13241322551.567567, 'bytes' ); -- select [dbo].[FormatBytes]( 13241322.567567, 'mb' ); -- select [dbo].[FormatBytes]( 132413225.567567, 'gb' ); -- select [dbo].[FormatBytes]( 1324132255.567567, 'tb' ); -- select [dbo].[FormatBytes]( 13241322551.567567, 'zb' ); create or alter function [dbo].[FormatBytes] ( @Number decimal(38, 7), @InputUOM nvarchar(15) = N'bytes' ) returns nvarchar(20) with SCHEMABINDING as begin declare @Multiplier decimal(38,2) = case @InputUOM collate SQL_Latin1_General_CP1_CI_AI when N'b' then 1 when N'byte' then 1 when N'bytes' then 1 when N'kb' then 1024 when N'kilobyte' then 1024 when N'kilobytes' then 1024 when N'mb' then 1048576 when N'megabyte' then 1048576 when N'megabytes' then 1048576 when N'gb' then 1073741824 when N'gigabyte' then 1073741824 when N'gigabytes' then 1073741824 when N'tb' then 1099511627776 when N'terabyte' then 1099511627776 when N'terabytes' then 1099511627776 when N'pb' then 1125899906842624 when N'petabyte'then 1125899906842624 when N'petabytes' then 1125899906842624 when N'eb' then 1152921504606846976 when N'exabyte' then 1152921504606846976 when N'exabytes' then 1152921504606846976 when N'zb' then 1180591620717411303424 when N'zettabyte'then 1180591620717411303424 when N'zettabytes' then 1180591620717411303424 when N'yb' then 1208925819614629174706176 when N'yottabyte' then 1208925819614629174706176 when N'yottabytes' then 1208925819614629174706176 when N'bb' then 1237940039285380274899124224 when N'brontobyte' then 1237940039285380274899124224 when N'brontobytes' then 1237940039285380274899124224 when N'geopbyte' then 1267650600228229401496703205376 when N'geopbytes' then 1267650600228229401496703205376 end; declare @Bytes decimal(38,2) = @Number * @Multiplier; declare @prefix money = case when abs(@Bytes) < 1024 then @Bytes --bytes when abs(@Bytes) < 1048576 then (@Bytes / 1024) --kb when abs(@Bytes) < 1073741824 then (@Bytes / 1048576) --mb when abs(@Bytes) < 1099511627776 then (@Bytes / 1073741824) --gb when abs(@Bytes) < 1125899906842624 then (@Bytes / 1099511627776) --tb when abs(@Bytes) < 1152921504606846976 then (@Bytes / 1125899906842624) --pb when abs(@Bytes) < 1180591620717411303424 then (@Bytes / 1152921504606846976) --eb when abs(@Bytes) < 1208925819614629174706176 then (@Bytes / 1180591620717411303424) --zb when abs(@Bytes) < 1237940039285380274899124224 then (@Bytes / 1208925819614629174706176) --yb when abs(@Bytes) < 1267650600228229401496703205376 then (@Bytes / 1237940039285380274899124224) --bb else (@Bytes / 1267650600228229401496703205376) --geopbytes end; declare @suffix nvarchar(15) = case when abs(@Bytes) < 1024 then N' Bytes' when abs(@Bytes) < 1048576 then N' KB' when abs(@Bytes) < 1073741824 then N' MB' when abs(@Bytes) < 1099511627776 then N' GB' when abs(@Bytes) < 1125899906842624 then N' TB' when abs(@Bytes) < 1152921504606846976 then N' PB' when abs(@Bytes) < 1180591620717411303424 then N' EB' when abs(@Bytes) < 1208925819614629174706176 then N' ZB' when abs(@Bytes) < 1237940039285380274899124224 then N' YB' when abs(@Bytes) < 1267650600228229401496703205376 then N' BB' else N' geopbytes' end; return CAST(@prefix as nvarchar(42)) + @suffix; end;
USE [Tools] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- select dbo.XrdDay(4,'thursday', 'november', 2018) -- Thanksgiving -- select dbo.XrdDay(4,'thursday', 'november', 2019) -- Thanksgiving -- select dbo.XrdDay(4,'thursday', 'november', 2020) -- Thanksgiving -- Must have the day of week and the name of month spelled correctly. alter function [dbo].[XrdDay]( @xrd int --3rd, 4th, 5th, etc. ,@dayname varchar(8) --'saturday' ,@month varchar(10) --'february' ,@year smallint --year ) returns date as begin -- declare @xrd int = 3; declare @dayname varchar(8) = 'monday'; declare @month varchar(10) = 'september'; declare @year smallint = 2019; declare @Result date= @month + ' ' + convert(varchar(64),(@xrd*7) - 6) + ',' + convert(varchar(64), @year); -- get Sunday. --select @Result, datename(weekday, @Result); --I'm fairly sure this is inefficient, but I can't seem to recall of another way around it. --to make it better, we need to calc @sunday+int==dayname in one go, not this while loop. while datename(weekday, @Result) collate SQL_Latin1_General_CP1_CI_AS != @dayname set @Result = dateadd( day, 1, @Result ); --select @Result, datename(weekday, @Result); return @Result; end;
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 8gb 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 wrong).
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 duplicated 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. (man 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 ‘venting’.
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 slowing down the production database servers.