Well, most of program use database for their web application. Eventually all web application does have database. But how many of those web application really works good. Well quite few. I am in web programming for more than 7 years now and during these years I have seen lot of developers building website using PHP or ASP.NET or ASP or PERL using SQL SERVER, MYSQL, Oracle or other database, but I always have a sense of making best application than them. But today I when I look back I realise that I was bad at writing code and making application in my initial years. Not that I don’t want to write a good code, but the fact my approach was not right. Some of the important thing that I learn over the year based on my experience are listed below. However before that I want to tell that not just me but all of us have read them in books but most of us might not have used it thinking we don’t need it as things works otherwise as well. So major stuff are:
1. Fast Database
2. Object oriented Code
3. Serialize flow of application.
In this post I try to mention Only Fast database. Well with Fast database I do not mean to pick the Fastest database on planet through bookmark testing, nor does I mean to use heavy hardware to make database run faster. These two things can really make your database appears to be faster but not really fast. I often ask a question in forum that I have few million records to parse, and can the DB engine handle it. I always get one answer if a database cannot handle few million record why we really need it.
This was very true statement, but do you really facing problem when you have only half million record and your site start taking 20-30 second to access database ? If so, then welcome to my past world. Well don’t worry you don’t need new hardware, you need new approach of programming your software and most importantly you need better database structure.
Starting with most commonly used stuff and I guess most importantly forgotten stuff.. Indexes. Yes, this is most common solution to make your site fast. Okay, so you already have define primary key in your table and it auto create index for it, but still not good enough. Well you need to index columns that you have used in your search and or in Join. Making index of one column does help, but making index of combination of 2 columns make it much better in queries that use the search on those two fields. For example, if you are searching Employee Last Name and their City from Employee table, having index on LastName and City seperately produce result slower than having index on Lastname and city name.
Now moving to next part of it. I have a sql query that has Client Code and User code in seperate column and I have a text box that allow user to enter ClientCode – UserCode in there, and I have to search it in database. I got little lazy and what I did I took the Textbox value as it is and did something like
where ClientCode + "-" + UserCode = @MyClientCode
Now, this looks good and works well for me, but this is not a good approach of writing it. In where clause never do the combination of column like that. Search easy column seperately, i.e.
where clientCode =@ClientCode and UserCode =@UserCode
the difference make your search works 10-15 times faster if you tend to have lot of records.
Third thing that comes to my mind is normalization of database and then creating views with indexes on view to join them if needed. I often see that database is not normalized and lot of Text field are used to store combination of values. For example if you want to have a product in different category then make a table Product_category, which store product ID and CategoryID, instead of storing comma seperate category IDs in product table. This seems awkward at first to change, but advantages are real. You can search product on category much faster as only Integer values are compare against the Regular expression match in comma seperated values. You can index the value in relation table, but not in text field. This approach is very common in developer in sub continent. If I am not wrong 70% of sites I see from other developer has this mistake.
Above are some error in design of database I have seen and some of them I did at my start of career, but I change my approach and today I am handling database with more than 10-20 million entries easily. Infact my site took less than 1 sec to search with a three table join. I remember the old version of this join that took 15 secs. I did great optimization there.
Anyways, making a software is your work, making it great is your choice.