I think the presentation started off a little shaky for a couple of reasons.  I had sent out an email requesting additional info to help prep for the presentation – however, I sent it late which impacted the feedback.  I also didn’t review the PASS Monthly slide deck and sort of shot from the hip going through that stuff.

Despite that, the presentation went well.  I had several people give good feedback.  They liked the presentation!!  Participation really went well and it felt more like a conversation than a presentation – that is a good thing.

Probably the best thing to come from the presentation was the resounding support in favor of trying to get a SQLSaturday down here.  With that info, it looks like I will be starting the process to host a SQLSaturday and organize it from our side.

Another benefit to be derived from this presentation is a growing warmup that has been happening with the group.  Each presentation I have given has improved in group participation.  That would be coming from both sides – I am sure.  Going back to some of my early blogs about participating in the Local User’s Groups, I want to reiterate how good it feels to participate.  I am glad I have had the opportunities to present.

As far as the survey, I think we got it wrong by not working harder at having unbiased questions, and we got it wrong by not understanding that it’s damned difficult to justify a decision that appears to be opposite of what the survey says. We were slow to put out the summary data, and I still hope we’ll release the raw data. We also didn’t make the effort to do some follow up polling to clarify the ranking of what was most important – location or MS participation at a super high volume?

The decision to stay in Seattle really came down to a single point – Microsoft presence. Not cost, not venue, not whether the Summit would work in other cities, none of that was (in my opinion) even close to a deciding factor.  Microsoft doesn’t “make” us hold the event in Seattle. Their public stance is pretty fair, we budget x dollars for the event, if you hold in Seattle we can send more people than if you hold it elsewhere, the difference being travel costs.

From the PASS perspective that increased level of participation gives us:

• A marketing bullet point, a way to be seen as better/different than other conferences
• Increased attendance (MS people that is) which makes the conference “look” more successful
• Value to our attendees who have a chance to meet people on the SQL team outside of the usual presenters

I balance that against wanting to reach people in other areas that we just won’t reach if we stay in Seattle. I know there’s been a lot of discussion about not much different in price to fly to one place or the other, but as an East coast guy I can tell you that it’s time – the time difference ends up adding a day each way to my trip, and that’s two days of work, or two days of billable hours for some. So, the question is, does the desire to reach different areas out weight the benefits I listed above?

I think it does. We can still trumpet our great speaker lineup (including many from MS). Our attendance may drop due to fewer MS employees. Can we offset that with gains? I don’t know, but I can live with having perhaps 300 fewer attendees if it comes to that (and note, those aren’t full price attendees). The value to our attendees is important, and while I hate to diminish that, I know that we’ll still get the top 50 or top 100 MS people at the event, and then the next year or year after we’d get the whole crew again.

I don’t mean to diminish the value that having developers from the SQL team participate in our event brings. It’s not just what we might learn from them, it’s what they learn from us. Our problems, our wishes, our passion. I think it’s one of the most important things we can do is provide a way for them to interact with us. I just don’t think it has to be every year.

I voted against having it in Seattle in 2011 and 2012. I think a once in three year rotation would be fair, giving us the ability to host one in the middle of the country (Chicago, Dallas, Denver). I voted that way because I thought it was best for the community and that it would not cause any serious damage to PASS or the PASS Summit.

I think the decision to remain in Seattle reflects the disparity many of us have seen in PASS from the beginning. PASS is more a business than a community. We should make good business decisions, protect the organization so that it lives on, but past that, too often we fail to make decisions that might be in the interest of the community more than in the business.

I’m pleased to see more people speaking out, even if they don’t agree with me. We need that outside influence, especially when it’s focused on trying to do good and find compromises that work. But, speaking as an insider, and as perhaps a tired Board member, I’ll also say that if you want real change, you’re going to have to field some Board candidates that share your views of what PASS should be.

I’ll post again soon about the upcoming elections and some thoughts about my term so far, in hopes of persuading the best and brightest of you to come serve your community for two years in a very visible way.

SharePoint 2010 is 64-bit only. It requires Windows Server 2008 SP2 or Windows Server 2008 R2. It will not run on a Server Core installation.

