Database optimization, Materialized Views

Database tuning and optimization are big buzz words these days. How do you optimize your database? What does optimizing your database get you?
Sure, you now have all of your data in a safe place, your database.  But as your data grows and as you expand your database and how you use your data, you will have to enhance the way the data is accessed.  This is so that as the data size and its complexity increases, your operations are not adversely affected as a result of slower response times.  This should be a part of provisions for scalability.  After all if you have hundreds of records, you will access them a lot faster than if you have millions.  This is why all major relational databases feature ‘Views’.  Views are snapshots of your most desired data.  Views are dynamic and get updated as data gets updated.  Very nice tool to get better database response times.

Now even with views when you have millions of records, speed of data access can become an issue.  Especially for applications such as financial or banking applications that potentially have to process millions of records per minute (or even per second).  With such massive volumes of data, you would need to make your views more efficient.  This is why Oracle came up with ‘Materialized Views’.  These are views that get saved on the disk!  Yes in the case of materialized views, your views are not quite dynamic, but they almost are!  You can refresh your materialized views every 10 seconds!  I guess you can still call that real-time updating of views. 

With Materialized views, access to data is much faster, although the view has to be read from a disk but since it can be scheduled to be refreshed (e.g. every 10 seconds), it can still be considered real-time (i.e. dynamic) data.

There are a lot of little tricks with an efficient use of Materialized Views, a discussion that I will leave for another time. 

To optimize your database, other than views discussed above, you may have to change things both on the database side and the application side:

On the database side, you also need to look at how well your data is indexed.  Your most accessed data items need to be indexed more efficiently.  For instance, foreign keys used in joins need to be indexed.  However remember that indexing does slow inserts so there are some precautions to take.

On the application side, every time you call a database routine to perform an operation, you will need to login to the database, open it, make your call and then close the database and grab the returned data (if any).  You will need to see how many trips you make to the database within a single module and minimize that number as much as possible. 

In conclusion, database optimization involves in-depth knowledge of database tools and techniques.

Post to Twitter

Posted in Uncategorized | Tagged , , , , , , , , , , , | Leave a comment

Outsourcing, politics and payroll taxes

Sure, now we hear president Obama take on his rival Mitt Romney for Mitt’s alleged outsourcing US jobs to overseas companies.
With that in mind, it’s also important to note that the Federal government and State Governments are currently not obligated to keep US jobs at home! Our company, AMK Web lost at least 2 California state contracts to firms that ship their software development jobs overseas!
Now wouldn’t you think that if we are to stop outsourcing jobs, we should start with government jobs?
Mr. President, my company has been hiring local developers for the past eleven years. What incentive has the US government offered small firms like ours? We still have to pay a great amount in payroll taxes. If we outsourced our jobs, our payroll taxes would have been much less or next to none! In this economy, this is a matter of survival.
Outsourcing and hence saving money may be just good business. After all this is a free enterprise system and you should maximize profits in any possible legit way you can.
Since I am a software specialist and run a software company, I will keep my focus on just my field. In a previous blog on this same website, we analyzed a bit what happens when a company sends their software projects overseas. The result is almost always more money spent, a lot more headache and then a project taking several times longer than what it would take in the states. When Outsourcing in other fields, the outcome might not be so grim.
Now here are a few other items to consider, when you send your jobs overseas, you are in effect helping another economy instead of US’s economy. Money leaves the US and is poured into another economy.
There are a lot of valid arguments such as lack of financial incentives to keep jobs here. US government and our great legislators do not dish out a lot of good incentives to businesses to keep their jobs here. How about higher taxes on companies that ship work overseas? How about cutting payroll taxes for those who hire within the US? The benefits are obvious but are the politicians listening?

Post to Twitter

Posted in Uncategorized | Leave a comment

Costing a Web Application

 

This is a huge issue especially in the current economic downturn as everybody is looking for the best deal in town! Well with application/database development the best deal in town is not the one with the lowest dollar amount. Rather it’s the one that has feasible and reasonable cost estimates and more importantly with the right group of developers. The truth is when you go out to look for a company to do an application that your business depends on, before you sign that contract and pay the deposit, make sure the company can do what it promises. Check their references and find out if they have a history of commitment to their clients. It’s easy to say that we can do it and also we can do it in a required time frame. Remember that once you pay the deposit, you are hooked.

