T-SQL: INSERT with XML shredding is very slow

Back in 2010, I came across a performance bug with SQL2008 SP1. When shredding XML using the nodes query, the performance is fine with a SELECT but when you want to INSERT the data to a table/temptable/tablevariable the performance becomes very poor.

The query I was having problems with was something like this (Thanks for the sample data wBob!):

DECLARE @xml XML;
-- Spin up some test data 
WITH cte AS 
( 
SELECT 65 x 
UNION ALL 
SELECT x + 1 
FROM cte 
WHERE x < 90 ) 

SELECT @xml = (
    SELECT CHAR( a.x ) + ' & co.' AS "name"
    a.x AS "value"
    'FIELD' AS "paramdata/field"
    FROM cte a 
      CROSS JOIN cte b 
      CROSS JOIN cte c
    FOR XML PATH('root'), TYPE
    ) 

DECLARE @ConfigData TABLE (ID INT, Name NVARCHAR(255), Value NVARCHAR(255), ParamData XML)

INSERT INTO @ConfigData (ID, Name , Value, ParamData) 
SELECT 1,
    tbl.cols.value('name[1]', 'varchar(1000)'),
    tbl.cols.value('value[1]', 'varchar(1000)'),
    tbl.cols.query('./paramdata[1]')
FROM @XML.nodes('//root') AS tbl(cols) 
GO

