Where there is no vision, the people perish
(Proverbs 29:18A, KJV)

It’s old wisdom but still valid. When you have no direction you flounder and sometimes the people around you flounder, too. This is true in the tech world as much as it is in more obviously social arenas.

A corollary of this is that if your vision is a bad one then the people may just barely scrape by.

A while back I posted some code that imported documentation from a CSV file and used that data to update a Sybase PowerDesigner data model. I mentioned that the code was slow because you were potentially iterating through almost the entire data model for each piece of column documentation you imported. I was pretty sure there was some method of the PowerDesigner COM object that held the key to fixing my performance problem. This may have been true but I managed to solve it by changing my vision. I moved from a very limited vision to a broader vision and this fixed things.

I was pretty interested in learning a lot about the PowerDesigner COM object. I was more interested in this than in solving my problem – e.g., importing documentation into the data model. I so focused on getting the PowerDesigner COM object to do interesting things that I didn’t pay much attention to the less nifty parts of my project – the lowly, old CSV file.

Iterating through a PowerDesigner data model through the COM interface is slow but interesting. Pulling a CSV file into an in-memory hash object and searching the hash object is less interesting. But it allowed me to solve the problem significantly faster. On my machine, when the CSV file is the pulled into an in-memory hash object and the PowerDesigner data model is iterated over only once the code executes in almost 1/10th the time it takes to iterate over the PowerDesigner data model multiple times and the CSV file only once.

Once I stepped back and looked at things more objectively it was easy to see how to make this thing run faster. Once my vision became broader – e.g., solving the problem rather than solving the problem by getting PowerDesigner to do something interesting – I was able to see the obvious solution.

The root of the problem was that my vision was too specific.

This seems to happen a lot in the tech world. XML is a great answer to a lot of different problems but a number of years ago people were using it to solve all sorts of problems to which it wasn’t suited. It seemed almost as if there was an unwritten mandate in a number of organizations that if you couldn’t fit XML into your application then you wouldn’t get your project funded. Developers were trying like mad to get that acronym on to their resumes, too, and that didn’t help. A lot of people got soured on XML or believed it was all hype because of that. The dust has settled and XML is a mature technology with a variety of uses but it was hurt by the narrowing of vision that came along with the XML boom.

Anyway, I’ll post the updated code below. It has two functions which do the same work. One of them does it by iterating over the PowerDesigner data model one time and finding the appropriate CSV data in an in-memory hash object. That function is called test_hash. There is another function called test_model_iteration that basically uses the algorithm in the original post. I’ve also included some helper functions that are used by test_model_iteration. The script outputs the start time and end time of each call so you can see for yourself that the in-memory hash approach is much faster.

As in the original example, the following variables need to be set:

$pathToDocumentation is a path to the CSV file with a column called Column and a column called Description

$PathToModel is a path to the Sybase PowerDesigner model

$tableToUpdate is the name of the table whose documentation comments you are updating.

Does anyone else have any stories about how the narrowness or broadness of their vision affected their work?

Please let me know if you have any questions about the code or have problems adapting it to your situation. I can’t promise I’ll have time to help you but I might be able to offer a comment or two.

Function GetTable($Model,$table){

$Model.Children | where-object{$_.Name -eq $table}

}

Function GetColumn ($Model,$table, $column){

GetTable -Model $Model -table $table | foreach-object{$_.Columns} | where-object{$_.Name -eq $column}

}

Function GetColumnComment($Model,$table, $column){

GetTable -Model $Model -table $table | GetColumn -Model $Model -Table $table -Column $column | foreach-object{$_.Comment}

}

Function SetColumnComment($Model,$table, $column, $comment){

GetTable -Model $Model -table $table | GetColumn -Model $Model -Table $table -Column $column | foreach-object{

$_.Comment = $comment

}

}

function test_hash{

foreach ($row in $documentaion_raw){

$documentaion[$row.Column] = $row.Description

}

$MyModel.Children | foreach-object{

If ($_.ClassName -eq "Table" -and $_.Name -eq $tableToUpdate){

$_.Columns | foreach-object{

$_.Comment = $documentaion[$_.Name]

}

}

}

}

function test_model_iteration{

Foreach($comment_entry in $documentaion_raw){

SetColumnComment -Model $MyModel -table $tableToUpdate -column $comment_entry.Column -comment $comment_entry.Description

}

}

$pathToDocumentation = "C:\Documents and Settings\My Documents\PD_Test_Project\data models\documentation\test_import.csv"

$PathToModel = "C:\Documents and Settings\My Documents\PD_Test_Project\data models\PD_Test_Project.pdm"
$tableToUpdate = "ps_import_test"

$PDApp = new-object -comobject PowerDesigner.Application

$MyModel = $PDApp.OpenModel($PathToModel)

$documentaion_raw = Import-Csv($pathToDocumentation)

$documentaion = @{}

Get-Date

test_hash

Get-Date

Get-Date

test_model_iteration

Get-Date

$MyModel.Save()

$MyModel.Close()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($PDApp) > $null

Remove-Variable MyModel

Remove-Variable PDApp