U2U Blog

for developers and other creative minds

Why is ADO.Net so slow?

That was the question I recently got from a customer: they had two systems which send comparable queries to the same SQL Server database, and the system using ADO.Net was 'significantly slower' than the other system.

OK, first thing to do if such a question pops up is to run the profiler to measure the difference. And indeed, we saw two queries retrieving the same data, but the one from ADO.Net was more than a factor 20 slower than the other. Very striking was the huge number of page requests (page reads in profiler, or using SET STATISTICS IO ON): the fast query accessed about 100 pages, the ADO.Net one read more than 15000 pages!

The fast query is

exec sp_executesql N'SELECT * FROM dbo.adonetdemo WHERE trackingstring = ''4E0A-4F89-AE'''

The ADO.Net query is

exec sp_executesql N'SELECT * FROM dbo.adonetdemo WHERE trackingstring = @trackingid',
N'@trackingid NVARCHAR', @trackingid = '4E0A-4F89-AE'

My first guess was: wrong execution plan. Either outdated statistics or wrong execution plans can cause a lot of unnecessary data access... So we looked at the execution plans (in Management studio, Ctrl+M and running the query) and noticed... no immediate difference! Both queries used the same indexes in the same order: first the non-clustered index on trackingstring to resolve the where clause, then doing a lookup in the clustered index to retrieve all the columns on the selected row (select *):

image

The problem identified

The only indication that something weird was going on, was that the cost of the row lookup was estimated at 93% of the cost in the fast query, and only 9% of the cost in the slow ADO.Net query. Only upon closer investigation of the execution plan, we noticed a small but important difference: the slow query did use an implicit operator: converting the trackingstring column (of type varchar) to nvarchar. In other words, it picked the smallest index that contained the varchar column, and converted every string to nvarchar before comparing it with the varchar parameter we provided in the query!

The real problem was the ADO.Net code, which looked like this:


cmd.CommandText = "SELECT * FROM dbo.adonetdemo WHERE trackingstring = @tstring";
cmd.Parameters.AddWithValue("tstring","4E0A-4F89-AE");

Since strings in .Net are unicode, these types are mapped onto NVARCHAR, and if the underlying column happens to be of type VARCHAR, SQL Server will convert the full column to NVARCHAR before using the comparison operator, on every query again! This causes a lot of unnecessary IO, if we know that the input string can easily be written as VARCHAR. In other words: dropping the N from the NVARCHAR variable declaration improved the performance of our query by at least a factor 20!

The solution

There is nothing wrong with ADO.Net if you use it in the right way, but as the above example illustrates, it is easy to shoot yourself in the foot if you don't pay attention. For this example, there are three solutions which would have avoided the problem:

  1. Using stored procedure: if our query was embedded in a stored proc with a VARCHAR parameter, no harm would be done.
  2. Using typed datasets: if we had used typed datasets and/or typed table adapters, these would have queried the table metadata before and would have generated the right queries. Be careful though if you later on change a type from NVARCHAR to VARCHAR without regenerating the typed TableAdapters.
  3. Setting the parameter type in .Net: the Parameter objects in a SqlCommand have a DbType property, with which we can signal the appropriate type:
  4. cmd.CommandText = "SELECT * FROM dbo.adonetdemo WHERE trackingstring = @tstring";
    cmd.Parameters.AddWithValue("tstring","4E0A-4F89-AE");
    cmd.Parameters[0].DbType = System.Data.DbType.AnsiString;

 

Nico Jacobs

Developing a List Definition with the Visual Studio 2008 extensions for WSS

Developing a list definition for a custom list using the Visual Studio 2008 extensions for WSS is not that hard because there is an existing project template for it. But having the custom fields displayed in the several forms (edit form, new form, display form, etc) can be a challenge. This post offers you a walkthrough on how you can proceed.

Lets say you want to develop a list definition for a custom list having fields like CustomerCode, Name, City, Country and StartDate. The custom list must also contain a customer ID but as the list item will inherit from the standard list item, the ID column will automatically be created. The Name column will be the standard Title column. When a new customer is entered, you also want to check if the customer code is not already used for another customer, so you will need to add an event receiver to the custom list.

Open Visual Studio 2008 and choose to create an empty SharePoint project. Add a new list item of type SharePoint List Definition. In the dialog that appears, select Custom List and check both the check boxes.

image

This is the structure that is created by the Visual Studio extensions for WSS. Notice that the necessary forms like the AllItems.aspx, DispForm.aspx, etc are all there. They will need no modification. The schema.xml file contains the definition of the list template and it is in here that you will have to do the work. The instance.xml file will create a list instance based on this custom list definition upon activation of the feature. The ItemEventReceiver.cs file will contain the necessary code for checking the existence of the customer code. The ItemEventReceiver.xml file on its turn will install the event receiver upon activation of the feature.

