SQL Server Data Quality Services in SQL2012 RC0 – Part 2

Since my last post on SSDQS I’ve been in touch with the development team who have raised some suggestions and workarounds to improve performance. This post will focus on that feedback and how effective it is in reducing execution times.

SSIS vs DQS Client Cleansing

The first bit of feedback was that interactive cleansing through the DQS Client was known to be faster than SSIS interaction – so my first instinct was to test just how much faster it was – and I was surprised – the speedup was around fivefold. The below chart shows my results for processing 10,000 rows with 1-5 Domains:

SSDQS SSIS vs Client performance

If this scaled, then my 3.9 hour estimate for a 1m row / 10 Domain process would shrink to under an hour. Still not ideal, but getting closer to a production viable speed.

Now, the reason behind this – as explained by the DQS team – is that the component sends discrete chunks through to validate (1000 rows at a time as far as I can tell) which the DQS Server then passes back – which adds overhead and is inefficient for the DQS Server. This is done so that the DQS Cleansing component is not a blocking component. However at this point it’s not possible as far as I can tell – to have any control over the size of these chunks.

Speeding up SSIS processing

The next bit of feedback was to suggest breaking up the work to improve throughput. There’s two ways of doing this – first is to split up the domain processing and second is to break up the data into  chunks and process in parallel. So I tried this by splitting it up the following ways:

1. 5 Domains through a single DQS Cleansing Task – 10,000 rows
2. Each domain though a dedicated DQS Cleansing Task – 10,000 rows
3. 5 Domains through 5 dedicated DQS Cleansing Tasks – 2,000 rows each – 10,000 total

To be honest, the results weren’t overwhelming:

1. Untuned: 94s
2. Separated Domains: 86s
3. Separated Data: 78s

Given that the Separating of Domains means the data would in a real situation have to be split up and recombined, there’s probably not enough saving there to make that approach worthwhile. Splitting up the data yielded a 20% processing time saving – nice, not enough to be really useful given how long it takes normally.

Practical suggestions for the DQS Team

A direct quote from the DQS team’s mail to me was “DQS is designed to best perform on large chunks.”. Looking at the SSIS logs, it’s only sending 1,000 rows at a time – which is clearly sub optimal for DQS + SSIS to interact effectively. So there are two options available for a fix based on my understanding:

1. Make the component configurable to send larger chunks – with a more SSIS like 10,000 rows default
2. Make the component optionally blocking

The first just makes sense and I doubt would be a massive job to make “Rows To DQS Server” a configurable property. The second may be harder – and can probably be duplicated just by setting the new “Rows to DQS Server”  property to zero or a very high number.

In practice it’s still a bit slow for very heavy DW workloads, but hopefully the above suggestion would give it a real boost in performance and make it viable for mid sized ones.

SQL Server Data Quality Services in SQL2012 RC0 – Part 1

So the key news – in case you missed it – is that SQL2012 RC0 has been made available for download. After a few battles with the Installer – first the known issue with the Distributed Replay users – then some things requiring manual installs of KB’s to get the installer to run through – I have a VM set up with it.

The DQS team have posted about the improvements made in the DQS blog – and the one I really wanted to focus on was performance via SSIS as the CTP3 offering was not viable for large data sets. So this Part 1 post is all about the performance of DQS via SSIS in RC0.

So, I set up a Knowledge Base in the same way as I did for testing CTP3, with 5 duplicate domains – just evaluating an Integer with a single rule saying that integer had to be greater than a value to be valid. Then I ran two sets of values (5k & 10k rows) through the KB via SSIS, evaluating 1,2,3,4 and 5 fields.

So how does DQS Perform?

Here’s the results- the value in the grid is Seconds taken to process.

DQS Performance in SSIS

So – have we moved on from CTP3? A bit. But not much, and enough to be accounted for by a different VM setup (as a reminder CTP3 processing 5k rows took from 20 to 45 seconds for 1-5 columns). I accept a VM may be slower than a properly configured server, but even if it was twice as quick it would still not be a viable option for industrial use.

Looking at execution time changes by number of columns / rows processed, the time taken seems to be pretty linear as rows and columns increase, so it appears DQS performance can be evaluated pretty much as:

DQS Execution Time = Spin Up Time + (Columns * (Rows * Row Process Time))

Where:

Spin Up Time = time taken for DQS engine to start (Constant)

Columns = number of columns being evaluated (Variable)

Rows = number of rows being processed (Variable)

Row Process Time = time taken to process a single row (Constant)

On my VM, Spin Up Time seems to be 7 seconds, and Rows Process Time = 0.0014 seconds.

So, if we had to validate 10 columns on 1,000,000 rows of data (not too crazy) -

DQS Execution Time = Spin Up Time + (Columns * (Rows * Row Process Time))

DQS Execution Time = 7 + (10 * (1,000,000 * 0.0014))

DQS Execution Time = 7 + (10 * (1,000,000 * 0.0014))

DQS Execution Time = 14007 seconds = 233 minutes = 3.9 hours

Which effectively rules it out as a viable production process. Note of course that my formula doesn’t make any allowance for rule complexity.

Is DQS Production ready?

As per anything, the answer is – It depends. For validating small data sets it’s in the realms of slow, but probably acceptable. For big data sets, I’d have to say no – I couldn’t use it in a production environment to validate large sets of data. I’ve added a Connect suggestion to get this on the teams radar.