My last post described various attempts to improve the response time of a large SELECT statement from a very large table in IBM Universe. I’m pleased to report that we finally found the trick, though it turned out to having nothing to do with .Net and very little with UniOLEDB.

As mentioned at the end of my last post, my effort to optimize the SELECT statement by changing the .Net code had topped out around the 5 minute mark, well short of the 45-second response time that a query written in Universe Basic could achieve. I couldn’t see any way that .Net and UniOLEDB could replicate the technique used by the Universe Basic code, which involved building a native List structure from the keys, using a native Universe Basic command to extract the matching records from the large table, then running the SELECT on just the extracted records.

Fortunately my .Net code didn’t have to replicate this technical trickery; it could just stand back and let the Universe Basic code do the heavy lifting. Thanks to the fact that Universe Basic subroutines can return resultsets to .Net, I simply passed my SELECT statement to our Universe Basic code, let it do its thing, and processed the results.

OleDbCommand cmdTest = new OleDbCommand(”CALL GET.RECORDSET(‘SELECT yada yada yada’)″, conn);
cmdTest.CommandType = CommandType.Text;
dataAdapter = new OleDbDataAdapter(cmdTest);
dataAdapter .Fill(dataSet, “Table1″);

From the point of view of the .Net application this change is totally transparent — the contents of the resultset are exactly the same as when my code submitted its own SELECT statement. The Universe Basic subroutine had to be modified to extract the table name and key list from the SELECT statement, and it has since worked flawlessly in our testing, without stumbling over any rows or column types.

This turned out to be quite a good overall solution: virtually no .Net code changes, about half-a-day of coding for the Universe Basic programmer, and since the .Net code still uses SQL we maintained close compatibility with other databases. If we were to replace Universe with a relational database, I would just have to change a couple of lines of code in the .Net application.

The performance improvement when reading large amounts of data from the very large table was dramatic: from a previous best of 310 seconds down to just 45. Performance is slightly poorer than before on smaller SELECTs, but not enough to be noticeable to the user.

I’m a little disappointed that we couldn’t find a solution that only used .Net code. We already had to replace our INSERTs, UPDATEs and DELETEs with Universe-specific code as described in this post, and now the last facade of seamless SQL support has fallen. However, it was probably naive to think that we could transparently run SQL statements on a non-relational database like Universe. When in Rome, do as the Romans do. An appropriate axiom, considering that the Universe database is almost ancient history!