Limiting data in Indexed Views by date

Hi everyone,
We have a fact table containing 28,000,000 rows and are experiencing
scalability issues when performing queries. It turns out that indexed
views are an excellent way to pre-calculate aggregates and improve
scalability.
The problem is that the table is part of an OLTP solution, and is
updated constantly throughout the day. However, our indexed view only
needs to include data up to yesterday (which never changes once it's
written). However, when we create an indexed view, it includes today's
data, and the overhead required to maintain the view is causing our
data insert routines to slow down by 90%.
What are our options to limit the data in the indexed view to
yesterday? Ideally, we'd like the index to automatically update at
midnight each night, without the need for replication or batch jobs.
Here are the options we know of:
1. Create indexed view without regard for the date. As stated above,
this slows down insert speeds on the table and is not feasible.
2. Create a copy of the table which contains data only up to yesterday,
and run the indexed query on this table. Run a batch process at 12:01am
every morning to copy the new rows to the new table. The table is very
large, so this will necessitate some downtime however.
Is there another simpler way to do this?On 1 Dec 2005 09:25:46 -0800, marketing@.adwarereport.com wrote:
(snip)
>What are our options to limit the data in the indexed view to
>yesterday?
(snip)
>Is there another simpler way to do this?
Hi marketing,
Have you considered creating a second database for reporting. Each
night, after the backup of the OLTP database has finished, load the
reporting database with that backup.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Limiting data in Indexed Views by date

Hi everyone,
We have a fact table containing 28,000,000 rows and are experiencing
scalability issues when performing queries. It turns out that indexed
views are an excellent way to pre-calculate aggregates and improve
scalability.
The problem is that the table is part of an OLTP solution, and is
updated constantly throughout the day. However, our indexed view only
needs to include data up to yesterday (which never changes once it's
written). However, when we create an indexed view, it includes today's
data, and the overhead required to maintain the view is causing our
data insert routines to slow down by 90%.
What are our options to limit the data in the indexed view to
yesterday? Ideally, we'd like the index to automatically update at
midnight each night, without the need for replication or batch jobs.
Here are the options we know of:
1. Create indexed view without regard for the date. As stated above,
this slows down insert speeds on the table and is not feasible.
2. Create a copy of the table which contains data only up to yesterday,
and run the indexed query on this table. Run a batch process at 12:01am
every morning to copy the new rows to the new table. The table is very
large, so this will necessitate some downtime however.
Is there another simpler way to do this?
On 1 Dec 2005 09:25:46 -0800, marketing@.adwarereport.com wrote:
(snip)
>What are our options to limit the data in the indexed view to
>yesterday?
(snip)
>Is there another simpler way to do this?
Hi marketing,
Have you considered creating a second database for reporting. Each
night, after the backup of the OLTP database has finished, load the
reporting database with that backup.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Limiting data in Indexed Views by date

Hi everyone,
We have a fact table containing 28,000,000 rows and are experiencing
scalability issues when performing queries. It turns out that indexed
views are an excellent way to pre-calculate aggregates and improve
scalability.
The problem is that the table is part of an OLTP solution, and is
updated constantly throughout the day. However, our indexed view only
needs to include data up to yesterday (which never changes once it's
written). However, when we create an indexed view, it includes today's
data, and the overhead required to maintain the view is causing our
data insert routines to slow down by 90%.
What are our options to limit the data in the indexed view to
yesterday? Ideally, we'd like the index to automatically update at
midnight each night, without the need for replication or batch jobs.
Here are the options we know of:
1. Create indexed view without regard for the date. As stated above,
this slows down insert speeds on the table and is not feasible.
2. Create a copy of the table which contains data only up to yesterday,
and run the indexed query on this table. Run a batch process at 12:01am
every morning to copy the new rows to the new table. The table is very
large, so this will necessitate some downtime however.
Is there another simpler way to do this?On 1 Dec 2005 09:25:46 -0800, marketing@.adwarereport.com wrote:
(snip)
>What are our options to limit the data in the indexed view to
>yesterday?
(snip)
>Is there another simpler way to do this?
Hi marketing,
Have you considered creating a second database for reporting. Each
night, after the backup of the OLTP database has finished, load the
reporting database with that backup.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Limitations in term of number of tasks and number of columns

Hi,

I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.

To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.

Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?

If not, then do you have any idea why it's so slow ?

Thanks in advance for any answer.
Two things. One, the XML on your package has to be extremely large and cumbersome for the engine to work with. I would imagine this is one source of your slowness. If you can break your package up into smaller packages, that would be much better and likely easier to support.

Two, you might have some success in working offline via the "Work Offline" switch under the SSIS menu in BIDS. That is, perhaps some of the slowness is in validating your data flows against the connections.