It requires one of these versions of x64 SQL Server:

                SQL Server 2005 SP3 CU3 (Build 4220) or greater (Note: SQL Server 2005 goes out of mainstream support in April 2011)

                SQL Server 2008 SP1 CU2 (Build 2714) or greater

                SQL Server 2008 R2

Microsoft’s official position is still that SQL Server 2008 R2 will be available in the first half of 2010, even though the SharePoint team has announced a May 12 launch date.  If I were planning on a brand new SharePoint 2010 deployment, I would want to be running on Windows Server 2008 R2, using SQL Server 2008 R2. This will give you the best performance and the most functionality. For example, PowerPivot in SharePoint 2010 requires SQL Server 2008 R2.

I have put together some sample code that shows how to determine what edition and version of SQL Server is installed on an instance. This makes it easier to take advantage of version specific features (such as data compression) and edition specific features (such as online index creation) where appropriate.

-- Method 1: Retrieve complete version info, parse results in C#SELECT @@VERSION AS [SQLVersionInfo];-- Method 2: Detecting the edition and version of SQL Server from T-SQLIF SERVERPROPERTY('EngineEdition') = 3BEGIN
PRINT 'Enterprise Edition'IF CONVERT(INT, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('productversion')))) > 9BEGIN
PRINT 'SQL Server 2008 or greater';-- Do something like use data compression on an indexEND
ELSE
BEGIN
PRINT 'SQL Server 2005 or lower';-- Do something like create an index in online modeEND
END
ELSE
BEGIN
PRINT 'Standard Edition';-- Create an index in offline modeEND

There have been a fair few forums questions in recent months asking for help in removing index scans, loop joins, sorts or other, presumed, slow query operators. There have been just as many asking how to replace a subquery with a join or a join with a subquery or similar aspects of a query usually for performance reasons.

The first question that I have to ask when looking at requests like that is “Why?”

Why is removing a particular query operator the goal? Why is changing a where clause predicate the goal? If it’s to make the query faster, has the query been examined and has it been confirmed that query operator or predicate really is the problem?

The title of this post refers to a comment I’ve seen again and again in blogs or articles about front-end development. “Premature optimisation is the root of all evils.” It’s true in the database field as well.

While optimisation is very important in database development, trying to optimise queries without any idea where the problem with the query is, or even if the query is a problem at all is about as effective in fixing a database performance problem as using a shotgun from 100 meters is in killing mosquitoes. If you hit the problem, it’s by shear luck and nothing else.

There’s two sides to this problem.

The first aspect of this is, during development, spending time on optimising a query (or stored procedure) without any idea whether or not the query is inefficient and no idea whether or not the changes made make any improvement or not.

Firstly this is a waste of time that could be better spent developing other queries. Second it creates an incorrect impression that the queries have been optimised when in fact nothing of the sort has been done.

The second aspect when, with a production database that is performing badly, queries are modified almost at random in an attempt to fix the performance problem quickly.

This almost never works. It wastes time fixing stuff that very likely isn’t broken in the first place all the while the database performance deteriorates and management curses SQL Server as ‘nonscalable’

So, what is the right approach for the above two scenarios?

1. Don’t optimise queries without knowing if they need it.
2. Don’t optimise queries without knowing if they need it. ¹

New development

When writing queries and stored procedures they need to be tested against a representative data set on a server with representative workload and their performance characteristics evaluated to see if they are acceptable. If the query’s performance characteristics are acceptable, then that query requires no optimisation²

This doesn’t mean write bad code and push it to production. It means write good, solid code, following accepted coding standards, ensure that it runs acceptably against production-volumes of data, and do not spend hours or days trying to get it running a couple of milliseconds faster.

And if the query doesn’t perform acceptable, identify the problematic portion and fix that, don’t flail around rewriting bits of the query in the hope that the problem will magically go away.

The execution plan is the primary tool here, along with the output of Statistics IO.

Fixing existing code

When evaluating existing databases with know performance problems, limit the performance tuning to queries that really are performing badly and need optimisation. It’s often true that fixing the top 5-10 worst performing queries will have massive effects in overall system performance, far more than tuning twice that number of queries that aren’t really a problem.

The best tool for finding which queries really are the worst offenders is SQL Trace.

When looking at queries that are a problem, identify the portions that are inefficient and target attempts at optimisation towards those problems.

In conclusion

Measure Twice.
Optimise if necessary.

