David's Blog - Algoritmus

Write Code Like You You Buy A Car

Published March 28, 2012 | By David

At the recent jQuery conference in Oxford there were a number of great presentations. One that particularly caught my imagination was by Doug Neiner who made some really interesting points about the trade-off between performance and maintenance. His presentation included some great one liners, one of which was “Write Code Like You Buy A Car – Always weigh the difference between cost and quality“. For cost you can also read performance. Whilst we should always endeavour to produce code that performs well, it’s not the only factor to be considered. I had the opportunity to chat with Doug in the bar after the event and I was pleased to say that our approach seemed to be broadly similar. It also reminded me of a couple of very different performance ‘issues’ that I had experienced.

Too Quick On This Occasion

I had the pleasure of working with a very experienced developer some few years ago, but he was no infallible (who is). We worked on one particular project with a VB6 front-end (I said that it was some few years ago) and Oracle back-end. Rather late in the project it was realised that the requirements for management reports had not been properly defined, and when they were defined it become apparent that our database structure did not lend itself to the production of the necessary outputs.

Instead of altering the database schema at a late stage in the project (our client had some very bureaucratic change control processes), my colleague developed a suite of awesome PL/SQL procedures that manipulated all of the data in memory using dynamic table arrays. The procedures were really fast, but the trouble was they ran at the end of the overnight batch process when performance was really not critical, and nobody but him could understand what they were doing. When the batch finished all of the data that had been manipulated and passed from procedure to procedure vanished. If any problems arose or questions were asked about the accuracy of the reports there was no way that anyone could look at the data that had been used during the process.

Some while after the development team was disbanded and the application had been supported by another team, the management reports were re-written to use a series of permanent tables that were emptied at the start of the process and could therefore be inspected at the end of the process should the need arise.

Nowhere Near Quick Enough

I was also reminded of what I hope is the worst performing code that I have ever written. If this isn’t the worst then there must be something really horrible lurking out there somewhere.

I developed some code, again in VB6, but this time using OLE Automation to manipulate Microsoft Word documents. One of my routines scanned a document, searching for each occurrence of a specific piece of text and replace it with another piece of text. It worked just fine. In my (too limited) testing I found no problems.

The issue arose when one of our first customers tried our application with a much bigger document. It took ages, about 20 minutes instead of 30 seconds.

When I looked again at my code the reason was obvious. What had I been thinking. I had written a loop in VB that did separate Find and Replace operations, each one incurring the overhead of transferring control from VB to Word via OLE. Word had a perfectly suitable Find & Replace All option so why hadn’t I used it. With that change made we were back to 30 seconds to process the document instead of 20 minutes. Clearly not my finest hour.

Performance Matters

So performance does matter, but it is almost always a trade-off. Don’t fall into the trap of trying to achieve the ultimate possible performance with every line of code you write. Consider also how easy it will be to maintain the code, and how much time you have available to squeeze out the last possible drop of performance.

Write Code Like You Buy A Car – Always weigh the difference between cost, quality and performance.

Posted in Performance | Leave a comment

Oracle TO_CHAR and National Language Support (NLS)

Published February 13, 2012 | By David

Some problems that ought to be trivial to fix can take an inordinate amount of time to track down and resolve. I recently spent more time than should have been necessary to identify the cause of a problem with some reasonably simple Oracle SQL within an application that I support.

The application manipulates some Railway data, combining Train Crew information with some supplementary Passenger details. It is very typical to find in Train Running and Train Crew data a 7-character field of that defines which day(s) of the week are applicable, with Y and N used to indicate days that are or aren’t applicable. When used in conjunction with separate start and end date fields it is very easy to define a calendar of dates (e.g. Friday only from 1-Dec-2012 to 31-Dec-2012)

The SQL in question needed to find records in the Train Crew data that were applicable to a single date in the supplementary data. Consider the following:

