When I use a VPN

vpn-animationOver the weekend I signed up for a lifetime unlimited VPN plan from VPN Unlimited. Have not tried them before, but at US$39 for a lifetime plan, it does seem to be too good a deal to pass, and I am assuming that TNW should be reputable enough to do proper screening of their deals before offering them to followers.

My most pertinent use case for VPNs is when I am using open wireless networks either in SG or overseas. You can never know whether it is a rogue wireless hotspot that was set up to steal your data or if there are other unruly elements together with you on a “protected” network who are trying to sniff and decipher your network traffic. A particular type of wireless network vulnerability was exposed a few years back with the “Firesheep” Firefox extension.

Another benefit of using VPNs from a VPN provider who has international servers is that you will be able to spoof your location so that you can access localised content (e.g. Hulu, BBC, some Youtube channels, etc.) that will otherwise be blocked from international users. For some — like the people behind the Great Firewall of China — this feature is particularly relevant as they are not only able to spoof another location, they can also bypass the Great Firewall since the VPN tunnel is encrypted and will theoretically block prying eyes.

Me and my Virtual Machines

vbox_logo2_gradientI use Virtual Machines (VM) at home. If you think that VMs only have their place in corporations, then you may want to think twice and consider deploying them for use within your home. Even if you do not have any servers, it still makes sense to deploy them on your notebooks, desktops, etc.

The reason why I use VMs is because they provide a contained environment which can be ported to different machines if I should choose to do so. I run Ubuntu Linux on my Oracle Virtualbox, and it is simply a cinch for me to switch notebooks when I upgrade. Once you perform a VM migration and see your desktop and applications being migrated across so easily, you will probably think twice about using your native desktop again!

Other than migration, full system backup is also no longer a chore. Now all I need to do in order to have a full system is to create a snapshot, and when I feel like it, copy the VM and its associated VDIs out to my external HDD for safekeeping. With large HDDs being so cheap now, it is probably more cost effective for you to just backup the entire VM rather than debate about what to backup and what to leave out.

How to avoid your DB index

Do note the headline is written in jest! Of course if you create a database index, you should try to make use of it in order to allow your queries to return the results faster. However, if you write your query in such a manner as below:

select
a.field1, a.field2, b.field3   
from dbo.table1 a   
inner join table2 b 
on UPPER(a.field2) = UPPER(b.[field4])   
inner join table3 c   
on c.field1 = a.field1
Unless there is a computed column present in the DB table, this script will be performing the dreaded full table scan even if table indices were defined for table1.field2 and table2.field4! This comes about because of the use of the UPPER keyword which transforms the string into something that cannot be matched in the index.

Apparently in Oracle, it is still possible to create a function-based index to meet this shortfall, but in SQL Server you will need to specially create a computed column, then create an index specifically for that column in order to enjoy the benefits of an index!

The slow death of shell scripts

Unix_more_outputShell scripts are rarely taught nowadays, and to really have a good handle on it, you need to be using it on a regular basis and finding out how it best works. I can still remember the early days in my career when I had to write scripts in Unix and get a handle on the various nuances of csh, bash, ksh, etc. I think I gained my foundation in shell scripts when I was messing around with Windows batch files in my primary school days and it was fascinating that interactive batch files could actually be written to accept input from the user and trigger different behaviours.

Fast forward to the present day where the Un*x terminal or Windows command prompt is usually not covered in depth. For those who know the power of shell scripting, it can be quite frustrating to know how some things can be done easily through shell scripts and regular expressions but some developers may not be familiar and thus have to resort to writing a full blown Java/.NET program or a DTSX just to handle some simple jobs.

Software developers should view themselves as “craftsmen” where they develop a good understanding of the tools at their disposal and know which tools are the best to use under given circumstances. However this will require passion and a strong interest in honing the necessary skills and knowledge, not just an attitude of “bringing home the bacon.”

Is there a case against the use of CASE?

Consider an example statement:

 

select field1, field2, field3, rt = case cond_field1  when 'B' then 'BUY'  when 'S' then 'SELL' end, field4
from some_table

 

This uses a conditional case keyword within the SQL statement, which has the potential to be evil.

It is possible that the statement be expanded and rewritten as such:

 

select field1, field2, field3, 'BUY', field4
from some_table
where cond_field1='B'
UNION
select field1, field2, field3, 'SELL', field4
from some_table
where cond_field1='S'

 

This removes the conditional case keyword and also has the additional benefit a performance improvement should cond_field1 also be part of an index! However, I admit that the second approach does become a bit more unwiedy when there are multiple case conditions within the original statement. The second approach can also be more difficult to maintain since changes to the query have to be done in multiple places (e.g. if a new field5 is to be retrieved as well, the statement has to be updated in 2 places, otherwise the UNION will fail)

In short, the use of CASE can be justified on a case-by-case basis. (smile)

Simpler Operations

Extracting this code I came across (field names have been redacted):

(CASE WHEN (a.Field1 - b.Field2) > 0 THEN a.Field1 - b.Field2 ELSE 0 END)

Logically not wrong, but the number of mathematical operations can be cut further to increase processing speed.

Zoom in here

(a.Field1 - b.Field2) > 0

There is an arithmetic operation and a comparison operation here. This can be reduced to a single comparison operation with no loss in logic. The arithmetic operation is only executed when the expression is true, otherwise the literal 0 is returned.

(CASE WHEN (a.Field1 > b.Field2) THEN a.Field1 - b.Field2 ELSE 0 END)

Of course I may be wrong as I have not done any benchmarking. (smile)

Sharing my technical articles

I have finally managed to get enough PDUs to renew my PMP status! This means that I can probably write on topics other then project management to vary things a little here.

In my coming posts, I will be sharing some technical articles that I wrote earlier (meaning about a year or two back) but have never published to this site. Although IT moves at a breakneck pace, good principles and concepts will still apply regardless. Or so I hope!