dafunk2001
Thu Feb 19 19:24:05 CST 2004
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?
michael@hampel.fsnet.co.uk (Michael Hampel) wrote in message news:<a03d5ce8.0402190104.36a7450d@posting.google.com>...
> 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.
>
>
>
>
> dafunk2001@yahoo.com (Cip) wrote in message news:<9f0f419b.0402181828.3e961a70@posting.google.com>...
> > 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.