I'm not dismissing the idea at all, but I...

Ok I'm going to assume that you need NULLs from the input file for reasons I...

Craig Farrell (12/13/2010)

---

Alright, I'll bite. Anyone know how to turn a column from a stream in an SSIS package into a non-nullable version of itself? Data Conversion seemed...

Yes :laugh:

All things are better in...

LutzM (12/13/2010)

---

that really is a very interesting approach to deal with large volume tables. I guess the partition sturcture of some of our archive tables might change soon! (We're currently...

Ouch. I...

OMG there's a guy called Dr Seward, who has a free eBook called:

"Gila Monster Propagation"

I have not yet summoned the courage to hit 'download'...

edit: ok so I can't spell

You may even find that the partition-switching doesn't speed up the application of the deltas over using a cursor to delete and then insert one PortfolioID/Day combination from the staging...

No worries - the basic idea is pretty simple, but it may take a while for all the pieces to fall into place. I'd encourage you to play around...

Final demo: converting PortSecAgg into a partitioned table, using fast (minimally-logged) bulk load:

(Converting to a partitioned structure in-place will be glacially slow and blow up your log)

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL...

Demonstration of handling a refresh for a particular portfolio for a day:

(uses the same database in the state it was at the end of the last demo)

-- ==========================

-- Demo modifying...

Demonstration of instant loading for a whole new day of data:

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

GO

USE master;

DROP...

Ok, so those are the problems. The approach I would take solves those problems by not performing all that work at all.

Imagine if the only requirement was to load...

Hi Craig,

Looking at the execution plans you posted, there are a number of common reasons for the slow performance:

1. Full table scans

2. Large hash joins

3. Large sorts

4....

Craig,

Thanks for that, it helps a great deal. Just one more question before I get to the good stuff.

The delta/replacement files you get for historical edits: do they contain...