Wednesday, October 13, 2004

Why have a Project Management System?

During my earlier days, I would always wonder, why make a hassle by having a complicated, time-wasting project mgmt system. Just assign me the task, and I'll write notes, and it will get done. Well after 5+ years of experience, I have realized as boring as Project Management, it's really about preventing wasting of time and energy.

As I have learned, as I get more experience, is that being a good coder, is more than just writing good code. It means, mastering all the elements of a web development environment.

But today, we're talking about Project Management, or as I like to call it:

If you were psychic knew exactly what the client wanted, didn't have to waste time by asking any questions, and can just take a few minutes to generate immediate results.

Often, we are experiencing the matador/bull symptom, of seeing an attractive red cape to attack, but not seeing the matador's blade behind it.

This is where those who do not like anal retentive details, of what managing their assignments, are structured so that they are focusing the correct amount of time, on the correct elements that lead to a complete project that meets needs.

Let's analyze that last statement, "A Complete Project that meets needs."

First off, how do we make sure we really know what their needs are?

Secondly, since the person/people who have these needs are usually not the people fulfilling those needs. How can we make sure that we can meet those needs in the way the client wants?

Thirdly, about half-of-us, have to deal with feature-it-us, where in the middle of the project, someone wants to add new features that were NOT a part of the original discussion, and the rest of us are do not have to face it.

Where being Bulls, we just want to kill the matador, or bounce him around at least, we do not want to dance around, or try for Bull gymnastics. Just to keep focused on our goals, and get there....

Where as Matadors, just have needs, that they use the cape and blade to communicate with, not always clearly, and not always decisively.

So we as Bulls and Matadors must come together to work out a way to solve our mutual problems.

The problems are:

1. What the heck is this application, this isn't want I wanted.
2. Hey I definitely said red here, blue here, and lots of green over here.
3. I spent days, weeks of sleepless nights on this project, and you still aren't satisfied?

So how do we solve this problem?

1. You must have a clear and precise idea of what the project is, what the logic flow is like, maybe have some drawings of what the pages look like. What colors will be used, etc. Imagine the coder doing the work, is deaf, blind and dumb, and that you have to use all your senses to completely describe this project.

2. Then as a coder, you have to plan step by step, in detailed fashion, how you plan to execute this project. The client went to all the work to detail out their request, now you must explain how you plan to do the work. I usually ask myself questions, to help fill this out, such as: What kind of graphics need to be created/changed, What database changes need to be done, What form processing or logic flow changes are needed, and how will I know. Use flow charts, diagrams, the more details, the easier it will be to follow your plan.

3. Now client and coder sit down, and review the Project Plan, to make sure the coder's idea of the project are the same as the client's. And then you can go over how long each step will take. What priority this project has over any other work that needs to be done. Then the client either approves or disapproves the Project Plan. If approved, start coding/executing the plan, if disapproved, then must re-start Project Plan to fill any missing spaces, or to clear up any miscommunication.

4. If approved start executing the project plan.

5. Debugging, at this point, as you iron out the bugs, create a functional testing checklist- A list of items that help the average joe, know whether the changes done were accurately done. Coders know code, but end-users know whether they were able to balance their checkbook or not. So help your code testing, by identifying what functionality this new project adds/changes, and how to test the success/failure of that change/addition.

6. Functional Testing, preferrably, the client or a fellow coder, or anyone who has not looked at or worked on the application. You want someone with a clear mindset evaluate the success of your new application. You repeat steps 4,5,6 until all functional testing, says GO!

7. Deploy Mechanism, we each have different development/production environments, but it pays to have a system/person that deploys the code to the right servers at the right time. Remember to turn Trusted Cache off, to make the code active.

8. Test Again on Production, using the Functional Testing Checklist, to make sure that everything works as smoothly on production, as it did on development.

Then document all of this, so you can organize, prioritize, and learn from each project. I mean, wouldn't it be nice, if you were working on the same project as you did 6 months ago, to have that documentation available to help remind you of what work was last done on that application?

Remember this isn't about being Anal Retentive, it's about avoiding communication mistakes.

