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.

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.