image

As we want to develop an item event receiver, we can remove the ListEventReceiver.cs and the ListEventReceiver.xml file from the project.

First we are going to define the schema of the Customer list. Open the schema.xml file and locate the <Fields> node.

image

Enter a field element for each column in the custom list. If you copy/paste, don't forget to change the GUID of the field ID because the ID must be unique.

<Field ID="{7809EF2B-C225-4459-BC81-50F9CBF1A244}" DisplayName="Code"
       Type="Text" Required="TRUE" MaxLength="15" StaticName="Code"
       Name="Code" RowOrdinal="0"
       SourceID="http://schemas.microsoft.com/sharepoint/v3" />

This Field element should be added for each column needed in the custom list. Also pay attention to attributes like

- Name: this is the internal name of the field. This field doesn't allow spaces. In the case of Company Name you can use CompanyName or Company_x0020_Name.

- DisplayName: this is the user friendly name of the field and allows spaces.

- StaticName: give it the same value as the Name attribute. 

- Type: this indicates the data type of the field. Possible values are the SharePoint data types like Text, Note, DateTime, Currency, Number,... 

- Required: indicates wether a value is required or not.

At the top of the schema definition you find a ContentTypes element containing one or more ContentTypeRef elements. Remove these elements and add a new ContentType element. This is the definition of the Customer content type.

The ID attribute shows the inheritance: this content type inherits from the Item content type which has ID 0x01. The ID should then be followed by 00 and a new GUID.

It is necessary to define a content type to make the fields appear in the New, Edit and Display form.

<ContentTypes>
      <ContentType
        ID="0x0100247971ABF81E4ac9B96C7C6287D18772"
        Name="Customer Item"
        Group="U2U Content Types"
        Description="Customer item content type."
        Version="0">

        <FieldRefs>

               <!-- Here comes the reference to the fields -->
        </FieldRefs>

      </ContentType>

</ContentTypes>

The FieldRefs element of the content type contains the field references to the fields you defined in the Fields element. The first field is the reference to the Title field which is part of the Item content type.

<FieldRefs>
  <FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Name="Title" Required="TRUE" DisplayName="Company Name" />

  <FieldRef
    ID="{7809EF2B-C225-4459-BC81-50F9CBF1A244}"
    Name="Code"
    Required="TRUE"
    ShowInNewForm="TRUE"
    ShowInEditForm="TRUE"/>

  <FieldRef
    ID="{F5BC3F73-88E0-436e-8C2C-C9819FFE2FE3}"
    Name="City"
    Required="FALSE"
    ShowInNewForm="TRUE"
    ShowInEditForm="TRUE"/>

  <FieldRef
    ID="{D5877137-AB9C-4474-93BD-F99B734436D8}"
    Name="Country"
    Required="FALSE"
    ShowInNewForm="TRUE"
    ShowInEditForm="TRUE"/>

  <FieldRef
    ID="{242BF2C4-6459-45f2-B61C-EF7280A701C6}"
    Name="StartDate"
    Required="FALSE"
    ShowInNewForm="TRUE"
    ShowInEditForm="TRUE"/>
</FieldRefs>

 

The content type also contains a XmlDocuments element which is a collection of XmlDocument elements. An XmlDocument element can contain custom information. In this case it defines the form templates to use when displaying the content type.

<XmlDocuments>
    <XmlDocument NamespaceURI="
http://schemas.microsoft.com/sharepoint/v3/contenttype/forms">
       <FormTemplates xmlns="
http://schemas.microsoft.com/sharepoint/v3/contenttype/forms">
            <Display>ListForm</Display>
            <Edit>ListForm</Edit>
            <New>ListForm</New>
        </FormTemplates>
    </XmlDocument>
</XmlDocuments>

 

Then you have a number of View definitions. To have your fields displayed in the different views, for example the All Items view when viewing the list content, you have to add your custom fields to the ViewFields element of each view.

<ViewFields>
     <FieldRef Name="LinkTitleNoMenu">
     </FieldRef>
     <FieldRef Name="Code" />
     <FieldRef Name="City" />
     <FieldRef Name="Country" />
     <FieldRef Name="StartDate" />
</ViewFields>

 

Now that the list definition is set and done, we can start with the code for the event receiver. When a new customer is entered, you want to check whether the customer code is already used for another customer or not. Open the ItemEventReceiver.cs file and uncomment the ItemAdding event handler:

public override void ItemAdding(SPItemEventProperties properties)
{
    // Check here if the code already exists
    DisableEventFiring();

    string code = null;
    if (properties.AfterProperties["Code"] != null)
    {
        code = properties.AfterProperties["Code"].ToString();
    }

    SPList list = properties.OpenWeb().Lists[properties.ListId];

    if (!ValidateData(list, code))
    {
        properties.Cancel = true;
        properties.ErrorMessage = "This customer code is already in use.";
    }

    EnableEventFiring();
}

 