(1) No, that wasn’t a typo.

(2) At that time. Later changes to schema or data volume may require existing queries to be revised.

For more details on exactly how to identify problematic queries, refer to the series I wrote at Simple Talk last year.

The challenge with XML processing is to not need a huge amount of memory. I remember using the early versions of Biztalk with loaded the whole document into memory to map from one document type to another. This was fine for small documents but was an absolute killer for large documents.

You therefore need a streaming approach.

For flexibility however you want to be able to generate your rows easily, and if you've ever used the XmlReader you will know its ugly code to write.

That brings me on to LINQ. The is an implementation of LINQ over XML which is really nice. You can write nice LINQ queries instead of the XMLReader stuff. The downside is that by default LINQ to XML requires a whole XML document to work with. No streaming.

Your code would look like this. We create an XDocument and then enumerate over a set of annoymous types we generate from our LINQ statement

XDocument x = XDocument.Load("C:\\TEMP\\CustomerOrders-Attribute.xml");

foreach (var xdata in (from customer in x.Elements("OrderInterface").Elements("Customer")

                      from order in customer.Elements("Orders").Elements("Order")

                      select new { Account = customer.Attribute("AccountNumber").Value

                                  , OrderDate = order.Attribute("OrderDate").Value }

                      ))

{

   Output0Buffer.AddRow();

    Output0Buffer.AccountNumber = xdata.Account;

    Output0Buffer.OrderDate = Convert.ToDateTime(xdata.OrderDate);

}

As I said the downside to this is that you are loading the whole document into memory.

I did some googling and came across some helpful videos from a nice UK DPE Mike Taulty http://www.microsoft.com/uk/msdn/screencasts/screencast/289/LINQ-to-XML-Streaming-In-Large-Documents.aspx. Which show you how you can combine LINQ and the XmlReader to get a semi streaming approach. I took what he did and implemented it in SSIS. What I found odd was that when I ran it I got different numbers between using the streamed and non streamed versions. I found the cause was a little bug in Mikes code that causes the pointer in the XmlReader to progress past the start of the element and thus

foreach (var xdata in (from customer in StreamReader("C:\\TEMP\\CustomerOrders-Attribute.xml","Customer")

                              from order in customer.Elements("Orders").Elements("Order")

                              select new { Account = customer.Attribute("AccountNumber").Value

                                          , OrderDate = order.Attribute("OrderDate").Value }

                              ))

       {

           Output0Buffer.AddRow();

           Output0Buffer.AccountNumber = xdata.Account;

           Output0Buffer.OrderDate = Convert.ToDateTime(xdata.OrderDate);

       }

These look very similiar and they are the key element is the method we are calling, StreamReader. This method is what gives us streaming, what it does is return a IEnumerable list of elements, because of the way that LINQ works this results in the data being streamed in, it returns the elements one at a tiem rather than building a collection of them. The key is the use of the IEnumerable and the "yield return"

static IEnumerable<XElement> StreamReader(String filename, string elementName)

{

    using (XmlReader xr = XmlReader.Create(filename))

    {

       xr.MoveToContent();

       while (xr.Read()) //Reads the first element

       {

           while (xr.NodeType == XmlNodeType.Element && xr.Name == elementName)

           {

               XElement node = (XElement)XElement.ReadFrom(xr);

               yield return node;

           }

       }

       xr.Close();

    }

}

This code is specifically designed to return a list of the elements with a specific name. The first Read reads the root element and then the inner while loop checks to see if the current element is the type we want. If not we do the xr.Read() again until we find the element type we want. We then use the neat function XElement.ReadFrom to read an element and all its sub elements into an XElement. This is what is returned and can be consumed by the LINQ statement. Essentially once one element has been read we need to check if we are still on the same element type and name (the inner loop) This was Mikes mistake, if we called .Read again we would advance the XmlReader beyond the start of the Element and so the ReadFrom method wouldn't work.

To get this working,

1. Put a script component in your data flow as a source component.

2. Then add the columns you wish to the output.

3. Add the StreamReader function to your script code

4. Put the first foreach in the CreateNewOutputRows method

4. Change the LINQ query etc to match what you want

With the code above you can use what ever LINQ statement you like to flatten your XML into the rowsets you want. You could even have multiple outputs and generate your own surrogate keys.