Dynamic Connection Strings With SSRS Data Processing Extension

It is my firm opinion that whoever came up with the names for various parts of SQL Server must be fired. "SQL Server Reporting Services", "SQL Server Analysis Services", "SQL Server Service Broker" do not exactly roll off the tongue. Just try using a few of these in a speech - you will immediately realize you need nicknames (SSRS sounds too much like USSR. Oh, well, I'll just call it Kevin). Anyway, this post wasn't supposed to be a rant, so let's move on.

SSRS supports ten data sources out of the box, including SQL Server (duh!), ODBC, OLE DB, and Oracle. Data Processing Extensions are usually recommended when you need to generate reports from non-standard data sources, for example, files in a proprietary format. You start by creating a .NET assembly with classes that implement a half-dozen or so interfaces defined in Microsoft.ReportingServices.Interfaces.dll. Once everything is working, you deploy the assembly to two separate locations: Visual Studio subfolder on a report developer's workstation, and a Reporting Services subfolder on a server. The process is well documented on MSDN and there is also a good article on The Code Project.

However, what if your database is a SQL Server, but you cannot rely on a static connection string? Suppose you maintain separate databases for your various customers and generate connection strings at runtime? Although SSRS allows us to use parameterized connection strings, sometimes this isn't an optimal solution, given the fact that those parameters are passed around openly inside URL. I found that Data Processing Extensions can be used very effectively in this scenario.

Rather than implementing all the interfaces required by DPE, we will encapsulate an existing class SqlConnectionWrapper in the Microsoft.ReportingServices.DataExtensions namespace (it is marked as "sealed", so you can't subclass it):

   1:  using Microsoft.ReportingServices.DataExtensions;

   2:  using Microsoft.ReportingServices.DataProcessing;

   3:   

   4:  public class MySqlConnection : IDbConnectionExtension

   5:  {

   6:      private SqlConnectionWrapper _Connection;

   7:   

   8:      public MySqlConnection()

   9:      {

  10:          _Connection = new SqlConnectionWrapper();

  11:      }

  12:  }

Right-click IDbConnectionExtension and choose "Implement Interface". This automatically implements three more interfaces, IDbConnection, IDisposable, and IExtension members. Most of the new methods and properties added to our class will be merely wrappers of the respected methods and properties of the _Connection. For example:

   1:      public string Password

   2:      {

   3:          set { _Connection.Password = value; }

   4:      }

   5:   

   6:      public string UserName

   7:      {

   8:          set { _Connection.UserName = value; }

   9:      }

Of course, you still need to add the implementation.

Property LocalizedName should return the string that you want report developers to see in the list of data sources (e.g., on the "Select Data Source" screen of the new report wizard).

   1:      public string LocalizedName

   2:      {

   3:          get { return "Dynamic SQL Server Connection"; }

   4:      }

Arguably the most important implementation is ConnectionString setter. This is where you need to put your proprietary logic that dynamically generates a valid connection string. There are a couple of different approaches. If you don't need any additional information in order to generate the connection string, ignore the "value" and just call necessary methods:

   1:      public string ConnectionString

   2:      {

   3:          get

   4:          {

   5:              return _Connection.ConnectionString;

   6:          }

   7:          set

   8:          {

   9:              _Connection.ConnectionString = MyDataLayer.GenerateConnectionString();

  10:          }

  11:      }

If, on the other hand, your logic does require parameters, you will need to parse ConnectionString value that client code provided. Example below uses regular expression to extract CustomerID from the value (and skips property getter for brevity):

   1:      public string ConnectionString

   2:      {

   3:          set

   4:          {

   5:              Match m = Regex.Match(value, "CustomerID=([^;]+)", RegexOptions.IgnoreCase);

   6:              int custId = 0;

   7:              if (m.Success

   8:                 && int.TryParse(m.Groups[1].Captures[0].Value, out custId))

   9:              {

  10:                  _Connection.ConnectionString = MyDataLayer.GenerateConnectionString(custId);

  11:              }

  12:              else

  13:              {

  14:                  throw new ArgumentException("Valid CustomerID is missing");

  15:              }

  16:          }

  17:      }

Now the only thing left to do is deploy assembly containing MySqlConnection to both server and developer workstation.

*** UPDATED 11/22/2011 ****

Bad news for those of you using SQL 2008 R2 - the approach of encapsulating Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper I outlined in this post no longer works. Apparently, they changed class accessibility from "public" to "internal". Looks like the only opportunity is to implement all interfaces manually.

Why I Hate Entity Framework

A few months ago I took over development of an insurance-related system. It was a Windows Forms application created for a small workgroup. Original design utilized ADO.NET Entity Framework and it was my first serious encounter with the technology. Immediately, I sensed that something wasn't right: application was too slow. There were only a dozen or so tables and hardly any data in them, yet forms took whole seconds to load. Database updates were even more problematic - not only they took long time, but sometimes they failed for no apparent reason.

Initially, I tried to write it all off as part of a learning curve. I recreated entity model and ran a SQL Profiler in an effort to better understand the technology. Profiler results were simply shocking: instead of executing a half-dozen or so SELECT statements in response to user opening a record, Entity Framework generated hundreds of them (trace file was over 1Mb in size).

So, I remembered the little performance test I did for LINQ to SQL about two years ago and decided to expand it to include Entity Framework. New method looks similar to the one used to test LINQ to SQL:

private TimeSpan RunEntityTest()
{
var swatch = Stopwatch.StartNew();
NorthwindEntities db = new NorthwindEntities(
ConfigurationManager.ConnectionStrings["NorthwindEntities"].ConnectionString);

for (int orderId = 10248; orderId < 11078; orderId++)
{
var query = from o in db.Orders
    where o.OrderID == orderId
    select new
    {
        o.OrderID,
        o.OrderDate,
        o.Customers.CustomerID,
        o.Customers.CompanyName,
        ProdCount = o.Order_Details.Count
    };

foreach (var item in query)
{
    string s = item.CompanyName;
}
}
return swatch.Elapsed;
}

By the way, that original blog post has been criticized by an anonymous guest, who pointed out that my test routines for stored procedures, dynamic SQL and parameterized SQL were not equivalent to LINQ test, because I never read any values from SqlDataReader object after opening it. So, I changed the code in all three methods as follows:

SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string s = Convert.ToString(dr["CompanyName"]);
}
dr.Close();

I'm glad to say that this change didn't make any difference to test results: LINQ to SQL is still 40 times slower than stored procedures or dynamic SQL. But Entity Framework results were much worse: 2.2 times slower than LINQ to SQL. Here is the full table:

METHOD	AVERAGE TIME, ms
Stored Procedure	110
Dynamic SQL	115
Parameterized Dynamic SQL	126
LINQ to SQL	4,699
Entity Framework	10,547

So, in the end, I rewrote the data layer of the application using LINQ to SQL.

Updated test harness code is available here: http://members.cox.net/rmamedov/blog/EntityFrameworkTestHarness.zip

Automatic Deployment of SQL Scripts

1. Background

In my article "Introduction To Change Management" I wrote about the fundamental flaw in the deployment process:

Developers have the first-hand knowledge about the deployment artifacts, but they rarely have security privileges on servers and databases outside of the development environment. DBA's and operations, on the other hand, have permissions and are usually in charge of performing deployments, but they rarely have a good understanding of the deployment artifacts and their relationships.

Suppose we need to deploy a new build of assembly Foo.dll. A method inside it relies on a specific version of stored procedure dbo.Bar, so updated SQL script must be deployed together with the assembly. This is where our process becomes vulnerable to human errors, since IT, DBA, build engineer, and release coordinator all have an opportunity to break the deployment. Take into consideration the sheer number of components and database objects, and you can understand why it usually takes several iterations to make a successful push.

The standard two-track deployment process has other drawbacks, too. For example, it turns DBAs into very expensive clerks as they mindlessly combine scripts together and press "F5" button. Another problem is that it creates a false illusion that database scripts can be safely deployed as a hotfix (while binary code requires regression testing). In reality, both application and database code must be treated as a single entity.


2. Solution

My solution is to bundle all SQL scripts that are required by an assembly with the assembly itself using embedded resources. During deployment, IT engineer will need to invoke standard .NET installation utility:

Installutil.exe <assemblyname>.dll

This invokes a custom installer class which will enumerate script resources in the assembly and execute them in a predefined order.

Although it sounds pretty simple, the above approach has a potential to eliminate a lot of deployment issues, because it gives the developer - someone intimately familiar with implementation details - full control over database deployment process.

It also encourages a modular approach to software design by reducing external dependencies of the assembly.


3. How To Bundle Scripts With Assembly

3.1 Adding resources.

Adding SQL scripts to assembly is a very straightforward procedure. In Visual Studio Solution Explorer, right-click project name and choose "Add Existing Item" from the context menu (it is even better to create a subfolder for these files). After a file was added, open its properties and change Build Action from "None" to "Embedded Resource".

3.2 Marking resources as SQL scripts.

When deploying SQL scripts, order is very important. For example, if you have a stored procedure which selects records from table dbo.Customers, you have to create the table before you can create the stored procedure. So, in order to maintain the order, I created a new custom attribute SqlScriptResourceAttribute:

[AttributeUsage(AttributeTargets.Assembly, Inherited = false, AllowMultiple = true)]
public sealed class SqlScriptResourceAttribute : Attribute
{
   public SqlScriptResourceAttribute(string scriptName, int scriptSequence)
   {
      this.ScriptName = scriptName;
      this.ScriptSequence = scriptSequence;
   }
}

When using the attribute, it is important to specify fully qualified name of the script, which is assembly name + subfolder name (if any) + file name. In the example below, table creation script t_Customer.sql will be executed before other scripts.

[assembly: SqlScriptResource("MyAssembly.pr_Customer_s.sql", 2)]
[assembly: SqlScriptResource("MyAssembly.pr_Customer_i.sql", 3)]
[assembly: SqlScriptResource("MyAssembly.t_Customer.sql", 1)]

3.3 Adding Custom Installer

In order to be able to run installutil.exe against your assembly, you need to add a custom installer to it. This is done using a few lines of code:

using System.ComponentModel;
using System.Configuration.Install;

namespace MyAssembly
{
   [RunInstaller(true)]
   public class MyAssemblyInstaller : Installer
   {
      public MyAssemblyInstaller()
      {
         ScriptInstaller installer = new ScriptInstaller(this.GetType().Assembly);
         this.Installers.Add(installer);
      }
   }
}

All we need to do is to initialize an instance of ScriptInstaller class with the reference to current assembly and add it to the Installers collection.


4. How To Implement ScriptInstaller

ScriptInstaller is a subclass of System.Configuration.Install.Installer. It overrides standard Install(IDictionary stateSaver) method and implements SQL script deployment logic.

4.1 Enumerating Scripts.

I use Reflection to retrieve embedded resource names and list of SqlScriptResourceAttribute instances, then cross-check them to ensure integrity. Finally, I add all script resource names to a SortedList, ordering them by execution sequence.

private SortedList<int, string> BuildScriptList()
{
   SortedList<int, string> scripts = new SortedList<int, string>();
   List<string> resources = new List<string>(_currentAssembly.GetManifestResourceNames());

   object[] attributes = _currentAssembly.GetCustomAttributes(typeof(SqlScriptResourceAttribute), false);
   if (attributes != null)
   {
      foreach (object item in attributes)
      {
         SqlScriptResourceAttribute attrib = (SqlScriptResourceAttribute)item;
         if (resources.Contains(attrib.ScriptName))
         {
            scripts.Add(attrib.ScriptSequence, attrib.ScriptName);
         }
         else
         {
            Context.LogMessage(string.Format("## Script {0} not found in the current assembly.", attrib.ScriptName));
         }
      }
   }
   return scripts;
}

4.2 Loading Scripts.

Loading scripts from the assembly is accomplished using a GetManifestResourceStream method of the Assembly class:

foreach (string scriptName in BuildScriptList().Values)
{
   Context.LogMessage(string.Format("## Installing script: {0}", scriptName));
   Stream resourceStream = _currentAssembly.GetManifestResourceStream(scriptName);
   string sqlScript;
   using (StreamReader sr = new StreamReader(resourceStream))
   {
      sqlScript = sr.ReadToEnd();
   }
   DeployScript(sqlScript);
}

4.3 Deploying Scripts.

Although this is essentially a standard ADO.NET ExecuteNonQuery() operation, there are two potential caveats. First is database security. Whichever way your code normally builds database connection string, it is very unlikely you will be able to use it with DDL scripts. Commonly, you only get "execute" (and possibly "select") permissions at runtime. So, in order to successfully execute deployment scripts, you need to tweak the connection string to establish a more privileged security context. The best approach, in my opinion, is to use SQL Server Integrated Security and assume that the person executing the installutil command has relevant SQL Server permissions.

Second potential pitfall is related to the fact that most deployment scripts contain multiple batches of TSQL separated by the "GO" command, e.g.:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].pr_Customer_s') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].pr_Customer_s
GO

