Within 2 weeks of starting I was in charge of a VB application running with an Access 2.0 back end. Since I was also doing data entry and under writing, it became obvious to me after about a month that this was not a viable solution for the long run. No one believe me off course, me being a lowly co-op student, and the highly paid consultants that came up with the solution said other wise. I finally convinced one of the network engineers to hook up network sniffer to prove that Access sent way too much data back to the client. My estimate was that with the current user load of ~20 people, we would reach stand still at about 80,000 quotes.
Making noise off course leads to being asked "What do we do instead smart ass?" by the CIO. My off the cuff remark was "I have heard about this thing called MS SQL Server. It looks like what we need!" Ahh the self confidence of being young.
This in turn lead to the same highly paid consultants being charged with converting it from Access 2.0 to SQL 6.0. Big mistake.
When we got it in house, it took two SQL Servers to run. 1 to store the quotes and binds (term for when someone buys a policy), and the other for doing calculations. If I remember right these two servers where Pentium 60 or 66 CPUs. Me not knowing better I took their word for this.
When the calculation server started to blue screen every 3 to 4 hours from what appeared to be memory leaks I seriously started looking into this SQL thing a bit harder. Up until then I just made sure backups worked. In the calculation database I found tables with our factors we used to calculate the premiums. I found no code I could look at. After some research I found these xp_ things. Yupp, extended stored procedures. Which again was written in C. No problem, I know C, let me take a look. Nope, no source code anywhere. Call to the consultants: "We had to do it this way, SQL can't do calculations." What? "I might not know SQL, but I have never heard about a computer language that cannot do math! Get me the source code and I will take a look." After much complaining, up to and including the CIO of the company threatening to pull all the contracts from them, I finally got the source code. Printed out. On a dot matrix printer.
On a business trip to visit with AOPA in Wichita in November 1995 I grabbed the print out and the book that came with SQL 6.0 (the actual book used on that trip is in the bottom right corner of this picture):
That was all I had to learn T-SQL from. In those days there was no SQLServerCentral.com, or the myriad other blogs and web sites to learn from, nor did I know a single other person that knew SQL. All the hint I got from the consultant was "this is a SELECT statement, that is all SQL can do with the data in the tables."
By the time I got back from Wichita, I had all the extended stored procedures re-written to T-SQL. Back in the office I made my very first stored procedure out of one of these queries, ran it, and WOW I HAVE A RESULT. Pass that result into the next procedure for the next factor, and WOW I HAVE THE NEXT RESULT. "This is COOL!!!!!!!!!!!"
Quick implementation on our dev and test server (one thing I did insist on when we went to SQL), then a lengthy round of testing to ensure values matched the AS/400 for the same criteria (we could have a max deviation of 1 cent), When I, with <4 months of experience could prove the highly paid consultants where absolutely clue less about SQL (experience coding AIM-7s turned out to not be too relevant), that consulting company was summarily fired and we hired in NewData Strategies.
Most of my career at American Eagle Insurance I also kept being an underwriter, as well as the Client/Server Application Specialist. At one point we made an application that we could install in agents offices if they wanted, with this they could enter the information directly into our system, and if the application was within certain limits, it would automatically do the underwriting and quoting on the spot. If it went above those limits it would notify the underwriters.
The efficiency we created with this system was unprecedented in the aviation insurance industry. From what I understand from the VP of the division at the time, everyone else used rate books that they would manually go look up all the factors to give a quote, depending on the aircraft there could be up to 17 factors (in our system). With the AS/400 system we maxed out around 250 quotes in a day, with the new system (after we got it on SQL) we broke 800 in a day easily. At one point they even had to let half of the data entry staff go since they where no longer needed. We could take a call, give you quote over the phone and bind the policy in <5 minutes. The huge time save came when someone wanted options, like different levels of liability coverage: 15 seconds to generate a new quote with the new limits.
Why was this speed important when everyone else took days? Strangely enough people will buy a new aircraft without checking on insurance first, an agent would typically type up the information about the plane and pilot and fax it to the various insurers. Very often we would get the business purely on being the first to return a quote. Our quote to buy ratio at one point was over 3 times the rest of the industry.
With our capabilities to electronically quote we also managed to land the contract for sole underwriter of AOPA (Aircraft Owners and Pilots of America) Insurance. This was a huge contract that I ended up spending the last year and half with the company supporting both from a database standpoint (handling the specific database code and security for them to hook into our system), as well as being the chief underwriter on the account for a while. Good times. Lots of good steaks consumed in Wichita.
By the middle of 1997 it was evident I needed to move on if I was ever going to learn more, the company had stagnated, and so had my skills. Time to move on to new SQL adventures!
My biggest regret with the first job was not taking a few extra days at a client in Florida, could see the Space Shuttle being moved to the lift off position. Would have had a spectacular view point from the roof of their building.
To Be Continued.
No comments:
Post a Comment