The ValidateData method is a private method that executes a CAML Query to check whether the customer code already exists or not.

private bool ValidateData(SPList list, string email)
{
   if (email != null)
   {
      SPQuery qry = new SPQuery();
      qry.Query = "<Where><Eq><FieldRef Name='Code' /><Value Type='Text'>"
            + email + "</Value></Eq></Where>";
      SPListItemCollection results = list.GetItems(qry);
      if (results.Count > 0)
          return false;
   }
   return true;
}

 

Time to deploy the list definition. The ListDefinition.xml file contains the definition of the list template. Open the file to modify some metadata. You can change the display name of the list template and set the type to a custom number.

<ListTemplate Name="CustomerListTemplate"
      DisplayName="Customer List Template"
      Description=""
      BaseType="0"
      Type="700"
      OnQuickLaunch="TRUE"
      SecurityBits="11"
      Sequence="410"
      Image="/_layouts/images/itgen.gif" />

 

The instance.xml file defines the list instance that will be created based on your custom list template. Also here you can change f.e. the Title attribute.

<ListInstance FeatureId="bc53ee2c-46b3-41ea-8deb-e75013c92eee"
      Title="Customer List Template instance"
      Url="Lists/CustomerListTemplate">
</ListInstance>

 

Open the project Properties and set the Start browser with URL on the Debug tab to your SharePoint site where you want to deploy your custom list template. Press F5 to start the deploy.

Open an internet browser and navigate to your SharePoint site. The list instance is already created for you. Add a new customer and fill out some data.

image

Add another customer with the same code and you will get an error message indicating that this customer code is already used.

You can download the code here.

Visual Studio 2010 and C# 4.0

It's always an exciting moment when you hear that there is a new version of Visual Studio and C# is on the way. At PDC 08 they announced the release of Visual Studio 2010 and the .NET Framework 4.0 CTP, you can download it here.

As a Silverlight, AJAX and JavaScript fan, it is nice to hear the integration bewteen C# 4.0 and JavaScript. Hope that I can give you some demos and code very soon.

 

Recycling Application Pools in Windows Server 2008

Done it finally: formatted my HP Compaq 8510p and installed Windows Server 2008 64-bit.  I did not have any real problems configuring the system to run MOSS 2007 Enterprise, Visual Studio 2008 and the Office products. Had some problems with the wireless but that was my own stupid fault. You need to add the Wireless feature to the server manager. Nice to see that the OS also starts working heavily with features :). A bit of a disappointment is the no-go for doing a standby of your laptop after you have installed hyper-v. Means that I need to be 5 minutes earlier in the classroom to have my machine booted!

Most of the tools (except the Extensions for WSS) work as a charm. Of course, there are a couple of interesting changes. For example, one of the last lines in my little batch files for the deployment and testing of the dev work is very often the call to the iisapp.vbs to recycle a specific application pool. When you run on Windows Server 2008, that one is not available anymore. After a quick G-search I found what I have to include from now on. Here is the line to recycle an application pool (looks cleaner don't you think?):

C:\Windows\System32\inetsrv\appcmd.exe recycle apppool "sp pool"

Using LINQ to XML in combination with the Lists.asmx

Yesterday I was learning how to work with LINQ. I tried to use LINQ to XML to parse the XML that results from calling methods on the Lists.asmx.

I made a small windows application with Visual Studio 2008 to fill a combo box with lists. When you select a list, a grid view is populated with the some information of the fields of the selected list.

image

The combo box is populated as follows:

XmlNode listsResult = listService.GetListCollection();

// Get the Title and the ID from the xml
XDocument results = XDocument.Parse(listsResult.OuterXml);

var lists = from item in results.Descendants(XName.Get("List",         "http://schemas.microsoft.com/sharepoint/soap/"))
            select new
            {
                Title = item.Attribute("Title").Value,
                Id = item.Attribute("ID").Value
            };

ListsComboBox.DataSource = lists.ToList();
ListsComboBox.DisplayMember = "Title";
ListsComboBox.ValueMember = "Id";

The grid is populated as follows:

XmlNode result = listService.GetList(ListsComboBox.Text);
XDocument results = XDocument.Parse(result.OuterXml);

var fields = from item in results.Descendants(XName.Get("Field", "http://schemas.microsoft.com/sharepoint/soap/"))
             where item.Attribute("ID") != null
            select new
            {
                DisplayName = item.Attribute("DisplayName").Value,
                InternalName = item.Attribute("Name").Value,
                TypeAsString = item.Attribute("Type").Value,
                Id = item.Attribute("ID").Value ?? "empty"
            };

FieldsDataGridView.DataSource = fields.ToList();

What a delight not to have to parse all that XML again!