StartDate = 1-Dec-2012
EndDate = 31-Dec-2012
Days = NNNNNYN (Friday Only)
SupplementaryDate = 10-Feb-2012 (Friday)

The SQL was something like this:

SELECT * FROM TrainData WHERE StartDate <= SupplementaryDate AND EndDate >= SupplementaryDate AND Days LIKE ‘_____Y_’

However, it wasn’t quite that simple because the mask used in the LIKE clause had to be dynamically generated SQL based on SupplementaryDate. The position of the ‘Y’ within the string of underscore characters (the Oracle wildcard for ‘any single character) can be determined by the day number, so with Sunday being the 1st character and Saturday the last, Friday is the 6th character. Using the Oracle TO_CHAR(SupplementaryDate, ‘D’) to return the day number and DECODE to translate that into the necessary string we get:

DECODE(TO_CHAR(SupplementaryDate+1, ‘D’), ‘1’, ‘Y______’, ‘2’, ‘_Y_____’ etc.

The ‘D’ parameter of TO_CHAR returns the day of the week (1-7).
The +1 on SupplementaryDate adjusts for the fact that Monday is the first day of the week in the UK and the masks expects Sunday in the first position.

The code worked fine until one day a customer reported the somewhat unhelpful unexpected results. After several attempts to pinpoint the problem, pouring over data dumps and log files, it finally became apparent that this mask was being generated incorrectly, or more precisely it was one day out, returning ‘______Y’ instead of ‘_____Y_’. Of course this actual string is not exposed in any of the data that was immediately available to me, and most of the records had a variety of other values including ‘YYYYYYY’ or ‘NNYYYYY’ which masked the problem.

Having determined that the wrong mask must be getting generated I had to work out why. Of course, the first day of the week can vary by country, that must surely be causing the problem. According to Oracle documentation the ‘nlsparam’ argument specifies the language in which month and day names and abbreviations are returned. According to other sources I found on the web this should have resolved my problem, but no amount of fiddling with the SQL gave the correct results, and further investigation suggested that it was the ‘nls_territory’ session setting that needed to be changed. Sure enough, the following commands illustrate the problem but didn’t give me a workable solution:

ALTER session SET nls_territory=’UNITED KINGDOM’;
SELECT sysdate, TO_CHAR(sysdate,’D’) FROM dual;
ALTER session SET nls_territory=’AMERICA’;
SELECT sysdate, TO_CHAR(sysdate,’D’) FROM dual;

I was unsure how I could reliably execute something like this via an ODBC connection and for it to work with the various ODBC Driver and Oracle database configurations that my customers have. The solution that presented itself was a different approach to calculating the day of the week. By using the ‘J’ parameter instead of ‘D’, the TO_CHAR function calculates the number of days since January 1, 4712 BC, and this number is the same regardless of the national language settings. So now my function has become;

DECODE(MOD(TO_CHAR(SupplementaryDate+ 1, ‘J’), 7) + 1, 1, ‘Y______’ , 2 , ‘_Y_____’ etc.

Posted in Oracle | 2 Comments

An Approach to Data Import Validation

Published January 8, 2012 | By David

Data import validation is not something that I have had to worry about for a while. I recently had to develop some code for an application that I support to import data from a CSV file, match selected fields against data in an Oracle database, and add the information contained in the file to printed output that the application generates.

The tasks was a fairly simple one and I suspected from the start that the biggest problem was likely to be matching data from the CSV file with the correct records in the database. Developing an interface between systems which are loosely coupled in this manner is open to all manner of difficulties when you have no control over the quality and accuracy of the data in the interface file.

The initial implementation appeared to work perfectly well with the limited amount of test data that I had available and was delivered to the customer for them to try. After some lengthy delays whilst the customer waiting for the application that generated the CSV file to be finished they commenced their testing and immediately reported that too few records were being matched. Was I surprised?

Despite of the fact that the code I had developed included what I thought was a fairly comprehensive set of logging options, generating a log file that could show exactly how each record in the CSV file had been handled, the customer needed help to identify why records were not matched. A quick look at the log file revealed where the problems were and some minor changes were made to handle certain conditions, but a new approach was required.

After given this some thought it occurred to me that instead of ignoring the data from the CSV file that didn’t match any information in the database, with a fairly trivial change I could import these records to an put them into a special status of ‘hidden’, where they would be ignored by the rest of the system unless the user set a flag to indicate that they should be shown. Another minor change to the user interface to add this special flag and suddenly the users could see all of the data and inspect the records where no match had occurred. Why didn’t I think of that at the beginning?

Posted in Data Validation | 1 Comment

Implementing custom sort for a Treeview

Published December 7, 2011 | By David

The Treeview Sort method applies a simple character sort to the node text. For sophisticated business applications this is often too simple and a custom sort is required in order to sort the nodes based upon the content and not just the character representation.

For example, consider this simple scenario with the following nodes:

First Item
Second Item
Third Item
Fourth Item

Sorting these nodes using a character sort will place them in alphabetical order; First, Fourth, Second, Third.

A custom sort is easy to implement and link to a Treeview control. In this example the Tag property of the Node control is used to store data for sorting the nodes. The NodeSort class is assigned to the TreeViewNodeSorter property before sorting the nodes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

Public Class Form1
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

Dim nodParent As TreeNode
Dim nodChild As TreeNode

' Add four nodes and assign the sequence number to the Tag property
For intNode As Integer = 1 To 4
nodParent = TreeView1.Nodes.Add(Choose(intNode, "First Node", "Second Node", "Third Node", "Fourth Node"))
nodParent.Tag = intNode
Next

' Add a series of dates to the 4th node
For intAdjust As Integer = 1 To 49 Step 7
Dim dteNode As Date = DateAdd(DateInterval.Day, intAdjust, Date.Now)
nodChild = nodParent.Nodes.Add(String.Format("{0:dd MMM yy}", dteNode))
nodChild.Tag = dteNode
Next

' Assign the custom sort class and sort the TreeView
With TreeView1
.TreeViewNodeSorter = New NodeSorter
.Sort()
End With
End Sub
End Class

Public Class NodeSorter
Implements IComparer

Public Function Compare(ByVal x As Object, ByVal y As Object) _
As Integer Implements IComparer.Compare
Dim tx As TreeNode = CType(x, TreeNode)
Dim ty As TreeNode = CType(y, TreeNode)

If TypeOf tx.Tag Is Integer And TypeOf ty.Tag Is Integer Then
' Sort numbers in ascending sequence
Return String.Compare(tx.Tag.ToString, ty.Tag.ToString)
ElseIf TypeOf tx.Tag Is Date And TypeOf ty.Tag Is Date Then
' Sort dates in descending sequence
Return String.Compare(String.Format("{0:yyyymmdd}", CDate(ty.Tag)), String.Format("{0:yyyymmdd}", CDate(tx.Tag)))
Else
' Sort everything else alphabetically in ascending sequence
Return String.Compare(tx.Text, ty.Text)
End If

End Function
End Class

Note that the dates are sorted in descending sequence simply by swapping the X and Y nodes and comparing Y with X instead of X with Y.

Posted in .Net | Leave a comment

Using jQuery AutoComplete with JSON Data

Published December 7, 2011 | By David

The Microsoft AJAX Toolkit seems have been overtaken by jQuery these days, so I thought that it was about time I investigated what it could do and how it worked. For one of the projects that I’ve got on the go the AutoComplete widget that is part of the jQuery UI looked useful, especially as it can be linked to a remote datasource. The data for this project is stored in a SQL Server database, so I needed to prove that the AutoCompete widget would work with this.

After digging around it looked to me as though I would need to implement a generic handler that jQuery could call from the client and return some JSON data. The DataContractJsonSerializer class appeared to provide the mechanism for returning the data.

Step 1 was to create the generic handler. In my VS2010 project I added a folder called Soap and within that I created a new generic handler that I called SoapTest.ashx. The code that I wrote to begin with always returns 6 items, with the text entered by the user followed by a counter. All I wanted to achieve at this point was to make sure that I could return some data to the client.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

Imports System.Web
Imports System.Web.Services
Imports System.Runtime.Serialization

Public Class SoapTest
Implements System.Web.IHttpHandler

Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
context.Response.ContentType = "application/json"
Dim Term As String = String.Empty

If Not context.Request.QueryString("term") = String.Empty Then Model = context.Request.QueryString("term")

Dim DataList As List(Of AutoCompleteData) = New List(Of AutoCompleteData)
For ItemCount As Integer = 1 To 6
Dim DataItem As New AutoCompleteData
DataItem.label = String.Format("{0} - {1}", Term, ItemCount)
DataItem.value = String.Format("{0} - {1}", Term, ItemCount)
DataList.Add(DataItem)
Next

Dim Serialiser As Json.DataContractJsonSerializer = New Json.DataContractJsonSerializer(DataList.GetType())

Serialiser.WriteObject(context.Response.OutputStream, DataList)
End Sub

ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property

End Class
''' <summary>
''' AutoCompleteData class for serialising Json data
''' </summary>
''' <remarks>
''' The properties must be lowercase otherwise the jQuery code does not handle them correctly.
''' </remarks>
<DataContract()>
Public Class AutoCompleteData
<DataMember()> Public Property label As String
<DataMember()> Public Property value As String
End Class

Testing this code is simple. Run the project and change the URL to http://localhost:51727/Soap/SoapTest.ashx. Note that the port number when you run the project might not be 51727. If everything is working the function will return some JSON data that looks like this:

[{“label”:” – 1″,”value”:” – 1″},{“label”:” – 2″,”value”:” – 2″},{“label”:” – 3″,”value”:” – 3″},{“label”:” – 4″,”value”:” – 4″},{“label”:” – 5″,”value”:” – 5″},{“label”:” – 6″,”value”:” – 6″}]

Of course, the code is expecting to receive the data that the user has typed into the AutoComplete field, passed in via the QueryString ‘term’ item. Changing the URL to http://localhost:51727/Soap/SoapTest.ashx?term=abc will result in JSON data like so:
[{“label”:”abc – 1″,”value”:”abc – 1″},{“label”:”abc – 2″,”value”:”abc – 2″},{“label”:”abc – 3″,”value”:”abc – 3″},{“label”:”abc – 4″,”value”:”abc – 4″},{“label”:”abc – 5″,”value”:”abc – 5″},{“label”:”abc – 6″,”value”:”abc – 6″}]

Having established that the handler is working I then needed to hook this up to a control on the client with jQuery. A input field is required for this, and with ASP.Net 4.0 it is now very easy to link ASP controls into the Java script by making use of the new ClientIDMode property. Setting ClientIDMode=”Static” generates an HTML input field with the same ID as the ASP control which can therefore be used to hook the control into jQuery.

The server side control therefore looks like this:

Enter Text: <asp:TextBox ID=”TBAutoComplete” runat=”server” ClientIDMode=”Static”></asp:TextBox>

And the associated jQuery call like this:

1
2
3
4
5
6
7
8
9
10

This all seemed to work quite nicely. Next I need to extend this to link the AutoComplete field to a drop-down list and pass an additional parameter to the Soap handler. Finally I’ll need to hook up the Soap handler to my database but that should be the easy bit.

Posted in jQuery | 3 Comments

Algoritmus

Software Solutions for Business Problems

Write Code Like You You Buy A Car

Too Quick On This Occasion

Nowhere Near Quick Enough

Performance Matters

Oracle TO_CHAR and National Language Support (NLS)

An Approach to Data Import Validation

Implementing custom sort for a Treeview

Using jQuery AutoComplete with JSON Data

Recent Posts

Recent Comments

Archives

Categories

Meta