Francisco H Tapia
my.lists at verizon.net
Thu Jun 10 16:04:57 CDT 2004
OOOOUUUUCHHH!!! :O Well keep fighting the good fight. We have a base stored procedure that feeds about 4 diffrent final reports... in the Access report they are just grouped and displayed diffrently, but make one change in one place and it is less headaches for us. I like building on the principel of easy to maintain. That's my job security, making it easier.... Nicholson, Karen wrote On 6/10/2004 1:52 PM: >I am fighting against some dinosaurs; we have so many Crystal reports that >they take 16 hours to process per day. One example is our sales commission >reports; 26 versions of the same data, just presented and grouped >differently. 175 Crystal formulas in each report - make a change and all 26 >reports need to be changed. I just wrote a file extract so that it changes >in one place. I get the hair on their necks to stand up, but my blood >pressure is low. > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Martin >Reid >Sent: Thursday, June 10, 2004 4:43 PM >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] Difference between views and queries > > >Thats how we do it. We copy the live data out to another server and database >and the users report aganist that one. But then the data they use will only >change slightly over the academic year. I have never seen reporting tools >used on a live database anywhere I have worked. Thats not to say it dosnt >happen or is bad I just havnt seen it. > >Martin > >----- Original Message ----- >From: "Francisco H Tapia" <my.lists at verizon.net> >To: <dba-sqlserver at databaseadvisors.com> >Sent: Thursday, June 10, 2004 9:38 PM >Subject: Re: [dba-SQLServer] Difference between views and queries > > > > >>SUCH developers need to be dragged out to the street to be tarred and >>feathered... ;o) j/k >>no... >> >>A lot of times I find that my boss will request that he wants a new >>table w/ a join to another table and somehow magically come up w/ some >>solution for some third criteria. The fact remains that "HE" is not a >>developer. that's why he hired me. I know that sounds arrogant, but >>let's consider the performance of your SQL Server, if you have adequate >>RAM, CPU and a marginally good IO subsystem; well then your performance >>may even be acceptable. I have no idea how long it takes to run "any" >>of your reports, but the ones we've written so far in Access whose >>recordsources are stored procedures, well, the result has been < 20 >>seconds for reports that used to take well over 5 minutes in Access. In >>fact just about all report run in the 2-3 seconds timespan which has >>helped make our new Access.ADP system shine. >> >>One thing that they've been missing is the ODBC access to tables to >>create their own reports. Many other SQL Developers often find a >>combination of VIEWS that are interpreted to the End Users as tables to >>help fill this gap, thus you'd have ODBC views which the users would >>think are "TABLES". This will satisfy direct linking to tables and >>possibly promote better access time (tho not guarnateed, because as we >>all know, it is definatly easy enough to write a poorly joined SQL >>statement). >> >>another solution some developers use is to have another SERVER, >>generally known as a reporting server for processing CUBES of data. In >>this scenario, data is replicated TO it by the LIVE system in generally >>a delayed Transactional method. Many times the data is flattened out >>and de-normalized. This helps hide ID's that are meaningless to >>endusers, and for them to have direct "table" access to such system. >>These systems often have to be guarded and monitored. >> >> >>so in your scenario the answer really is, "it depends" :). If Views are >>a possiblity and your system is Not just for datawarehousing, then >>definitly I'd push in that direction. If it were my system, I wouldn't >>want to be considered responsible for lack of availablity should people >>mis-construct their reports. >> >> >>Nicholson, Karen wrote On 6/10/2004 1:03 PM: >> >> >> >>>So what do you think of having tons of Crystal Reports that hit against >>> >>> >the > > >>>sql server (odbc) tables? I prefer to create views for the Crystal >>> >>> >Reports > > >>>to read, >>>but everyone in this corporation likes to hit against the tables and >>> >>> >write > > >>>the >>>groupings, calculations, etc in Crystal. >>> >>>-----Original Message----- >>>From: dba-sqlserver-bounces at databaseadvisors.com >>>[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of >>>Francisco H Tapia >>>Sent: Thursday, June 10, 2004 3:58 PM >>>To: dba-sqlserver at databaseadvisors.com >>>Subject: Re: [dba-SQLServer] Difference between views and queries >>> >>> >>>jwcolby wrote On 6/10/2004 9:33 AM: >>> >>> >>> >>> >>> >>>>Can anyone explain the difference between a view and a query? Views use >>>> >>>> >a > > >>>>query, plus the view keyword. I have a couple of books that I have read >>>> >>>> >>>> >>>> >>>the >>> >>> >>> >>> >>>>chapter on Views, but I so far haven't managed to "get" why you wouldn't >>>>just use the query itself instead of turning it into a view. >>>> >>>> >>>> >>>> >>>> >>>> >>>A query is a request for an Access Database, however for Sql Server you >>>would either use a View or Stored Procedure to return the data you >>>wanted... you are also able to use dynamic SQL to retrieve the >>>information you need. ANY request given to the SQL Server engine is >>>managed by the engine, unless you are running Remote servers (iirc). >>> >>>In Sql Server, it is TABOO, nay, GENERALLY bad practice to use dynamic >>>sql because of the implication of SQL INJECTION attacks, this poses a >>>"real" security threat to your database. and your server. >>> >>>another reason to use a VIEW over dynamic sql is that it is >>>pre-optimized by the SQL Server Engine and thus runs faster and more >>>efficient. Additionally if you use Dynamic SQL then your individual >>>users who access the server will need EXPLICIT "SELECT" permissions by >>>you, which is another 'bad' practice. In SQL Server you make data >>>available to your users via VIEWs and Stored Procedures or some other >>>secure way in order to protect your tables and it's data. >>> >>>ya get wot I mean? >>> >>> >>> >>> >>> >>-- >>-Francisco >> >> >>_______________________________________________ >>dba-SQLServer mailing list >>dba-SQLServer at databaseadvisors.com >>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>http://www.databaseadvisors.com >> >> >> >> > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > > > > -- -Francisco