Which is faster? Loading XML from disk or from SQL Server?

I'm going to geek out on you for a minute. I may start doing this abit more from time to time. Anyway...

We had an interesting discussion at work. Which is faster? Loading an xml document from hard disk or loading it from SQL Server. I had assumed it would be faster to load from HDD but never one to assume - I decided to let science do the work.

So I built up a little console app. It simply displays the time after loading an xml file from either hard disk or sql server.

Bottom line? Based on my tests, loading from disk is 4x faster. Even if sql server has your query cached, so it doesn't have to pull the data from disk, it's still 4x faster to load from HDD. Why? Because of all the API's you have to pass that data through just to get the data across the wire. In fact, I gave sql server the benefit of the doubt, loading the xml file into a table with only 1 column and 1 row, running the query over and over so it was cached, and finally the data is sitting local to the same machine the app is on. If the data was buried in a large table on a remote sql server, I'm sure the local copy would be even faster.

Load from HDD console output:

Starting ... [9/25/2006 9:33:56 AM]
Finished ... [9/25/2006 9:33:56 AM]
Elapsed time: 00:00:00.0156253

5 attempts:

00:00:00.0156253
00:00:00
00:00:00.0156253
00:00:00
00:00:00.0156253

Load from SQL Server console output:

Starting ... [9/25/2006 9:34:40 AM]
Finished ... [9/25/2006 9:34:40 AM]
Elapsed time: 00:00:00.0937518

5 attempts:

00:00:00.0781265
00:00:00.0625012
00:00:00.0937518
00:00:00.0781265
00:00:00.0781265

C# code:

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Text;

   4:  using System.Xml;

   5:  using System.Data.SqlClient;

   6:   

   7:  namespace LoadXml

   8:  {

   9:      class Program

  10:      {

  11:          static void Main(string[] args)

  12:          {

  13:              DateTime startTime = DateTime.Now;

  14:              Console.WriteLine("Starting ... [{0}]", startTime);

  15:   

  16:              //LoadFromDisk();

  17:   

  18:              LoadFromSqlServer();

  19:   

  20:              DateTime endTime = DateTime.Now;

  21:              Console.WriteLine("Finished ... [{0}]", endTime);

  22:              TimeSpan timeSpan = endTime - startTime;

  23:              Console.WriteLine("Elapsed time: {0}", timeSpan);

  24:          }

  25:   

  26:          private static void LoadFromSqlServer()

  27:          {

  28:              XmlDocument xmlDocument = new XmlDocument();

  29:   

  30:              using (SqlConnection sqlConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=tempdb;Integrated Security=SSPI;"))

  31:              {

  32:                  SqlCommand sqlCommand = new SqlCommand("select [xmldocument] from [dbo].[sqlxml]", sqlConnection);

  33:   

  34:                  sqlConnection.Open();

  35:   

  36:                  // use a reader since it's the fastest way to read data from sql server

  37:                  SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

  38:   

  39:                  sqlDataReader.Read();

  40:   

  41:                  // use column ordinal instead of column name for faster access

  42:                  string xmlString = sqlDataReader[0].ToString();

  43:   

  44:                  xmlDocument.LoadXml(xmlString);

  45:              }

  46:          }

  47:   

  48:          private static void LoadFromDisk()

  49:          {

  50:              XmlDocument xmlDocument = new XmlDocument();

  51:              xmlDocument.Load("Sample.xml");

  52:          }

  53:      }

  54:  }

XML file:

 1 <?xml version="1.0" encoding="utf-8" ?>
 2 <books>
 3     <book>
 4         <author>Carson</author>
 5         <price format="dollar">31.95</price>
 6         <pubdate>05/01/2001</pubdate>
 7     </book>
 8     <pubinfo>
 9         <publisher>MSPress</publisher>
10         <state>WA</state>
11     </pubinfo>
12 </books>

SQL Server Script to create the table and data:

 1 /* Create the table in tempdb and load the xml into it */
 2 use [tempdb]
 3 go
 4 
 5 if  exists (select * from sys.objects where object_id = object_id(N'[dbo].[sqlxml]') and type in (N'u'))
 6 drop table [dbo].[sqlxml]
 7 go
 8 
 9 create table [sqlxml]
10 (
11     [xmldocument] nvarchar(2000) not null
12 )
13 
14 insert into
15     [sqlxml]
16     (
17     [xmldocument]
18     )
19 values
20     (
21 '<?xml version="1.0" encoding="utf-8" ?>
22 <books>
23     <book>
24         <author>Carson</author>
25         <price format="dollar">31.95</price>
26         <pubdate>05/01/2001</pubdate>
27     </book>
28     <pubinfo>
29         <publisher>MSPress</publisher>
30         <state>WA</state>
31     </pubinfo>
32 </books>'
33     )