This is what Avanade calls your time between projects. Last week I rolled off my first major project and started my bench time. This time is meant for training and getting certified. So I did just that.
The first four days of this week I spent training on Microsoft Office SharePoint Server 2007 where I utilized independent study materials that I obtained through Avanade’s internal training department.
Friday was spent craming for Exam 70–528: TS: Microsoft .NET Framework 2.0 – Web-Based Client Development, which I ended up passing on Saturday. This was my second Microsoft exam but my first with Avanade. As part of being a Solution Developer at Avanade you are required to become a Microsoft Certified Professional Developer. This was the first of five exams that make up the MCPD certification. And with five exams, I thought I should better get crackin’.
The other day out at Microsoft I ran into some interesting behavior in SQL Server regarding the difference between running a query in a table-valued function versus running it in a stored procedure.
This particular query I was trying to run selects columns from an existing view that is part a database of the Microsoft product that I am currently working with. Unfortunately, this is no ordinary view. This view not only has two nested select statements, it joins on a table holding thousands and thousands of records. Looking at the estimated execution plan was ridiculous, having probably 50 or 60 processes.
Running this query in a table-valued function, basically just running the view, causes the CPU on the server to soar to 100% and Management Studio to basically timeout. However, placing the same query in a stored procedure yields almost instant results.
I talked this over with the resident SQL guru on the project and all we could come up with was that, because of the complexity of execution plan, the pre-compilation of the query in a stored procedure makes all the difference.