CREATE PROCEDURE dbo.pr_Customer_s

Keyword "GO" is not part of TSQL language definition, so if you try to call ExecuteNonQuery on the above script directly, you will get an exception. What you need to do is split the script into individual batches using "GO" keyword as a guide, and then execute each batch individually:

string[] batches = sqlScript.Split(new string[] { "\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string sqlBatch in batches)
{
   DbCommand cmd = db.GetSqlStringCommand(sqlBatch);
   db.ExecuteNonQuery(cmd);
}

5. Wrapping Up

There are situations where the solution described in this article is either not applicable (for instance, when company ships their software on a CD with a special setup application) or doesn't add value (for example, when database engineers - not .NET programmers - are developing database scripts). But if your change management process separates database scripts from binary code deployment, you may have an opportunity to streamline it. Automated scripts installation will reduce the number of deployment issues, and DBAs will surely feel relieved that they no longer need to perform this mundane task.

LINQ To SQL Performance

In the interest of full disclosure, I must say that I like LINQ. I think the declarative approach to data manipulation is just wonderful! It makes code much cleaner and code maintenance much easier. There is an overhead, of course, but with LINQ to Objects all calls are in-process, so it's still going to be fast.

When it comes to LINQ to SQL, though, the game changes somewhat: not only database access is out-of-process, it is also notoriously tricky. Poorly designed queries can take minutes instead of seconds and drain server resources, so it is very important to know what T-SQL is being generated for a given LINQ query. But that is a topic for another post...

What I really wanted to know is - all other things being equal - how much performance overhead does LINQ to SQL add on top of ADO.NET. So, I built my test harness around one simple query against Northwind database:

SELECT [t0].[OrderID], [t0].[OrderDate], [t0].[CustomerID], [t1].[CompanyName], (
 SELECT COUNT(*)
 FROM [dbo].[Order Details] AS [t2]
 WHERE [t0].[OrderID] = [t2].[OrderID]
 ) AS [ProdCount]
FROM [dbo].[Orders] AS [t0]
INNER JOIN [dbo].[Customers] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
WHERE [t0].[OrderID] = @OrderID

Basically, for a given order ID, we are retrieving a single row with information from order, customer, and order details tables:

OrderID     OrderDate               CustomerID CompanyName                              ProdCount
----------- ----------------------- ---------- ---------------------------------------- -----------
10248       1996-07-04 00:00:00.000 VINET      Vins et alcools Chevalier                3

(1 row(s) affected)

In order to get measurable results, I wanted to run this query for every one of 830 orders (independently, to simulate a multi-user application). For benchmarking, I used three alternative approaches: dynamic SQL, dynamic SQL with parameters, and stored procedure. Because SQL Server caches query execution plans, I restarted it before switching to a different approach.

Dynamic SQL

In the method below, I am concatenating order ID value to the end of the WHERE clause. This technique is known to have poor performance, because SQL Server doesn't realize we are using the same query and will have to compile it every time. Indeed, the initial run took approximately 4100 ms on my laptop. SQL Server caches query plans, though, so subsequent executions of the test yielded a much better result of roughly 200 ms.

private TimeSpan RunADOTest()
{
  using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
  {
     conn.Open();
     SqlCommand cmd = conn.CreateCommand();
     cmd.CommandType = CommandType.Text;
     DateTime dtStart = DateTime.Now;
     for (int orderId = 10248; orderId < 11078; orderId++)
     {
        cmd.CommandText = @"SELECT [t0].[OrderID], [t0].[OrderDate], ...
          WHERE [t0].[OrderID] =  " + orderId.ToString();
        SqlDataReader dr = cmd.ExecuteReader();
        dr.Close();
   }
   return DateTime.Now.Subtract(dtStart);
  }
}

Parameterized Dynamic SQL

The only difference from previous method was that query WHERE clause changed to "WHERE [t0].[OrderID] = @OrderID". I also added these lines before calling cmd.ExecuteReader():

   cmd.Parameters.Clear();
   cmd.Parameters.Add(new SqlParameter("@OrderID", orderId));

As expected, performance has improved. After SQL Server restart, the method completed in 460 ms, and subsequent executions were around 190 ms. It's also important to realize that SQL Server has cached only one query plan and not 830 as in the previous example.

Stored Procedure

Ever since Microsoft added query plan caching for dynamic queries in SQL Server 2000, there really is no performance difference between stored procedure and a parameterized dynamic query. Of course, there are many good reasons for writing stored procedures (greater security, better code reuse, smaller network traffic).

In my test harness, results were nearly identical to parameterized dynamic SQL: initial run (after SQL Server restart) took 453 ms, and subsequent executions took 187 ms.

LINQ To SQL

I dropped Customers, Orders, and Order Details tables to the surface of Visual Studio object relational designer to create a LINQ to SQL classes. In order to ensure that all calls are executed using a single database connection, NorthwindDataContext is initialized with an open SqlConnection object. I also wanted to make sure T-SQL generated by LINQ is the same as in previous tests, so I installed SqlServerQueryVisualizer component and ran a SQLProfiler trace. Indeed, both Parameterized DSQL and LINQ to SQL tests issued the same exact exec sp_executesql command. I had to use query variable in a foreach statement to make it run (because of deferred execution).

Test results were, frankly, disappointing. After SQL Server restarted, the method took 4250 ms. Subsequent executions yielded between 3300 and 3400 ms, or more than 10 times slower than all other tests.

private TimeSpan RunLINQTest()
{
  DateTime dtStart = DateTime.Now;
  using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString))
  {
     conn.Open();
     NorthwindDataContext db = new NorthwindDataContext(conn);

     for (int orderId = 10248; orderId < 11078; orderId++)
     {
        var query = from o in db.Orders
          join c in db.Customers on o.CustomerID equals c.CustomerID
          where o.OrderID == orderId
          select new
          {
            o.OrderID,
            o.OrderDate,
            o.CustomerID,
            c.CompanyName,
            ProdCount = o.Order_Details.Count
          };

        foreach (var item in query)
        {
          string s = item.CompanyName;
        }
     }

     return DateTime.Now.Subtract(dtStart);
  }
}

Test Summary

The table below summarizes test results. As you can see, after initial query plans are cached, dynamic SQL and stored procedure have essentially the same performance. Either approach is 94% faster than LINQ to SQL.

Can't say I'm happy with these results (after all, I do like LINQ), but at this point I don't see what else can be contributing to the delay. Unfortunately, I only have Professional version of Visual Studio which doesn't include a profiler, so I can't research further. However, I welcome any comments or corrections. Source code of the test harness is posted here: http://members.cox.net/rmamedov/blog/LINQ2SQLPerformanceTest.zip.

                   Initial  Subsequent

Dynamic SQL             4100         200

Parameterized
Dynamic SQL              460         190

Stored Procedure         453         187

LINQ to SQL             4250        3350