Case Study

Case Study: When to realise mismanagement?

From last month or maybe two months, I am helping one of my client’s company. They are sort of done with outsourced developer because (well they didn’t meet me before) and get bad experience. They are using my client, who is a hosting provider, for providing server services. They hire an in house apprentice to maintain their 10-12 yr old system which use Classic ASP to early version of .NET Framework based console app for automation.

So, now they need some help as their in house programmer is not very good and they reach me indirectly. All, is good so far as I am not charging them much and it is really just help. But I feel bad for them, because they are so mismanaged that they don’t even realize that they are missing a proper management. They get in this frame where they think developer is done in this bad environment like that. They have simple process, Client purchase their hardware, there Point of Sale machine generate a TEXT DAT file that they use to print “pre printed” invoice, so their old system only has to write text where it is required. Since the print invoice system is old and they don’t want to change, so our only option is to read those “DAT” file and read it into database to create PDF or do some reporting.

For this they initially have a website which does some online selling and reporting for their admin. It was in classic ASP. So, when a need of PDF arise they get a developer to write PDF, but since ASP is not handy and ASP.NET was in market this developer write that PDF using single page script in ASP.NET for PDF. It works fine. But now they need to bulk email to different vendors and customer at end of day their invoice, so they get couple of Console EXE that they run in background and get things done. It looks good maybe at that time. And it keep working if they get a proper team to handle it.

But here comes the mismanagement part. First they do not have the source code of EXE, until they need to change the pre-printed Invoice design, that result in change in PDF. They realise they never took the source code from old developer, or they don’t bother to keep it safe. Old developer who was now fired don’t bother to keep source code, because he is not responsible to keep it. He send a copy to client and is done when he was fired.

So, my first job to help them was to either recreate a small PDF code as their in-house developer cannot do that. (I still wonder why they call him developer). But I give them option to recreate or try to de-compile code, which works and give full source code back in shorter time. So they become happy to get old code back as it is easier. I however suggest them to not invest much on old system and get new system. They can elimiate to many moving parts from new system and get much stable version. But they are not ready, as for them it is always “one last small change, and we are done”. But are they really smart? I mean it is almost 2 months and their PDF changes are not yet done. They pay 2 month salary to their in house developer. Not to mention if they have to pay me for all help? They are so mismanaged in their approach, that they don’t realise they are missing a good management in IT department. They are lucky to have stable business that is paying for unstable IT needs. But what business they are losing for it, and wasting their resource they have no idea.

You can be busy, have a working solution. But that doesn’t mean it is always the best. Yes they are constraints, money, time, resources. But you have to make decision until when you can drag it. If they were using a simple Github/bitbucket account they can avoid losing files, source code in emails. If they serialise process they can avoid 5-6 console EXE to make similar yet different PDF, they can combine them in one EXE to reduce cost, and save time. Wish I can help them more than I am doing now. Hope their in-house developer learn fast, but he is not. They don’t realise it yet. Hope one day they will.


SQLite Bulk Insert on ADO.NET

Today, I was trying to find an alternative on reading a Binary data file to use information in my software. For this I choose to use SQlite the embedded database. It has quite a few steps before I achieve a acceptable speed with database and here is list of those that need to be taken care for BULK INSERT in SQLite database.

1. Try to simplify your for or while loop as much as you can. For example do not construct the Query within Loop. Give String Query before the start of loop.

2. Loop should be enclosed within the Transaction. Somebody techincally explain me that with Tranasction the fsync function which is responsible of ensuring the  data safely written, is called once only. If you don’t use Transaction fsync is called on every insert query and proves to be a bottleneck in performance.

3. Index your Table if you have to “update” the result within the Loop. In my case I have to first find one value in my Binary file, write it down to Database, then in one of condition to read of file, I might get other value to UPDATE my existing record. So, within my loop I have insert and update commands. And indexing makes it easier to find record when I got more than 50-60K record inserted. It make the speed upgrade of 1500% in my case with just 0.1M records. I have to read 1.5M records.

SQLite is really fun. It works charm with proper techniques used and store more than 1M record for me to work on.