Architecture question

[Tim]

Hello everyone. I visit this board pretty regularly but have never
posted. My question is an application architecture question... so
hopefully it's okay to post in the dotnet general forum.

I have this project I'm proposing but want to get some opinions on a
good architecture. Some of the requirements are as follows:

1. 30+ tables and 25+ lookup tables
2. Small number of records added monthly (<100)
3. Fairly complex, PCU-intensive reports acessed monthly
4. Extremely complex, PCU- and bandwidth-intensive reports accessed a
few times a year (complex calculations involving tens of thousands of
records, multiple aggregations, outer joins -- queries referencing
dozens of other queries referencing 30+ tables. Record source of report
after calculations includes ~10,000 records).
5. One or two primary users for data entry and reporting, but future
needs may require reports accessed by dozens of clients.

The moderately-expensive monthly reports could be handled by a central
DB server and the network, but the super-expensive reports I think are
too much to ask a central server and the company's network. Therefore,
I think it's necessary to store data locally on users machines and run
the reports from there.

Since the reports are complex, I think a relational data store is
required on the client machines to generate them (rather than a saved
Dataset). I was thinking MSDE would work.

So my biggest question is such: What's the best way to keep a central
server in sync with client DBs without too much toll on the network and
central server? My two ideas so far are:

1) Build the application as if the client DB is the main database.
Don't worry about excessive traffic between the app and the DB because
it's on the same machine. Use SQL Server replication to sync the
central server to the client machines.

The downside to this design seems to be requiring replication to be set
up at the central server for each client machine. This might not be
feasible because I'd like people to be able to install the client
software without the server DB knowing about each installation.

2) Timestamp every single record saved in the client DB (and also every
record stored in the central DB). When the client app opens, check the
central DB for new records and download them. When the client app
closes, upload all new records to the central server.

The downside here might be that each time a client opens the app, a
whole bunch of queries are run on the server, checking timestamps in 30
or so tables. This could be a big hit. Also, timestamps on the client
machines need to be in synch with the timestamps on the server, so a
web service would be required to expose the system clock of a central
computer.


Sorry, for such a long post (especially for my first one), but I really
need some input on this. Is there an easier way to handle this
situation? I'm not in love with either of my ideas.

Any help is GREATLY appreciated.

 

[Brian Gideon]

Tim,

Putting MSDE on client computers and trying to keep them in sync with a
central server sounds very complicated to me. Doing calculations on
tens of thousands of records shouldn't be a problem for even the most
feable database server. If the logic is complex then put it in a
stored procedure.

How many clients are we talking about? How often will each client run
one of these reports? How many records are displayed on the reports?
Is the database schema already created? If so, do you have the freedom
to modify it if it will enhance performance?

Brian

 

[Tim]

Brian,

We're talking about only a couple clients at the moment, but in the
future maybe dozens. Not too many.

The super-heavy duty reports will only be run a few times a year by the
existing couple of clients. In the future, they may want dozens of
clients to be able to run the reports, but they still wouldn't be run
frequently. Less heavy-duty reports may be run several times a month,
but there are about 6 of them.

The heavy reports display ~10,000 records. The less heavy reports
display a couple thousand.

The database schema is already created in Access, and the heavy-duty
reports take several seconds to run on a standalone machine. I agree,
this would probably improve with stored procedures. I don't think the
core data architecture would change because it's well-designed and
normalized, but I thought one way to possibly decrease the hit on the
the server for the heaviest reports would be to output the first level
of calculations to tables at the client's request (after monthly data
is entered). Then when a report is run, it is looking at hard data
that has already passed much of the intense calculations, and the calcs
won't need to be re-run each time a client requests the report. This
would help with the server load, but would still send 10,000 or so
records across the wire on occasion. Is it acceptable you think to
send that many records across the wire? The company on a previous
project got uncomfortable sending a few thousand at a time, so I had to
limit it to around 500 (although that table was text-intensive, and the
one I'm talking it has a few text fields and mostly numeric fields).

 

[Paul]

Tim,

Maybe SQL Server Reporting Services might be the answer. I haven't tried
this yet but from what I understand it should be possible to schedule the
"heavy" reports to run off-peak on the server and simply deliver the results
in a browser to whoever wants them. Worth a look?

http://msdn.microsoft.com/SQL/sqlwarehouse/ReportingServices/default.aspx

Paul

 

[Brian Gideon]

Tim,

You shouldn't have any problems sending 10,000 rows across the wire. I
don't understand the company's apprehension regarding this issue. How
large are the rows? Are we talking about 10, 100, or 1000 bytes?
Assuming they're 1000 bytes each that's still only 10MB plus overhead
right?

Brian

 

[Tim]

Paul,

Thanks much for the tip! I'll look into this.

tim

 

[Tim]

Brain,

It looks like results are about 350 bytes per row, so aroung 3.5
MB....about the of the size of an MP3. I don't know, that still seems
like more than they would be comfortable with if the user has the
ability to request the report at any time, as many times as he/she
wants. But maybe you're right, and I just need to convince them that
it shouldn't be a problem. I think the company is skeptical about any
outside contractors developing apps that they have to host.

Thanks!

tim