2

I am trying to optimize an SSIS package that retrieves data from a very large transaction table that gets millions of new records daily. The data is being collected from several hundred field servers where SQL Server 2008 Standard Edition is installed. CDC, therefore cannot be used as it is an Enterprise feature. The plan is this:

  1. Archive column, smallint - 0=new (default constraint), 1=archived, 2=updated (trigger)
  2. Default constraint Archive=0
  3. Update trigger Archive=2
  4. Index on archive, timestamp

The SSIS package will then get all records where Archive=0, insert those into the destination then get all records where Archive=2 and update them in the destination.

What I need help with is the setting of the Archive column to 1 after the SSIS procedure is done writing new or updated rows. What would be the most efficient way of setting all of the rows processed without touching any rows that might have been inserted or updated after the initial select?

  • 1
    How about making use of the OUTPUT virtual table? Set your transaction isolation level correctly (snapshot/serializable) so that you only see the rows as of the moment your process begins. Use the following for your OLE DB Source UPDATE T SET Archive = 1 OUTPUT DELETED.* FROM Table T WHERE T.Archive = 0; That updates everything in a nice atomic operation with a side effect of generating the target output into your data flow buffers. Route that to your destination and it's done. Nice and neat – billinkc Dec 2 '14 at 16:38
  • I like it! Nice and devious. If I can ever get the distributed transactions to work, this will be perfect! Can you copy your suggestion to an answer? – Metaphor Dec 3 '14 at 2:38
  • Works like a charm. Please put your suggestion in an answer, so I can give you the bounty. – Metaphor Dec 4 '14 at 2:06
2
+50

How about making use of the OUTPUT virtual table? Set your transaction isolation level correctly (snapshot/serializable) so that you only see the rows as of the moment your process begins.

Use the following for your OLE DB Source

UPDATE T SET Archive = 1 OUTPUT DELETED.* FROM Table T WHERE T.Archive = 0;

That updates everything in a nice atomic operation with a side effect of generating the target output into your data flow buffers. Route that to your destination and it's done. Nice and neat

  • Great suggestion! I'm well into getting this integrated into my solution. Thanks! – Metaphor Dec 4 '14 at 16:31
2

Since you are using SSIS and capturing the changed rows, you should have SSIS capture the PK values for the rows that are being processed. The last step of the package should be to do a targeted update on the source table, setting the [Archive] field to 1 based on those PK values.

And if not already done, the update trigger needs to exclude those changes from setting the [Archive] field to 2. You will probably need something along the lines of:

UPDATE tab
SET    tab.[Archive] = 2
FROM   dbo.MyTable tab
INNER JOIN INSERTED ...
INNER JOIN DELETED ...
WHERE INSERTED.Archive = DELETED.Archive
AND   DELETED.Archive IN (0, 1) -- Add 3 to this list; see below for why

BUT, then you need to deal with the scenario where a row gets update during the ETL process. You still want to ETL that change the next time around. But if the ETL process is running it will change the [Archive] field to 1 at the end and then there is no record of that row being changed. So then you kinda need another value to indicate "Being ETLed", maybe 3. You can then, similar to what @billinkc suggested in a comment on the question, do an UPDATE with the OUTPUT clause as follows:

UPDATE TOP (@BatchSize) tab
SET    tab.[Archive] = 3
OUTPUT INSERTED.*
FROM   dbo.MyTable tab
WHERE  tab.[Archive] IN (0, 2)
ORDER BY tab.[Archive] ASC;

Then, in the final step of the SSIS package, the targeted update should be on both the PK values AND [Archive] = 3. And add 3 to the IN list in the trigger update query above. This way, only records that have not changed during the ETL process will get updated to 1, while records that have been changed during the ETL will appear as 2 and will get picked up again the next time around with their modified values.

Of course, you might also want to create a filtered index on the [Archive] field to support the getting of records marked as 0, 2, or 3. Hence:

CREATE INDEX ... WHERE [Archive] IN (0, 2, 3);

You want to grab the records marked as 3 because, assuming this is single-threaded per field server, records marked as 3 represent stuck records due to a prior process that failed. This is also why you don't want to mark the records as 1 in the UPDATE...OUTPUT when you initially get them; if the process fails then they will be marked as already archived and not picked up again.


That being said, you might be better off using a queue table instead of a status field. You could create a simple table with just a few fields:

QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTablePKfield  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

Then the trigger becomes a simple INSERT, UPDATE trigger that just does:

INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.PKfield
  FROM   INSERTED ins;

