First off, we need some sample data:

[sourcecode language=”sql”]
DECLARE @Students TABLE (
FirstName nvarchar(50),
LastName nvarchar(50),
DisplayName nvarchar(100)
)
INSERT INTO @Students
SELECT ‘Jim’ as FirstName, ‘Bob’ as LastName, ‘Jim Bob’ as DisplayName
UNION
SELECT ‘John’, ‘Doe’, ‘John Doe’
UNION
SELECT ‘Jane’, ‘Doe’, ‘Jane Doe’
UNION
SELECT ‘Yuri’, ‘Tao’, ‘Yuri Tao’
[/sourcecode]

Now that we have our test data, we can attempt to create an XML string for each distinct row. Ultimately, we want our table to end up like the sample below:

We could try our first option:

[sourcecode language=”sql”]
select * from @Students FOR XML RAW
[/sourcecode]

However, this option returns an XML fragment for the entire dataset – not what we’re looking for here. We need an individual XML fragment for each row.

After searching around on the net and a few forums, I was finally able to get an answer:
[sourcecode language=”sql”]
SELECT *
FROM @Students s
CROSS APPLY
(
SELECT
(
SELECT *
FROM @Students t
WHERE t.DisplayName = s.DisplayName
FOR XML RAW
) x1
)x
[/sourcecode]

The CROSS APPLY function basically creates a Cartesian product. In this case, it iterates over each row in our table and produces the desired XML result. However, if you were to run this query as-is on our sample data, you would notice that the XML output, while formatted as XML, isn’t of an XML datatype.

To fix this, simply convert the column, like so:
[sourcecode language=”sql”]
SELECT s.*, CONVERT(XML,x.x1) as ErrorData
FROM @Students s
CROSS APPLY
(
SELECT
(
SELECT *
FROM @Students t
WHERE t.DisplayName = s.DisplayName
FOR XML RAW
) x1
)x

[/sourcecode]

That’s all there is to it! We now have an XML fragment for each row of data in our sample. As always, test the example for performance bottlenecks. I’ve utilized this 50,000 records and it returns in about 1 minute. Not too bad, given that the records are very wide (40-50 columns).

Later, I will write a post detailing querying and shredding XML data into a relational dataset – good stuff indeed!!

Additional Resources:

• Implementing XML in SQL Server: http://msdn.microsoft.com/en-us/library/ms189887.aspx
• Constructing XML using FOR XML: http://msdn.microsoft.com/en-us/library/ms178107.aspx

What if we take a step back and look at the code itself? Maybe the code is the problem, not the server performance. Since running across SQLServerCentral in the early days of my BI experience, there were a few blog posts and articles which have stuck with me throughout. One such article, More RBAR and “Tuning” UPDATEs, has been of great help to me.

This article opened up my eyes to a completely different way of thinking when it comes to performance problems within SQL Server. I highly suggest reading it before continuing with the rest of my post here.

I ran into this “tuning” problem the other day when working with some Fact records that I was trying to tie to a Type 2 Dimension. I have about 37,000 school enrollment records for which I need to find the appropriate Student ID surrogate key among 273,000 different student records. It seemed pretty simple enough:

• Link the Fact record to the Dimension record using the Student Number
• Based upon the Fact record’s registration date column, place the record with the correct version of the Student

Act 1

There are two different ways to construct our SQL statement to get this job accomplished: either set-based or row-by-row (see RBAR above) Obviously, one is much more preferred above the other method. Take, for example, the code below (RBAR):

UPDATE [PreStage_FactSchoolEnrollment]
SET [AlternateStudentID] =
(SELECT
    (SELECT TOP 1 DS.[StudentID]
    FROM [DimStudent] DS
    WHERE DS.[EffectiveEndDate] >= [FactSchoolEnrollment].[RegistrationDate]
        AND DS.[StudentSISID] = [FactSchoolEnrollment].[Pupil_Number]
    ORDER BY DS.[EffectiveEndDate],DS.[StudentID])
FROM [FactSchoolEnrollment]
WHERE [PreStage_FactSchoolEnrollment].[ID] = [FactSchoolEnrollment].[ID])

Seems innocent enough, right? However, there is a huge performance issue with this query. Below is a screenshot of one particular piece of the actual execution plan from this query above. Remember our record counts: ~37,000 Fact records and ~273,000 Dimension records.

That’s right…that number circled above is over 8 BILLION rows that were created in memory!! (8,465,578,262 to be exact). This is the base problem with RBAR queries. In essence, this query, as it is currently structured, queried and stored the ENTIRE dimension (all 273,000 records) for EACH of the incoming Fact records (37,000). That is where the 8.4 Billion records are created. Notice that this update took over 48 minutes run. There isn’t an index in the world that is going to help this type of performance monster.

Act 2

Enter set-based SQL. How about we reconstruct this query as a set-based query instead? Look at the differences in the SQL below:

UPDATE PreStage_FactSchoolEnrollment
SET PreStage_FactSchoolEnrollment.AlternateStudentID = ISNULL(b.StudentID,-1)

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY StudentSISID ORDER
BY EffectiveEndDate ASC) as rownum, Pupil_Number, sub_b.StudentID
FROM FactSchoolEnrollment sub_a
INNER JOIN DimStudent sub_b ON
    sub_a.Pupil_Number = sub_b.StudentSISID
WHERE (sub_b.EffectiveEndDate >= sub_a.RegistrationDate)) b
WHERE PreStage_FactSchoolEnrollment.Pupil_Number = b.Pupil_Number

This end result of this query is EXACTLY the same as the above query; the only difference is that this query took all of 9 seconds to return data. Now that’s a performance gain!

Followup

Now, understandably, it may not be feasible to rewrite your SQL code because of different constraints. But, if you can, at all, (and I’m pleading with you here), PLEASE try to rewrite the code itself. You will be surprised at how much of a difference syntax can make!

If this had been any other situation, I would have just gone over to my coworker and asked him to give me a copy of the Log file as well (after having chastised him for giving me a bum backup). However, when this situation occurred, we were at two different physical locations, and I didn’t have a way to get over to where he was.

After googling around for a little bit, I ran across a TERRIFIC post that saved my bacon.

Basically what the guy did is created a dummy database, took SQL offline and swapped the MDF files out, brought SQL back online (effectively putting the database into SUSPECT mode), then put the database into EMERGENCY mode, and had SQL re-create the LDF file. Genius!

I can attest to the fact that this works on SQL Server 2008. My database was 27GB in size, so it may take a while to create the LDF file. For me it took about 30 minutes to recreate the file.

Hopefully this post will help someone else out with the issue that I was facing!