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.
Photobucket - Video and Image Hosting

[Large Picture]

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

Comments

Jane Smith said…
i want to become a programmer hahahaha!
maks said…
hehehe sure you can. I can help you with that.
Anonymous said…
hey maks, as i've read this blog i can't help but noticed the way you approach a very simple conversion in your system, your coding is quiet complicated or too long. why not use simple SQL "UPDATE" command just to solve those problems.
e.g. "UPDATE TableName set Status=1 where Status='A' "

this approach is much faster compared to your codes. Dats ol 4 now.

-CyberGume
maks said…
hi cybergume i don't think that's as simple as you might think. It goes something like this:

You have to convert .dbf files from foxpro then transform it to sql server data files then during the process you have to identify first if the customer current status is active or inactive then convert it to 1 or 2.

Your solution is pretty cool but once you reach 1 million(with multiple links inside on it such us design view and triggers) data try executing that code to sql analyzer and you will be prompted with timeout expired even thoug have already reach the maximum 2gb of memory.

If you really want to understand what im working on I will give you some overview of it.

assume that you have a table name called newapplication wherein you have to get 3 fields accountno, name and status. then under status you need to identify if its active or inactive and transform those string into 1 or 2. Once you have transform it you need to Insert it on a different table called MatchMeter.

How will you do it? love to hear your thoughts on it.
Anonymous said…
This is because it's hard to improve an existing house compared to building a new house on a vacant lot. Hmmm... it's been two years already, right? By this time I was assuming you have fully converted everything from the old system to a new purely SQL powered one.

Visual FoxPro is greatest when it is used locally to query data taken from an SQL back end.

It's amazing too what user rants and whines can do to make us improve our codes, right?

30 minutes is still too long. I'm sure it is a great improvement from 3 hours... but 30 minutes... is still... s... l... o... w...

I'm sure you can improve that more.

Hmmm... why use one processor when you have five or more processors lying in your place.

Chop and distribute.

In theory if you can distribute the load to five computers, 30 minutes will be down to 6 minutes.

Re-assembling them would not be that long I'm sure.

- Guess WHO.
maks said…
hahaha I know who you are...verminator!I would love to do that but then I again I have to review my calculus first to make it happen. :D

Popular posts from this blog

John Gokongwei Speech before Ateneo 2004 Graduates

SM Mall of Asia

GWT+MySQL Best Practice