Tuesday 3 June 2008

Code: Speed vs Aesthetics

In our drive to produce the fastest, most optimised code out there, sometimes we can leave ourselves with a sprawl of code that no one else is going to be able to follow - or quite possibly you may not be able to follow a few months down the line, even though you wrote it!

I'm not talking about real down-to-the-core assembler or machine code here, just general high-level code that you might look at and think "I could rewrite that so it was a little bit faster", but ultimately leave it much less readable and maintainable for the future.

Take, for example, a reasonably new feature that Digital Metaphors added to its ReportBuilder product a while back: SQLBuilder. As I mentioned back in June last year:

Once you have built up your query (using either the standard Query Wizard or Designer, or your own custom Query Wizard or Designer), you can then access the query as an object. And each part of that query is an object too, so you have the Criteria object and the Join object, etc. You can access this at run-time so you can amend the query to suit your needs just before the report runs


This has always been available using the QueryDataView object, with which you can add and remove tables, fields, joins, etc, in Delphi code without actually touching the underlying query. No need to know which flavour of SQL you're using, or having to find the ORDER BY line to insert a new WHERE criteria above it. Anyway, I'm repeating myself. The point is, you can do this using this object and it's simple but takes a little effort to get to the objects:


function TmyJoinBuilder.GetQueryDataView(aReport: TppReport; var aDataView: TdaQueryDataView): Boolean;
var
lDataModule: TdaDataModule;
begin
aDataView := nil;

{get the datamodule}
lDataModule := daGetDataModule(aReport);

{get the query dataview}
if (lDataModule <> nil) then
if (lDataModule.DataViewCount > 0) then
aDataView := TdaQueryDataView(lDataModule.DataViews[0]);

Result := (aDataView <> nil);
end;


This just to get the QueryDataView object. When SQLBuilder was added, this became a little easier:

lSQLBuilder := TdaSQLBuilder.Create(aReport);


and the code to add a new table and join to the query went from this:


procedure TmyJoinBuilder.Add(aReport: TppReport; const aTableName1, aTableName2, aFieldName1, aFieldName2: String; aJoinType: TdaJoinOperatorType);
var
i: Integer;
lQueryDataView: TdaQueryDataView;
lSQL: TdaSQL;
lMainTable: TdaTable;
lNewTable: TdaTable;
begin
{get the query dataview for this report}
GetQueryDataView(aReport, lQueryDataView);

{get the TdaSQL object}
lSQL := lQueryDataView.SQL;

{get the main table}
lMainTable := GetTable(lSQL, aTableName1);

if (lMainTable <> nil) then
begin
lNewTable := GetTable(lSQL, aTableName2);
if (lNewTable = nil) then
begin
lNewTable := lSQL.AddTable(aTableName2);
end;

{add the table join}
if not (DoesJoinExist(lMainTable, lNewTable, aFieldName1, aFieldName2, aJoinType)) then
begin
lNewTable.AddTableJoin(lMainTable, aFieldName1, aFieldName2, aJoinType);

{call out of sync to ensure the sql result is refreshed}
lQueryDataView.OutOfSync;
end;
end;
end;


to this:


lSQLBuilder.SelectTables.AddJoin(lNewTable, lMainTable, 'Field_name', '=', 'Join_to_field_name');


Now I know that what ReportBuilder is doing is actually wrapping all of the long code into one method call for me, and possibly doing a little bit more than I am doing as well in order to be robust. But the readability and maintainability of my code has now increased beyond what speed impact there might be. (For the record, I haven't noticed any, but we're not doing lightning fast mathematical wizardry here in which every millisecond counts).

Sometimes you even know that doing it the short way means some lower-level code is called multiple times. But isn't the end result more pleasing than the speed offset?