I raised this with MS via Connect (#562092) and went on to implement my own workaround using the following CLR shredding function:

public partial class UserDefinedFunctions
{
    [SqlFunction(FillRowMethodName = "tvf_clr_FillParameterData",
        TableDefinition = "Name nvarchar(255), Value nvarchar(255), ParamData nvarchar(255)")]
    public static IEnumerable tvf_clr_ParameterDataShredder(SqlXml parameterData)
    {
        XmlDocument document = new XmlDocument();
        document.LoadXml(parameterData.Value);
        return document.SelectNodes("//parameter");
    }

    public static void tvf_clr_FillParameterData(object row, out string outName, out string outValue, out string outParamData)
    {
        XmlNode document = (XmlNode)row;

        outName = document.SelectSingleNode("name").InnerXml;
        outValue = document.SelectSingleNode("value").InnerXml;

        outParamData = null;
        if (document.SelectSingleNode("paramdata") != null)
            outParamData = document.SelectSingleNode("paramdata").OuterXml;
    }
};
 
This served me well and solved the performance issue and all was fine. Until now.

The thing is, this method doesn't handle decoding of escape characters in XML, so any &s that exist in the XML string being shredded will be returned as &:amp. Not ideal. My first thought was to just implement a decoding function within the CLR routine but the main candidates use System.Web which is not available within SQL Server. I eventually stumbled upon this post from Jonathan Keyahias which provided a sql safe Encoding implementation and I was going to use this as a basis for writing my own decoding function. While waiting the 20mins for Visual Studio 2008 to open, it occured to me to revisit the Connect case and fortunately, there were a number of workarounds there, and a combination of the solutions the most effective.

INSERT INTO @ConfigData (ID, Name , Value, ParamData)
SELECT     1,
    tbl.cols.value('(name/text())[1]', 'nvarchar(255)'),
    tbl.cols.value('(value/text())[1]', 'nvarchar(255)'),
    tbl.cols.query('./paramdata[1]') 
FROM @xml.nodes('//root') AS tbl(cols) 
OPTION ( OPTIMIZE FOR ( @xml = NULL ) )

The above simple modification to my SQL INSERT statement and performance was acceptable and more importantly, the decoding of the escaped characters was fine. Saved me a lot of work.

Visual Studio: Change default browser

When debugging web apps in Visual Studio, the browser it throws up is typically your default browser. Makes sense I guess. However, once you have many browsers on your machine you may wish to change this.

This is the scenario I cam up against the other day and thanks to this article, I managed to do find a way.

Essentially, you need to right click on an ASP.NET (.aspx) page in your project, right click and select "Browse With..." and change the default browser.

 

NB: if you are developing an MVC with Razor, I found you had to just add an .aspx file to the project to achieve this goal then remove it.

SSRS: Link to report URL directly

In writing a quick and simple app the other day, I had a requirement to link directly to a report hosted on a Reporting Services instance. The tricky part was working with directories and parameters for the report.

It was for a report called "Product Detail" in a directory called "Product Reports" taking a single parameter called ProductID. The following seemed to work.

http://myServer/ReportServer_INSTANCE/Pages/ReportViewer.aspx?%2fProduct+Reports%2fProduct+Detail&rs:Command=Render&ProductID=24

Denali: SSMS has Improved look and feel

One of the first things I noticed with the new version of SQL Server (Denali) was the changes to Management Studio and the development experience. While not as ground breaking as the move from Query Analyzer to SSMS when SQL 2005 was released, there is definitely further movement towards a common development platform for programmers.

Here is the initial splash page you get when booting up SSMS:



The main thing I noted here was the comment in the bottom right corner which explicitly states "Powered by Visual Studio". To me, this is a clear indication of the direction MS is heading with its database toolset.

And here we have a shot of what using SSMS for development looks like:



Notice here how we have much more varied colour coding for keywords, specifically variables and object names. This is a vast improvement on the generic black text in previous versions. Another feature you can see here is the bottom left of the shot above the results pane - a zoom dropdown allowing you to quickly increase the size of the text in your query window. There is also one for the results pane, although this shot doesn't show it.

Development Environment Convergence

We've already seen Visual Studio creeping into the SQL Server arena with the Business Intelligence Development Studio (BIDS) which is essentially the VS shell with SQL Server plugged in and I can see MS trying to consolidate their development environments still further. This makes a lot of sense for software devs who will often be developing database enabled applications and need to flit between their .Net code and database code. Having a more uniform and familiar environment makes the experience that much smoother and hopefully more efficient.

SSRS: Full report not rendered via ReportExecutionService

I've come across an issue when using the ReportExecutionService to render a report through a .Net App using code similar to that demonstrated here.

Essentially, I'm trying to render a report which contains 3 layers of nested subreports all using different datasets. When viewing the report through the SSRS report viewer, you get a question mark on the paging section of the toolbar:



This question mark is a result of the On-demand processing engine introduced in SSRS 2008 and basically means that the report is rendered in stages, giving a potential performance gain on large reports. The question mark indicates that it knows there is more to come, just not sure exactly how much.

So, as I understand it, if you have a report with 2 subreports accessing different datasets - only the first one will be run to minimise the data sent to the client. When user starts navigating through the report and comes to the end of the first report, the rest of the report will be rendered. Rob Bruckner explains more here.

All well and good from a performance perspective and typically doesn't cause any issues apart from the strange question mark appearing (even this can be worked around) and users just navigate through the report. However, when trying to access it through the webservice, the same behaviour is honoured and only the first section of the report is rendered - equivalent to the first page when viewing through the SSRS report viewer. This is incredibly frustrating as I know that the report is over 3 pages long but I only ever get the first page in my deliverable. Worse still, there doesn't seem to be a way to override this behaviour (its the same in R2 I believe) and something i'd like to see addressed by MS (on a report by report basis).

At first, I was stumped as even using the TotalPages workaround referenced above, I couldn't get the full report to render. But, then I took a closer look a the "master" reports and noticed that they all had orphaned data sources defined - a legacy from when the reports held definitions rather than subreports. As a last shot, I removed these from the reports as they weren't needed at all and was delighted to see that the full report now rendered correctly through the webservice.

I'm not clear exactly why these orphaned connection strings were causing such an issue but all I know is that clearing them did the job.

T-SQL: Table Valued Parameters - "adios" to the Split function

A very common problem that developers have faced is the ability to pass in arrays to SQL Server and although there are various options available for editions up to SQL2005, the arrival in SQL2008 gave us Table Valued Parameters which allows a much neater way of passing in multiple values.

Here is an example using a simple c# console app to add multiple values to a database table. The key part to note here is that the input parameter needs to be set as READONLY and within the c# code you need to pass in a datatable as the parameter.


-- set up the objects
USE tempdb
GO
-- your table type
CREATE TYPE dbo.string_list_tbltype AS TABLE (nm NVARCHAR(255))
GO
-- your table
CREATE TABLE dbo.tblBeatles (Nm NVARCHAR(255), Dt DATETIME)
GO
-- the insert procedure
CREATE PROCEDURE dbo.AddBeatlesMembers
@Members dbo.string_list_tbltype READONLY
AS
INSERT INTO dbo.tblBeatles
SELECT nm, GETDATE()
FROM @Members
GO


And now the c# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace Music
{
    class Program
    {
        static void Main(string[] args)
        {
            UseTVP();
        }

        public static void UseTVP()
        {
            DataTable BeatlesTable = new DataTable();
            BeatlesTable.Columns.Add("Name", typeof(string));
            
            // Fill with row
            BeatlesTable.Rows.Add("John");
            BeatlesTable.Rows.Add("Paul");
            BeatlesTable.Rows.Add("Ringo");
            BeatlesTable.Rows.Add("George");

            using (SqlConnection conn = new SqlConnection("Server=MyServer;Database=tempdb;Trusted_Connection=True;"))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "dbo.AddBeatlesMembers";
                SqlParameter param = cmd.Parameters.AddWithValue("@Members", BeatlesTable);

                conn.Open();
                cmd.ExecuteNonQuery();
            }

        }
    }
}

There is also an MSDN article that explains this in more detail.