Month: February 2015

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.”