Recursive T-SQL, first impressions
The deal: colleagues have been working on tracking and tracing software. Basically the software can track down all intake operations (raw materials) for a given product. This is what we call Upstream Tracing. Also the system can, for a given intake, trace all out loading operations (product) Downstream Tracing. The software is written in VB6, COM+ and SQL Server 7. To sum it up: The software is slow and extremely unstable so I’ve been asked to share my knowledge because of my experience with a high performance tracking and tracing solution written in C# (Which I actually architected, designed and coded myself).
First thing I noticed while testing where the timeouts thrown by Microsoft Transaction Server. For the sake of standard my colleagues decided to use their generic transactional common component (an abstraction layer over ADO). Transactions for read only operations? Huh I said… Next thing I noticed where the extreme amount of call’s through the layers with absolutely no “extra” logic inside. Huh… Also the VB6 runtime had a hard time with filtering, muting and hustling intermediate results… recursion caused nasty stack problems.
For the sake of the length of this article:
1. I added a wrapper for calling stored procedures via the common component
2. I added a business layer with a few methods calling methods upon the common component and shaping my recordsets the way I like them to be for my presentation logic. Added some business rules etc.
3. I wrote a few stored procedures (T-SQL) encapsulating my new algorithm (recursion)
4. Done some prototyping for the user interface, but that’s just to showoff management (these guys just don’t get what’s under the hood if they can’t catch a glimpse of the hood itself)
1. Speed improvements, previous: +14:00:00h computing power, current: 02:56:00h, previous 00:06:00h, current: 00:00:04h
2. Stability… the system is rock stable because of the raw power SQL Server provides in T-SQL. The VB6 runtime can concentrate on other stuff, staying stable. The whole algorithm logic is as close as it can get to the hardware
3. A nice, clean and well structured n-tier application
I still got to do some scalability tests to figure out what the roof of my algorithm is and some experienced people to do a proper code review. My VB isn’t what it used to be, hehe. Can’t wait to do some customer acceptance tests, but I guess I’ll be on my way by then. The next problem awaits me…bring it on!