What kind of miscommunication mistakes have you experienced? Or do you hate Project Management? Please comment.

Tuesday, October 12, 2004

SQL Turbocharging

To really make your pages fly, depends on a lot of different things, such as indexing, table relationships, and good sql statements.

Normally we have two starting points, whether you are creating a table from scratch or whether you are working on existing code.

Let's say you are working with an existing database, and that you want the queries for this specific application to work fast.

First thing, you have to consider is the freshness of data needs. How fresh does the data have to be? This information, can help shape the coldfusion side of your queries, whether you use caching or not.

Second thing, is the control of the datetype enforcement of incoming variables to be applied to your query. For security it always pays to enforce the specific data type, either by using cfqueryparam, or other means.

Third thing, what is the logic of the data you are grabbing, are you looping from 1 query to another query? This is ALWAYS a bad practice for performance.

One of the hardest things I learned, is that you should combine the two queries, instead of doing the looping. But it will take patience and practice to formulate the query so as to return the same data.

Remember each ODBC/JDBC connection to the database adds a hit of time,threads and performance. Less queries is always better.

Now you have this query, now we want to make sure the tables are prepared to deliver the best results.

I always start by making sure tables have a primary key that is also clustered.

You can run the below script to get a list of tables, with primary keys, and whether or not they are clustered or not.

select i.table_name, cu.column_name, tc.constraint_name, case objectproperty(object_id(i.table_name), 'tablehasclustindex') when 0 then 'no' when 1 then 'yes' end as [has clustered index]
from information_schema.table_constraints tc
inner join information_schema.constraint_column_usage as cu on tc.constraint_name = cu.constraint_name
full outer join information_schema.tables i on tc.table_name = i.table_name
where objectproperty(object_id(i.table_name), 'isusertable') = 1
order by i.table_name asc, [has clustered index]

It's a great script for documenting the indexes of all tables in your database.

Now that the tables have primary keys that are clustered, then you look at additional indexes, to make sure the joins are indexed.

For example if you are always WHEREING against a username, but it's not the primary key, then you'd want to make sure it is indexed. Now only the primary key should be clustered, it also does pay to use a easy to understand name of each index.

Once all tables have indexes, that are using in your joining queries, then it is time to start creating relationships.

Creating Relationships in SQL Server can be a painful process, but also a great tool to help improve performance.

You start by specifying a relationship that has no referrential integrity. Referrential Integrity means whether it is strict in making sure that when table 1 is updated, table 2 has to be updated right after. You should consult with a dba, if you want to make sure you are making the right decision for the type of relationship.

But I always start out with a non-enforcing relationship, because that takes less effort in maintenance, and can cause less problems.

Now you don't want to create 5 billion relationships, but you want to look through all the main queries of your applications, for what joins that are commonly used.

So now your tables have primary keys, that are clustered, and have indexes for joined queries, and have relationships with joined tables.

The Database is now setup to deliver speedier data.

Now it is up to how you query that database.

SQL Server is different from Oracle, in that your from statement, the order of tables in it, can help/hinder performance.

In SQL Server 2000, the big thing is your where clauses and your joins.

This is where we take out the SQL Query Analyzer, turn on Show Explanation Plan, grab a sample query, and looking step by step.

An explanation plan is a powerful and graphical way of looking of how SQL Server plans to deliver the data you requested. I start out by looking for anything that takes more than 20% in resources, I look for table scans, or any sign, that a query isn't performing the way I want to.

Let's say you have two tables, Table A has 100,000 records, and Table B has 200 records. Now obviously if you have to do a join, you don't want to scan thru all 100,000 records from Table A, just to get 2 matching records from Table B?

You have to play with the joins, trying subqueries, or in statements, or exists, to create the query that delivers the right data, without unnecessary data scanning.

It pays to think about what kind of data and how much data you need first, then create the query to grab that data...

This is one thing that takes experience, and even I, still have a lot more to learn.

I hope this has helped by putting together all the main ingredients to improving performance.

After all, what's the point of creating a nice web application, if it can't load for the user, or it keeps crashing your site?

Later...