Maintain separate table via trigger vs. indexed view

Hello,
I have a table LargeTable in many columns and many rows. However, I also
need to have a small subset of the rows in LargeTable and only data from
small set of columns; call it SmallTable.
SmallTable gets read very often. So basically I've been using trigger on
LargeTable to watch for any change there to populate the SmallTable.
However, I realized that an indexed view can replace SmallTable.
In general, which one would be a better design in term of performance? I
know that indexed view can be more elegant but I'm interested in
performance. thanks!"Zester" <zeze@.nottospam.com> wrote in message
news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table LargeTable in many columns and many rows. However, I also
> need to have a small subset of the rows in LargeTable and only data from
> small set of columns; call it SmallTable.
> SmallTable gets read very often. So basically I've been using trigger on
> LargeTable to watch for any change there to populate the SmallTable.
> However, I realized that an indexed view can replace SmallTable.
> In general, which one would be a better design in term of performance? I
> know that indexed view can be more elegant but I'm interested in
> performance. thanks!
>
>
What makes you think this is a candidate for an indexed view? You could
create an ordinary view and put a relevant nonclustered index on the base
table. That way you won't incur the same write overhead that an indexed view
has. In SQL Server 2005 you can also include non-key columns in a
nonclustered index.
--
David Portas|||the LargeTable gets read even more often than the SmallTable and LargeTable
serves the main feature of our product. Writing to it is not often but does
occur - most likely only to the columns that have nothing to do with the
dataset needed for SmallTable. The feature using SmallTable is minor, so we
don't want it to interfere with the main feature that needs LargeTable.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uKBFF$3BIHA.3564@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OaN3Tf3BIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> I have a table LargeTable in many columns and many rows. However, I also
>> need to have a small subset of the rows in LargeTable and only data from
>> small set of columns; call it SmallTable.
>> SmallTable gets read very often. So basically I've been using trigger on
>> LargeTable to watch for any change there to populate the SmallTable.
>> However, I realized that an indexed view can replace SmallTable.
>> In general, which one would be a better design in term of performance? I
>> know that indexed view can be more elegant but I'm interested in
>> performance. thanks!
>>
> What makes you think this is a candidate for an indexed view? You could
> create an ordinary view and put a relevant nonclustered index on the base
> table. That way you won't incur the same write overhead that an indexed
> view has. In SQL Server 2005 you can also include non-key columns in a
> nonclustered index.
> --
> David Portas
>

Maint Plans

Hi
SQL Server 2005 sp2
I have a maint plan that was created via the wizzard using separate schedule
fpr each task
sub_plan1 - history clean up; scheduled onec a day at 03:00AM
sub_plan2 - Full backup for all databases; scheduled onec a day at 03:00AM
sub_plan3 - Log backup for specific databases ; scheduled daily every 10
minutes
sub_plan4 - maintenace cleanup; scheduled onec a day at 03:00AM
I face 2 problems:
1. sub_plan2(Full) works only for those databases that are not part of
sub_plan3(Log)
2. sub_plan4 does not work
I will appreciate any help.
Thanks,
Yaniv
Hi
Have you set up the reporting and logging options for the maintenance plan
(next to manage connections) on tool bar. Knowing what the error messages
are! Make sure that your log backups are not running at the same time as the
full backup.
John
"Yan" <yaniv.etrogi@.gmail.com> wrote in message
news:%23yeqwseVIHA.5596@.TK2MSFTNGP05.phx.gbl...
> Hi
> SQL Server 2005 sp2
> I have a maint plan that was created via the wizzard using separate
> schedule fpr each task
> sub_plan1 - history clean up; scheduled onec a day at 03:00AM
> sub_plan2 - Full backup for all databases; scheduled onec a day at 03:00AM
> sub_plan3 - Log backup for specific databases ; scheduled daily every 10
> minutes
> sub_plan4 - maintenace cleanup; scheduled onec a day at 03:00AM
> I face 2 problems:
> 1. sub_plan2(Full) works only for those databases that are not part of
> sub_plan3(Log)
> 2. sub_plan4 does not work
>
> I will appreciate any help.
> Thanks,
> Yaniv
>

Maint Plans

Hi
SQL Server 2005 sp2
I have a maint plan that was created via the wizzard using separate schedule
fpr each task
sub_plan1 - history clean up; scheduled onec a day at 03:00AM
sub_plan2 - Full backup for all databases; scheduled onec a day at 03:00AM
sub_plan3 - Log backup for specific databases ; scheduled daily every 10
minutes
sub_plan4 - maintenace cleanup; scheduled onec a day at 03:00AM
I face 2 problems:
1. sub_plan2(Full) works only for those databases that are not part of
sub_plan3(Log)
2. sub_plan4 does not work
I will appreciate any help.
Thanks,
YanivHi
Have you set up the reporting and logging options for the maintenance plan
(next to manage connections) on tool bar. Knowing what the error messages
are! Make sure that your log backups are not running at the same time as the
full backup.
John
"Yan" <yaniv.etrogi@.gmail.com> wrote in message
news:%23yeqwseVIHA.5596@.TK2MSFTNGP05.phx.gbl...
> Hi
> SQL Server 2005 sp2
> I have a maint plan that was created via the wizzard using separate
> schedule fpr each task
> sub_plan1 - history clean up; scheduled onec a day at 03:00AM
> sub_plan2 - Full backup for all databases; scheduled onec a day at 03:00AM
> sub_plan3 - Log backup for specific databases ; scheduled daily every 10
> minutes
> sub_plan4 - maintenace cleanup; scheduled onec a day at 03:00AM
> I face 2 problems:
> 1. sub_plan2(Full) works only for those databases that are not part of
> sub_plan3(Log)
> 2. sub_plan4 does not work
>
> I will appreciate any help.
> Thanks,
> Yaniv
>