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
Aand de-serializes them. - For each item from
A, the client then asks the server for its associated items inB. - For each item from
A, the client then asks the server for its associated items inC.
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
XMLfor N rows from tableA, where each row contains items fromBandC. The XML is then de-serialized to a ready-to-use object inC#.
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