Thursday, March 27, 2008

Think like a hitman

Currently I'm working in Cyprus now and the weather is cold compared to Malaysia.

After working for a day or two, a client reported that they have a trace problem. The application does not work correctly due to the trace file is pointing to the wrong directory and the database is giving back errors.

So what is traces? What's the good and bad about it?

Traces is something like spy that monitor every program execution and put every step into a log file or report. A simple example is the windows xp event viewer where you can track an error event. In VB, we have debug.print. In Informix, we have set debug file and trace on.

The good about traces is that it helps programmer to track the error cause. It can show what went wrong, which module produces that error, which line of code (possibly) that causes that error and even trace the execution time for benchmarking.

The bad about it - is performance. Although it's not obvious in user environment but it really drag down the performance for server, which have a lot of transactions and running processes, due to the file writing process .

Further more is what struck me today with this trace issue: previous programmer let the traces all over the place and they all pointing to different directory. To help the client to solve this issue in order for my project to proceed, I have to track down each of the procedures (and sub procedure) to make sure all of them are trace clear. So another bad point for traces.

In short, traces are for hitman, who always cover their traces in their work. For programmers, better think like a hitman before using them.

Saturday, March 15, 2008

Stored procedure common mistake

Hey, I like mistake. Here I would like to point out the mistake that I found in my daily work

NULL value
The number one enemy for SQL programmer. In one scenario, I have to debug a stored procedure where it process a table by loading all the column data into the defined variables. After some processes, it joins the variables into a long string in the end. Unfortunately, most of the columns are allow NULL attribute and all the assigned variables do not have NULL check. When one of the variables is NULL, the whole string will return NULL, which causes the program to stop. It seems like the original programmer doesn't care about the potential NULL or maybe it just too much work to have NULL check on every column. I admit that even me wouldn't like to spend my time by coding NULL check for every columns.

In general programming world, a variable is either yes or no, true or false, have and don't have. But NULL does not belong either of this world; it just mean unknown. Sometime, I feel it's like a black hole, where it suck up everything that touches it.

Although NULL is bad for programmer, I believe its basic advantages are saving disk space and provide faster searches due less data are stored. People may argue that in todays world, the cost of hard disk is lower and the speed is much faster compared to old age. This NULL issue can be fixed by setting default value in each column in tables. But I would rather see the database system would automatically assign a default blank value to variable instead of NULL. Having this rule means that the variable cannot contain NULL since it doesn't hold any valuable information or mean anything. Eventually there are no black whole, less programming error and it does not eat up disk space as well.

Sharing temp table
Passing a collection of values from one stored procedure to another proves to be more challenging than in general programming. So far, I haven't seen any stored procedure that is able to use a collection/array as parameter in my work. So how do we pass a specific collection of data to another stored procedure for process? In one of the convention way is by using temp table. When a temp table created in a session, every executing stored procedure in that session is able to look into that temp table for data. This means that temp table is like public table, except that it will be dropped once the session is end. The draw back of using temp table is that: if you need to change the temp table column attribute such as char(4) to char(8), every variable that hold that column's data in every stored procedure needs to be changed, which caused a lot of effort in maintenance.

The advantages of having sharing temp table is better performance and simpler coding. But once it is misused, you will need a lot of caffeine and patient to fix it when the system becoming more complex.

How to share data between stored procedures