Boosting my code

Codes, codes, codes I eat, breath, and live with codes from the moment I wake-up early in the morning until the day I drop myself into bed. Whenever I think I already mastered everything it never fails to amaze me when I instantly discover something to enhance some parts of my module inside my system.

Just when I thought everything was fine and going smoothly according to plan, with just one comment from a single user everything changed just like when you were hit by a bold of lightning.

When I showed to Karen our billing staff the process of conversion from their rover database to SQL Server she simply said. “Bat ang bagal?” and I said, “It only took 3hrs, was that long enough? Come on girl your kidding me right?” And guess her answer was? Absolutely “YES.” with a bold caps lock enabled.

What choice do I have? Developers are born for this kind of usual reaction. I’ve got to satisfy these users.

The next three days of my life was devoted on removing unnecessary codes that stores data to my database server and allow the system to focus on what really matters the most. Just convert it.

I have managed to cut the processing time from three hours to an hour. Then made some tweaks and changed it to 55 minutes. Still it was not enough.

Just when I thought everything seems so helpless a light bulb puff in an instant at the top of my head.

And it goes something like this:

Assuming you had a set of data that look something like this:

Idno--Name----Status
----- -------- ------------
1------Peter-----Active
2------James---Inactive
3------John-----Active

Your task is to get their individual status and convert it to 1 if its active and 2 if its inactive. If you are a Visual Basic programmer who is stuck due to the bigger and complex world of your system then perhaps your code will look something like this at first.

Select Case Status
Case "Active"
Status = 1
Case "Inactive"
Status = 2

End Select

Then you realized you need to boost the process of your module by allowing your compiler to think less and respond as fast as it can. Minimizing the search target to a single character helps improve quite a little bit.

Select Case Status
Case "A"
Status = 1
Case "I"
Status = 2

End Select

The result was 1% improvement, which by default seems to be irrelevant to practical users. The only way for me to see a drastic change in performance is to morph my code into a one liner code. Which leads to one question how will I do it? Answer: convert it into a formula.

After careful analysis I have understood that there are only two common letters in the field of status A and I. Which means A for active and I for inactive. In order for me to transform it to mathematical formula I have to morph it into numbers thus, I found Unicode function to do the job in transforming letters to numbers.

Since I want my formula in a server side type I decided to use user define function in SQL Server to do the job. Here is the function.

CREATE FUNCTION StatusFunction (@status numeric)
RETURNS numeric AS
BEGIN
return((((@Status - 65) * 2) / 16) + 1)
END

You might wonder why 65? 65 is the numeric equivalent of “A” and 73 is the numeric equivalent of “I” once you execute the Unicode function.

Then I include another improvement to my code but this time it was quite hard to transform it to a one liner mathematical formula code, as an alternative I just use a user define function and connect it directly to my views. Here is the function.

CREATE FUNCTION ConnectStatusFunction (@ConnectStatus numeric)
RETURNS numeric AS
BEGIN
if (@connectstatus=1) or (@connectstatus=6)
begin
set @connectStatus = 1
end
else if (@connectstatus=2) or (@connectstatus=7)
begin
set @connectStatus = 3
end
else if (@connectstatus=3) or (@connectstatus=4) or (@connectstatus=5)
begin
set @connectStatus = 2
end
else if (@connectstatus=8)
begin
set @connectStatus = 4
end
return(@connectStatus)
END

To use it inside the views here is the image.


[Large Picture]

The result was pretty cool from 55 minutes to 30 minutes.