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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Cip (dafunk2001_at_yahoo.com)
Date: 02/20/04 

Date: 19 Feb 2004 17:24:05 -0800

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.

Relevant Pages

  • Re: SYSTEMS ENGINEER/ VAX-VMS/ CAREER POSITION
    ... System Administration and database admin from NIIT, ... salary details and generate reports and enclosures,and calculates ... Responsibilities: ... Write New programs from user requirements, Programming Changes ...
    (comp.os.vms)
  • Re: Selling the boss on a "publish to the web" Access app?
    ... provide a link to the database to users and they could come in to the site, run reports, etc. ... Can you find me an example of a database online and don't have to login that was written in asp.net for example? ... You REALLY need to adopt some overall secure model for your web systems, regardless of what your goal is here. ... So the idea of a one click to publish to some system, certainly means that you're going to have to adopt a web server with a FIXED set of standards. ...
    (comp.databases.ms-access)
  • Re: Advice needed for a growing Access 2000 project
    ... However, it turned out that quite a few of those were "leftovers" from previous releases, no longer accessible from anywhere but the database window, and, thus, no longer used. ... But that certainly isn't the _norm_ -- without any 'heroic' measures, there are routine reports of split Access DBs ... Finally, in my opinion, for "Windows apps", that is, individual-user applications, modest-sized multiuser applications, and client-server applications of any size, Dot Net does NOT "help along" any of these issues. ... The post I reference was in reference its self to the MS Access Help file under "Microsoft Access database general specifications" ...
    (comp.databases.ms-access)
  • Re: running report cause fatal error- on Win98, not XP
    ... another query. ... only the more complex reports cause the crash- the others do ... Then compact the database: ... Still in the code window, choose Compile from the Debug menu. ...
    (microsoft.public.access.reports)
  • Re: running report cause fatal error- on Win98, not XP
    ... It is actually not that difficult to crash JET with complex queries, ... another query. ... only the more complex reports cause the crash- the others do ... Then compact the database: ...
    (microsoft.public.access.reports)