Hello,

I have an e-commerce database that has both OLTP and Reporting
applications. OLTP applications mostly write and do very little
reading, only few records at a time (POS type of client), but there is
a lot of clients. Reporting applications read large amounts of data,
all day long. 90% of the reporting needs are related to a specific set
of data (spanning 5-6 tables), some kind of sales reports.

The database is quite big, with some tables overs 10GB, and using joins
or basic views in reporting does not allow for the required performance
(while OLTP is ok).

Data in OLTP and reporting must be almost synchronized, so a nightly or
even hourly DTS is a no-go, even a five minutes delay would be too
long. Also I have only one server so I can't use mirroring or
replication.

I thought of the following scenarios, maybe someone could give me
advice about which one is best.

1) Create reporting tables having all fields required by the reporting
applications. Store them on a distinct filesystem (dedicated LUN on a
San) to avoid I/O issues, and use triggers in original tables to update
the copies. In this scenario I worry about the transactions being
longer.

2) Same as previous, but using Service Broker to do the update. (Should
keep the transactions shorter).

3) Use an indexed view instead of a copy table. Include all required
columns in the index to have queries completely answered without having
to access base tables. (I am not too sure if this scenarios makes
sense).

Any advice will be appreciated.

Hi there,

As you have mentioned, keeping the transaction length as short as possible
is key so as not to effect the OLTP systems performance.

Even though you only have one server, replication is the way to go. Create
another database and replicate the data into it - you could even take the
opportunity to denormalise the data to enhance reporting performance. If
possible place this database (and its logs) on separate physical disks.

The brokering solution could provide a similar solution (use the activation
option on the queue) - but with much more development effort.

Is it possible to run your reports in dirty read mode? This could
significantly improve performance if its acceptable to your business
users...

Regards

Jamie

<lucm@iqato.com> wrote in message
news:1156176282.279667.141020@75g2000cwc.googlegro ups.com...
> Hello,
>
> I have an e-commerce database that has both OLTP and Reporting
> applications. OLTP applications mostly write and do very little
> reading, only few records at a time (POS type of client), but there is
> a lot of clients. Reporting applications read large amounts of data,
> all day long. 90% of the reporting needs are related to a specific set
> of data (spanning 5-6 tables), some kind of sales reports.
>
> The database is quite big, with some tables overs 10GB, and using joins
> or basic views in reporting does not allow for the required performance
> (while OLTP is ok).
>
> Data in OLTP and reporting must be almost synchronized, so a nightly or
> even hourly DTS is a no-go, even a five minutes delay would be too
> long. Also I have only one server so I can't use mirroring or
> replication.
>
> I thought of the following scenarios, maybe someone could give me
> advice about which one is best.
>
> 1) Create reporting tables having all fields required by the reporting
> applications. Store them on a distinct filesystem (dedicated LUN on a
> San) to avoid I/O issues, and use triggers in original tables to update
> the copies. In this scenario I worry about the transactions being
> longer.
>
> 2) Same as previous, but using Service Broker to do the update. (Should
> keep the transactions shorter).
>
> 3) Use an indexed view instead of a copy table. Include all required
> columns in the index to have queries completely answered without having
> to access base tables. (I am not too sure if this scenarios makes
> sense).
>
> Any advice will be appreciated.
>