Calculated Columns vs Calculations in stored procs.... what is the best way ???

[Cip]

I have several crossTab type reports in a VB app.

I am displaying these reports in a Datagrid. The Datagrid gets its
data directly from a stored proc I have created on SQL Server 2000
(rp_crossTab) which returns data in a nice crossTab fashion. (The
stored proc accepts parameters such as onRows, onCol, sumBy, etc.)

My problem is I have several crossTab reports which actually depend on
data found in other crossTabs.

For example, I have this total paid amount crossTab:

REPORTA

Name/Month Jan Feb Mar
Jon 20 40 40
Jack 100 50 200
Jill 25 75 75

And another report might be a month-to-month ratio of the total paid
amount:
(this report clearly depends on REPORTA)

REPORTB

Name Jan to Feb Feb to Mar
Jon 2 1
Jack 0.5 4
Jill 3 1


I thought of two ways to create REPORTB:

1) Call another stored proc which then calls rp_CrossTab, and performs
the necessary calculations using tempTables.

2) Creating a new table using new DataColumn objects in .NET and
setting their necessary values in the "Expression" field (eg
"Jan/Feb") and also making the columns dependent on REPORTA.

I can think of various problems with both scenarios...
Correct me if I am wrong but solution 1 would take a huge performance
hit since it would be in fact re-calculating REPORT A in order to
generate REPORT B.
Solution 2 sucks because I would have to hard-code logic in my .NET
app and I dont really wanna do that.... as well I dont think I can use
custom functions in the column Expression field (eg.
DataColumn.Expression = "MyWeightedAverage(COL1,COL2,COL3)")

What is the best way to create this second (or any other dependent)
report?
Is there something I have missed?

Is it generally better to have ALL logic in stored procs... even it is
much slower?

Thanks a lot, I really appreciate any comments anyone may have.

 

[Miha Markic [MVP C#]]

Hi Cip,

There is a third solution:
You might manually calculate your values and store them in cells - something
like Expression but do it in code.
I guess this is the best one.

 

[Michael Hampel]

This is a difficult issue and each situation needs to be evaluated but
generally if there is to be a lot of processing of data I think the
best place to do this is in a stored procedure. How this performs
would depend on how well your queries are written and on your database
schema. As you say with the functionality available in ado.net it is
extremely feasible to do this processing in .net and the main
consideration would probably be the volume of data to be processed.

If changes to the business rules have to be made it is simpler to
modify an sp rather than recompile a component and distribute that.

One of the most useful types of business rule to enforce in components
tend to be validating input before it is submitted to the database as
this will save round trips.

 

[Cip]

thanks for the suggestions...

I know there is no definitive ALWAYS DO THIS answer, but I was just
wondering what other people had to say as I have very little
experience compared to most of you.

If it helps, most (99%) of the Data Grids will be used to only DISPLAY
data. ie. users will never need to save anything to the database.

Keeping that in mind, should I be NOT be using DataAdapters -> fill
->DataSets ?? Is the performance hit that bad?

I chose to use DataSets because I found them easier to use... if I
call a stored proc that runs multiple select statements, filling the
DataSet automatically creates new tables for me for each select
statement. very nice.

Is there a way to make the dataset "read-only" or something like that?
(as i have said, 99% of reports will not update the database).

Basically all I have are a bunch of fancy views. CrossTab queries
with many complicated mathematical expressions.

WHAT IS FRUSTRATING IS:
This stuff literally takes subseconds to develop in EXCEL, but is
taking me a lot longer to code the logic in either Stored Procs or in
the Application itself (either by creating new DataTables or
explicitly setting the DataGrid's cell value as someone else
suggested)

any other advice?

 

[Miha Markic [MVP C#]]

WHAT IS FRUSTRATING IS:
This stuff literally takes subseconds to develop in EXCEL, but is
taking me a lot longer to code the logic in either Stored Procs or in
the Application itself (either by creating new DataTables or
explicitly setting the DataGrid's cell value as someone else
suggested)

Who did suggest that? I was talking about DataTable cells.
Similar to expressions - the difference is that you enter values by your
code.
If you are using DataGrid that you can use DataView's AllowNew, AllowEdit
and AllowDelete properties to set readonly mode (note that if you use
DataTable as DataSource in reallity the DataTable.DefaultView is used).

HTH