Naturally companies that send their work overseas will have the lowest costs. But unless you get extremely lucky (and in all these years, I have almost never seen that happen), you could be taken for a ride. You will be out of your money and your project will take 5-6 times the time it may take to finish here. Sure, in the beginning the cost is very attractive but then as your project goes on, you will see promises not coming thru and excuses come one fter another. And most of the time you are lucky if the project even ends. In many cases that I have seen at best overseas companies complete about 90% of the job and then you will be here looking for a company to do that last 10% only to find out that this 10% may cost you more than the 90%! Why? Because working on existing code is generally extremely difficult. You will have to pay for the new company’s learning curve and then how do you know that the code coming from overseas is well documented. Then also, After everything is complete, you will have the two companies blaming each other for any problems. Now after several years a project that should have completed in six months, is taking you about three years to finish and you have paid the same amount that you would have to pay if you had done it all locally.  That’s as I said, if you are lucky!

Also remember that with complicated enterprise database applications, you have to have quick access to developers. Sometimes it may take several meetings to get one single requirement understood. Just imagine how that would work with an offshore company with different time zones, language barrier, etc.

Do your homework. Know who you are dealing with. Check references and make sure the scope outlined in the proposal is complete.

Post to Twitter

Posted in Uncategorized | Tagged , , , , | Leave a comment

Application development conventions and rules

In any scalable software development project, there needs to be established a set of rules and conventions to follow.
Clients often ask for good documentation in the code. OK. We will put in documentation but chances are the notes that you put in right now for a module will be meaningless or at least insufficient year from now. Yes when you start coding, you make everything nice and pretty with comments above function calls or Stored Procedures. Notes describe what the function does but depending on its purpose, often the function will be edited again and again to the point where the original comment is rendered un-useful. That’s why as a project manager, you need to ask your developers to add a comment with every change they make. To that they will also need to add a date as well as their initials. That way several months or years from now when code is visited by some developer or even the developer that made the change, code can still make sense. Something like:
– Changed code to capture DOB, MK 3-12-2012
If the change is more complicated, then the documentation needs to reflect that as well.
At AMK Web, we have a set of conventions for how we name our database columns as well as database Stored Procedures and functions.
Here are a few tips:
1. Name the first field (primary Key) in any table a common name…. We simply call it primarykey, no matter what the table.
2. For stored procedure input fields, we use the prefix @inp_ followed by a name that is either a column name or some other meaningful name.
3. Using the same convention, your output variables start with @outp_
4. For stored procedure names, spell out the purpose of the SP… (e.g. insertAPayment)
You would be surprised how much time this saves us. We always know what the primaryKey is called in any database table we use. It’s also very fast to locate a SP.
Next I will go over some conventions that can be used in application development platforms,

Post to Twitter

Posted in Uncategorized | Tagged , , , , , | Leave a comment

Developing scalable classes for a web application

The key to developing an enterprise application that can grow with the business requires the application development team to have a vision.  Code the database and the front-end application so when business rules grow, it’s easy to accommodate new operational parameters.  A good way to create a scalable front-end application is to make available various data elements of an entity. 

Creating objects that encapsulate all necessary data elements of an entity is a great way to modularize your application.  Let’s think for example of an application you are writing for a university.  You have three main entities: Students, Classes and Faculty.  If I were to build such an application, I would create a class for each entity that would hold all possible data elements for that entity.  That includes data that your application doesn’t currently require.  For instance, in the Student class the city that the student was born in may not be of significance right now.  But still when you create the class, you should still allocate a space for the city of birth just in case it becomes important for future statistical purposes.  In such a case, instead of having an application programmer go into the class code and adding new functionality, the front end programmer can just pull the city of birth using a Get function.

Post to Twitter

Posted in Uncategorized | Tagged , , , , , , | Leave a comment

Managing a Web Application: Designing the Database

Database design or database application development is an art.

You have identified the data items that you want to save and you know what data you want displayed on each page.  Which do you do first, User Interface design or Database Design?

That might be a matter of preference.  What I normally do is create my database tables first.  It’s easier to make changes to your database table structure than it is to change how data looks on the screen.  Screen changes do take longer usually.

MS-SQL has made designing tables so much easier.  You can even use the SQL script builder to insert into and update your tables.  Writing Stored Procedures is always the preferred way to save and retrieve date.  A convenient way to call the stored procedures would be from data access classes.  You generally encapsulate your data access routines into these classes.  Then from your front-end application, you would call the class methods.   This way you would organize your application.

Post to Twitter

Posted in Uncategorized | Leave a comment

Web application components

Most Web Applications consist of two main components:

  1. User Interface Design
  2. Database Design

Designing the User Interface.