Then the SSIS process gets the values via:

SELECT TOP (@BatchSize) etl.QueueID, tab.field1, tab.field2...
FROM   dbo.MyTableEtlQueue etl
INNER JOIN dbo.MyTable tab
        ON tab.PKfield = etl.MyTablePKfield
ORDER BY etl.QueueID ASC;

And the end of the SSIS process does a simple delete against the [MyTableEtlQueue] table based on the [QueueID] values it pulled in at the beginning.

In this model, you don't need the filtered index, or status column on a large table, which reduces contention on, and size of, that already large table. You don't need to worry about records in transit, whether in terms of updates or process failures.

EDIT:
Given the new information of the source table not having any uniqueness, that requires only a minor change to this queue table method: instead of tracking only the PKfield (which apparently doesn't exist ;-), track all of the fields (or at least all that need to be ETLed).

So the queue table changes to be:

QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTableField1  INT NOT NULL,
MyTableField2  INT NOT NULL,
...
MyTableFieldN  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

And the trigger changes to be:

INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.MyTableField1, ins.MyTableField2, ..., ins.MyTableField1N
  FROM   INSERTED ins;

Then the SSIS process changes to be (note the lack of JOIN to the source table now):

SELECT TOP (@BatchSize) etl.QueueID, etl.MyTableField1, etl.MyTableField2, ...
FROM   dbo.MyTableEtlQueue etl
ORDER BY etl.QueueID ASC;

This queue table method isolates the source table (and its processing) from the audit/ETL process (much like CDC does). This allows you app to interact with the source table with no issues, and no new column which takes up more space on the datapage, and no potentially long running transaction holding onto the source table while the ETL processes.

  • Thank you for your suggestions, srutzky. For a short list of unfortunate reasons, the source tables are not able to support uniqueness of rows. At least not in the version of the software certified and installed in the vast majority of the sites. In fact, I currently use what should be a unique key to track handled data and that has caused an upset with our field services guys. Don't ask, it's politics. But, I must find a different solution and I think billinkc's suggestion with transaction handling is just the ticket. – Metaphor Dec 3 '14 at 2:37
  • 1
    @Metaphor how does the table accept updates if there is no uniqueness? multiple rows get the same updates? And my solution doesn't require uniqueness in the source table, it was just assumed to be there. But that is just a simple change of the queue table to hold all of the source table fields instead of just the PKfield (and then you don't need to join back to the source table either). This way you don't need to mess with the schema of the source table; it is very unobtrusive. In fact, at that point it is pretty similar to CDC. – Solomon Rutzky Dec 3 '14 at 4:00
1

Change Tracking (CT) and Change Data Capture (CDC) are built into SQL Server. CT can identify the rows which changed since you last asked. CDC can provide a complete history of all data changes since you last asked. Both are easily manipulated from SSIS.

There will be no need to reset flags per row after processing. The system handles all this through additional, hidden tables specifically for this purpose.

  • CDC would be great, but I'm trying to track changes on SQL Server standard edition. I collect data from several hundred field servers, where Enterprise cannot be installed because of cost. – Metaphor Dec 2 '14 at 14:53
0

A pure SSIS solution would be to deliver the Data Flows for the Insert and Update tasks into Cache Transformations.

http://msdn.microsoft.com/en-us/library/bb895264.aspx

If they occur in 1 Data Flow then you might be able to send them to a single Cache, otherwise you will need 2.

Then you can test against those Caches in later Data Flows using the Lookup transformation. I would redirect any Matched rows to effectively discard them, then continue with just the unmatched rows.

Note Caches are stored in memory so try to keep the content lean e.g. just the key column(s). As long as you dont exhaust Physical Memory, the runtime performance will be very fast (no I/O).

This solution is probably the simplest to build and test, as it needs no code to implement.

  • If I understand this correctly, it would require a lookup to determine if a record already exists on the target. That's just not feasible in this system. In fact, it would be much more efficient to just drop the destination and pull the entire table over but even that isn't feasible. – Metaphor Dec 3 '14 at 14:43
  • As I understood the requirement, you have a set of "all of the rows processed" (the Archive=1 candidates) from which you want to exclude "any rows that might have been inserted or updated after the initial select" (the Archive=0 and Archive=2 rows). So the Lookup(s) would be connected to the Cache(s) as loaded by the Cache Transformation(s) in the earlier Data Flow(s). The Lookup would not be connected to a database table. – Mike Honey Dec 3 '14 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.