"I've been involved in systems (Dell for example) where we've placed ALL logic into Oracle packages to improve performance. Reason for doing this was because the application was simply pulling data into the middle-tier massaging it and then putting it back into a different set of tables. We decided that rather than take the hit of the network hops we'd put all the work in the database - we couldn't have done this had we not had an Oracle DBA."
This brings up an experience I once had and the results are surprising. I was given a task of writing an import engine to import text files into MS SS 2000 and validating and sumerizing the data. The validation process was extremely complicated because of it's heirarchial structure and inheritance and user configurable. I was replacing the current guy who got the process down to 3 hours. Goal was 15 minutes because a competitor said he could import the data in 15 minutes (note: we didn't know what he did with the data though). After re-writing the sql SPs and fine tuning for a month, I was only able to get it down to two hours. It was 669 queries that had to be run for each file of 24 records and I think it was 10,000 files. I worked with a good DBA to fine tune and we just couldn't get it any better. One day we were in a meeting and a junior developer said "the bottleneck is the IO". That got me thinking....many threads can read but only one can write and even though we were using temp tables, the queries still had to write to disk (I thought it would happen in memory but it didn't). I had realized that what I needed was a middle-tier to do the validation and creation of exceptions. I re-wrote the process using VB.net. Within a month, I had written a middle-tier app that would BCP the data in MS SS 2000, then I would run an SP to retrieve all data needed to do validations, then do the process and write out the exceptions and the data back to the DB. I multi-threaded this just like I did the SQL version, but with running it on two standard desktop machines with 7 threads each, I got the processing time down to 18 minutes. Another plus was that the other developers weren't pissed at me when I ran the test and killing the server cpu like I did with the SQL version. I didn't have another machine to test with, but if I did, I could have made that 15 minute requirement. Another surprise twist to all this. The DBA was a contractor and so was the architect. They wanted me to do this in SQL because they had control over me and made sure my development time was slow when I worked on the SQL version (they spoon fed me info and I had to keep re-writing stuff). They were VERY VERY against the DotNet version and I couldn't figure out why until later. After I finished the task, I was fired for being unsubordinate. I knew something was going on behind the scenes but I didn't know what. Turns out that the contractors and the VP was in a little scheme together. Once they were caught, the contractors were let go and the VP was fired for selling the code to a competitor. Why did I get fired....because I went to the VP telling him that the contractors were slowing the project down and they had a different agenda then the company and our team. So, I hope you learn two things from this a) don't trust anybody b) just because there is network traffic involved with one possible solution, that doesn't mean that solution is slower. In my case, because I was multi-threading, when one thread was waiting on network traffic, another thread was doing validation and the DB server only had to worry about one thing....reading and writing....just how it should be. =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