Based on your blue print you should know what you want to present.

Put related information close to each other.   Data on the page needs to be related and although it may need to be saved in a few different database tables, needs to be interconnected.

Navigation from page to page needs to be made as easy as possible. If application’s web pages are required to have common headings, master pages will become handy.

However you decide to do your user interface, you will need to use a tool like the .NET environment.  .NET environment will let you design your screen the way it makes sense to users.  You will design your pages and then code the application so you can save screen data in your database.

The general advice would be to try to use a consistent way to save and retrieve your data so the job of replicating the same process would be easier.

Post to Twitter

Posted in Uncategorized | Tagged , , , | Leave a comment

Running an Application Development Project

IT managers often wonder how they should go about starting a project.   No matter how small or large your company is, principals are the same.  Even though with smaller organizations, you have fewer people to talk to, still the same path needs to be taken

Identifying the need

  1. Conducting meetings with requesting organizations to define in clear terms what needs to be accomplished.
  2. Creating a blue print of the final product.
  3. Refining the final product by reviewing the blue print with the requesting organization.

Keep in mind that these facts about the blue print:

  • While bullet points show the major idea, details may not cover 100% of what is needed.  This is just a fact of life.  Project definitions that try to define every minute detail, often times will take more time than necessary and will result in unwanted delays.  With that said, major points do need to be identified.
  • When a project is being defined, requesting managers should always talk about the big picture, what is in the horizon and how the product of this application development project fits within the future of the organization.
  • It’s important to conduct meetings with different people within the requesting organization to learn about other viewpoints and expectations about what need to be accomplished.

Next I will talk about:

User interface design

Database design and development.

Platform to use

Finding an application development team.

Post to Twitter

Posted in Uncategorized | Leave a comment

Figuring up Database Costs

Let’s be realistic; Database development is expensive.   A typical scenario: Your business has been running for years without a relational database, or maybe you have been using a relatively mediocre database tool such as MS-Access or a data management spreadsheet tool such as Excel.  You have come to a point where your existing data management tool is just not cutting it any more.   You just have way too much data to handle, searching has become a painful process and your data are not linked.   

I would say it’s time for you to upgrade your database management ways.  So where do you find database designers/developers?  How much should it cost you? 

People have different ways of finding their resources but as far as the cost is concerned, don’t think of it as going out there to buy a nice Appliance for your house.  Instead, in terms of cost, think along the line of remodeling your kitchen.   Database development is not cheap.  Even if you get a cheap offer say from offshore companies or some local recent graduates, don’t be duped into starting with them.  It will be hard to get out of it.  And when you do, most probably the new company will have to start from scratch.  You have lost several months and thousands of dollars, you are frustrated and you are still without a workable database.

The invaluable advantage of hiring a professional US company is that during the course of database development you will need to be constant contact with your developers.  They need to learn your business inside and out!  You will become very good friends with them so they will need to be easily accessible.   In fact before you hire them make sure they understand your business well and that you are comfortable with them. 

I used to hear that Microsoft and Oracle database experts charge something in the neighborhood of $400-500 per hour!  That’s a whopping figure but it should tell you that if you find a good database developer for under $150/hour don’t let them get away.

Now if you are new to this, I don’t want to scare you!  If you are just starting your business or have minimal amounts of data, your costs will be much lower depending on what you want to do with the data.

Post to Twitter

Posted in Uncategorized | Tagged , , , , | Leave a comment

Database triggers, why are they so dangerous?

People ask me whether or not they should embed a business rule into a trigger.  My answer is hmmm… Well, triggers are a wonderful tool to use when you always want a certain action to take place upon a table insert, delete or update.  Yes, triggers sound quick and nifty and are left for those with esoteric knowledge in database development, but are they safe?  After all you only see the triggers if you click on the trigger box under each table.  They are not immediately obvious.

The problem with triggers is that you tend to forget them!  Suppose after a table has been designed and used for many months or years, you decide to write a new script (i.e. stored procedure) to change something that affects one of the tables that your trigger changes, you will have to remember about that trigger!  Or it could come back to haunt you.  And then after the trigger damage is done, you will have to edit your trigger to make its functionalities conditional.  Over time, with so many conditions, your trigger will be way too confusing and the whole original purpose of creating the trigger diminishes.

Use triggers with care.   I think delete triggers are generally safe, insert triggers are fairly safe as well.  Most problems are with update triggers.  I would try to avoid them.

Post to Twitter

Posted in Uncategorized | Tagged , , , , , , | Leave a comment