CDS T-SQL endpoint pt 4 – Performance

A common question I’ve seen so far about the new CDS T-SQL endpoint is how it compares performance-wise to using FetchXML. After all, FetchXML is “native” to CDS while T-SQL might be viewed as the johnny-come-lately compatibility layer, so it’s going to be slower, right?

To check it out I set up a simple test. I’ve loaded about 60,000 lead records and used SQL and FetchXML to retrieve them.

The (abbreviated) test code looks like this:

private void SQL()
{
  using (var con = new SqlConnection("<connectionstring>"))
  {
    con.Open();

    using (var cmd = con.CreateCommand())
    {
      cmd.CommandText = "SELECT leadid, firstname, lastname, companyname, emailaddress1 FROM lead";

      using (var reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
          var leadId = reader.GetGuid(0);
          var firstName = reader.GetString(1);
          var lastName = reader.GetString(2);
          var companyName = reader.GetString(3);
          var emailAddress = reader.GetString(4);
        }
      }
    }
  }
}

private void FetchXML()
{
  using (var org = new OrganizationServiceProxy(...)
  {
    var fetch = @"
      <fetch page='1' paging-cookie=''>
        <entity name='lead'>
          <attribute name='leadid' />
          <attribute name='firstname' />
          <attribute name='lastname' />
          <attribute name='companyname' />
          <attribute name='emailaddress1' />
        </entity>
      </fetch>
    ";
    var xml = new XmlDocument();
    xml.LoadXml(fetch);
    var page = 1;

    while (true)
    {
      var result = org.RetrieveMultiple(new FetchExpression(xml.OuterXml));

      if (!result.MoreRecords)
        break;

      xml.SelectSingleNode("/fetch/@page").Value = (page++).ToString();
      xml.SelectSingleNode("/fetch/@paging-cookie").Value = result.PagingCookie;
    }
  }
}

I then ran each of these once, untimed, to allow the .NET runtime to do its initialisation thing so the results weren’t skewed by one-time startup costs.

Next I ran each one 10 times, timing each one, and took the average. The results are very encouraging:

Method	Avg. Time
SQL	5.758 sec
FetchXML	8.358 sec

So in this simple test, SQL comes out about 1.5x faster!

Now this is not quite fair, as the SqlConnection is automatically pooled so we don’t actually have any overhead in connecting to SQL on each attempt, but with the FetchXML test we had to open and close the connection each time. If we move the opening of each connection out of the tests so we do those once and only measure the actual query execution and result retrieval time, the results get a lot closer:

Method	Avg. Time
SQL	5.026 sec
FetchXML	8.129 sec

Still an impressive performance for the rookie!

Don’t put too much store in these exact numbers – I’m running these tests from the UK and my test instance is in Canada, which is going to add a lot of network overhead – but I’d expect the ratio between the two versions to be pretty consistent.

Update 2020-08-09 – I’ve added another post with more details on performance characteristics of particular queries, so check out that post for more information!

Other Posts

This is part of a series of posts on the T-SQL endpoint, read more about it:

1. Connecting
2. First Thoughts
3. SqlClient
4. Performance
5. EntityFramework / ORM
6. Aggregates
7. Extensibility
8. Security