Lump Of Madness, Continued

It is time to continue the action-packed database dumping adventure from my previous post. Okay, I went on to use C# to import the data. First, I tried using the ODBC way (OdbcConnection, OdbcCommand, etc) to get the data from Informix. Everything seems well until I realized that some wretched and foul things has happened, the data fetched seemed to be truncated! I got only the first character for a char(3) column, and only 5 from a char(10) column. Using both OdbcDataAdapter and OdbcDataReader yield the same results. WTF... Yes, exactly. I quickly resort to OLE DB for alternative, using OledDbConnection, OleDbCommand, etc. Getting the correct connecting string seems daunting at first, but this site is really a life-saver: http://www.connectionstrings.com/ First run with the OLE DB, I got an error, something like "INFORMIXSERVER variable is not set in your system" (I forgot the real error message). Came out that I need to set some environment variable thing with the Informix client tool. And after that, things move rather smoothly. I ran the script that afternoon at 5 PM (or is that 'evening' already?), hoping it'll finish up during the night. But it doesn't, need some couples of hours more. I know the job is huge, but it took more time than I expected. Let me give a bigger picture. I actually want to get 5 sample rows for each 'subclass' (that is some criteria in the system), denoted in the table as a char column. So I need to do something like:

SELECT FIRST 5 * FROM myTable WHERE subclass='ABC'

Yes, straight forward. But the catch is, we have about 150 types of subclasses. Meaning that I have to iterate that command 150 times, traversing more than a million rows (maybe less if we're lucky) every time. Oh, yeah, the table have about 160 columns. Yes, I'm also wondering what the DB designer was thinking. Finally, after much perils and hardship, I got the data that I long for! Yes, victory is always so sweet. This table is kind of like the super parent of all other tables, so after getting the desired primary key, it's easier to get other related data. I just used the click-next DTS wizard for this. Ok, that's it, el finito. Much more detailed than you desired I guess (who'd actually be interested about this anyway). I manage to figure out some things along the way.

1) I set up a linked server from my SQL Server to point the Informix DB. When querying it from the Query Analyzer, it is so darn freaking slow! Took several minutes to even get 1 row. With the DTS, which is directly using ODBC, is reasonably OK. But the OLE DB code with C#/ADO.NET is way faster. 2) Index your table guys! I just (really) realize that compared to querying an unindexed table, it's like comparing an F1 car (BMW Petronas, yay!) to a crippled chicken. With the good leg tied to a coconut. 3) I wonder what is the difference between creating an index consisting of two column, compared to creating 2 index (one for each column)? 4) ADO.NET rocks big time! To switch from ODBC to OLE DB, I just need to change OdbcConnection to OleDbConnection and so on. Besides the connection string of course. 5) Regular expression rocks even harder!!! This is the unsung hero of the story. I'm using my home-brewed regex tool to generate the SQL statements. Kinda of like a pseudo-code-generation technique. If you're a progammer, and don't know regex, please do learn it, and get some decent regex tool to make your life easier.