Tech Tip du Jour: How much data are you moving around?

This one is a really obvious one, but it's so often overlooked in this day and age of experienced programmers who are inexperienced in the ways of databases: think about the amount of data you're moving around in those SQL statements you're bandying about.

Scenario 1:

Table tablea contains a BLOB/CLOB/BYTE/whatever. Programmer uses SELECT * on the table when he doesn't need to access the large object.

Result? Poor performance and massive overhead on the server (and network) for no reason whatsoever.

Fix? Only select the columns you actually need, whether the table contains a large object or not.

Scenario 2:

Table tableb contains 16 different versions of a LVARCHAR(1000) field. This table is the core of the system and is hammered all day long by thousands of users. Only one version of the LVARCHAR is actually ever valid at any given moment.

Result? Massive IO load on a server that should really be doing bugger-all. Pointless excess load on the network.

Fix? Normalise the LVARCHAR into a separate table with a version number.

Scenario 3:

Table tablec contains a large object. The application has been coded in such a way that rows in tablec are read multiple times when the data has not changed between reads.

Result? Poor performance and massive overhead on the server (and network) for no reason whatsoever.

Fix? Only select rows when you actually need them, whether the table contains a large object or not.

Scenario 4:

Programmer needs to sum the values in a column for a given set of rows. He uses "SELECT * FROM tabled WHERE foo = bar" to pull the required rows into Java objects, then manually sums the values by inspecting the objects. (Really, I'm not making this up.)

Result? Unnecessary network load and poor performance.

Fix? Learn a bit of SQL, and try SELECT sum(columnname).

I make no apologies whatsoever for the apparent fatuity of this post. Programmers out there are making these exact mistakes right now.