XML : Fastest way to fetch complex data with many-to-many relationships?

Running SQL Server 2014. I have multiple tables with many-to-many relationships with other tables. Many times, I need to fetch N rows from table A, also showing items table B and C. I want to do this as efficiently as possible.

What is the most efficient way of doing this? Some ideas below.


Note: The client and server are not necessarily on the same network.

The naive approach

The naive approach looks something like this:

  • Client asks server for N rows from table A and de-serializes them.
  • For each item from A, the client then asks the server for its associated items in B.
  • For each item from A, the client then asks the server for its associated items in C.

This leads to a crazy amount of database round trips, causing severe performance problems when on a slow network (i.e. WAN). It's simply not an option.

The XML approach

By letting SQL Server generate XML, we can deliver structured data to the client.

  • Client asks server for an XML for N rows from table A, where each row contains items from B and C. The XML is then de-serialized to a ready-to-use object in C#.

It could look something like this:

  <data>    <a_collection>      <a>        <id>1</id>        <title>A Title<title>        <b_collection>          <b>            <id>123</id>            <description>B stuff here</description>          </b>          <b>            <id>124</id>            <description>Other B stuff here</description>          </b>        </b_collection>        <c_collection />      </a>    </a_collection>  </data>    

I like this approach, but it's slow. As the number of rows increases, and the relationships become more complex, the XML serialization on SQL Server becomes slow. Are there ways to somehow improve the XML serialization in terms of CPU and RAM usage?

The JSON approach

When SQL Server 2016 is released, we will have the option to use JSON instead of XML. Maybe the XML approach above can then be converted to JSON and possibly benefit from a faster serializer? But how would you de-serialize the objects when you can no longer benefit from System.Xml.Serialization?

The WCF approach

Creating an extra layer between the client and the database server seems like a good idea. How would such a solution compare to i.e. the XML approach?

Other approach?

There ought to be other efficient ways of delivering structured data from an SQL Server to a client.

No comments:

Post a Comment