| Author |
Topic |
|
roger_james_jr
Starting Member
7 Posts |
 Posted - 2005-03-07 : 19:01:12
|
| Hi,I need to provide a reporting layer to a sql 2K database. I am unsure of which to use - stored procedures or views. Which will be the most flexible, what would be the trade off's between them.I will be using crystal reports, and it doesnt seem to care either way (as you would expect)taRoger |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-07 : 19:14:34
|
| All access to the database should be through stored procedures. Then within those stored procedures, you can utilize views if you want to or just hit the tables directly.Using stored procedures is the best model for security as you only need to grant EXEC on these (unless you are using dynamic SQL which is another story). So users won't have access to the tables themselves but only can query the data through the stored procedures.Tara |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-07 : 19:39:43
|
| I half agree with tara -- use stored procs as much as possible, but if you have business users who need to write reports and they are familiar with SQL and reporting writing in general, it is my opinion that you should give them access to Views and let them be self-sufficient. It depends on their skill-set and what tools you are using.If you only allow stored proc access to your DB, and there's lots of reporting requirements, it might be a full-time job to continually meet with users, get specs on all reports, write all the procs, tweak them, troubleshoot them, tie them out, create new ones, add columns, criteria, sorts, etc. My philosphy in working with business users has always been to provide them with as many tools as possible and as much knowledge as possible to get things done. Some DBA's/IT people definitely do not follow this approach, they prefer to control things quite a bit more, but I have found that for all parties involved my approach works extremely well. - Jeff |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-07 : 19:42:48
|
| Stored procedures has another big advantage for Crystal Reports because when you want to filter the data, it can be filtered before it leaves the database rather than hauling all the data to Crystal and filtering it there.Be One with the OptimizerTG |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-03-07 : 20:18:09
|
| At my current company, we are struggling with the exact issue Jeff brought up. The business has come to depend on IT to provide all reports to them. Every report has its own stored procedure. This produces either an Access, Crystal, or Active Report that is delivered to the business. It's impossible to keep up with the continual demands for adhoc reports using this schema. My goal is to move towards a data mart of generalized objects. Views would help provide more specific information needed for reporting needs. The goal long-term is to become a solution provider and increase the overall knowledge of the business users. Actual reports should still be produced with stored procedures, or some type of dynamic reporting tools. Eventually, you would want to move towards a consolidated data warehouse to further the solutions and tools provided to the business. The bottom line is that there is no ONE right answer. A combination of stored procedures, views, and other reporting tools needs to be weighed against security, flexibility to the business, and overall solution value. blah, blah, blah/rantMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-07 : 20:40:33
|
quote:
Originally posted by TG
Stored procedures has another big advantage for Crystal Reports because when you want to filter the data, it can be filtered before it leaves the database rather than hauling all the data to Crystal and filtering it there.Be One with the OptimizerTG
That's not necessarily, true. Like Access, in Crystal if you use filter criteria with standard SQL tables, views, functions and expressions it will get passed to the server. you can also view/tweak the SQL queries generated by Crystal as needed. It is good to train the users on how to set up their criteria so it is server friendly. (Older crystal manuals were excellent at providing info and examples on this; I haven't read one since version 8.0 so I don't know how they look now)If your report combines two stored procs, then indeed it is crystal which is doing the work to bring back all data and doing joins/filters locally (perhaps this is what you are thinking of). This is a good argument AGAINST stored procs for report designs -- if the reporting tool allows you to combine stored procs results into your final report, then the performance can be horrible. Same with if it allows you to group stored proc results, or re-sort them. This all must be done at the client. Forcing users to request stored procs for all reporting needs might make them start combining procs themselves to create reports, instead of asking for new ones, and that would be a tremendous mess with horrible inefficiencies.- Jeff |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-07 : 20:50:57
|
| Well its certainly been a long time since I was developing crystal reports. I can't even remember the last version I worked with but I seem to recall that there was 2 choices, SPs or Tables/Views. No sql statments. So thankyou, I stand corrected.But I've got to say, I'm jealous. The users at your and Derrick's companies are much (IT) smarter than the users at companies I've worked at. I personally like model of a dedicated report writing team that reports to the execs (as long as I'm not on it). :)Be One with the OptimizerTG |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-07 : 21:19:31
|
| >> I personally like model of a dedicated report writing team that reports to the execs (as long as I'm not on it). :)Yes, that's what we had at my last job. it was basically myself and 2 financial analysts as the reporting team. I wasn't the DBA, but I had full DBO rights to the production reporting database (basically a data warehouse that i designed). The DBA made sure it was backed up and controlled security, I pretty much did the rest. I was in the middle between a business user and an IT person -- in fact, I was technically in the Finance department and reported to the Controller (though the CFO would often bypass everyone and go directly to me when he needed numbers quickly). In retrospect, the entire political struggle to gain control of that server from the IT department really ended up being a big reason why eventually I left the company. I actually really enjoyed working with the business users. So, you could say the DBA was indeed lucky to have a strong, self-sufficient team (certainly the finance department was!) though at the time I am not sure he understood what a burden he was being relieved of. Even so, eventually the analysts and even some accountants got to be quite good a writing reports and understanding joins, criteria, groups, etc. as well; often they didn't even need my assistance. (which was good since report writing was only a tiny part of what I did -- mostly internal app design, data interfacing and helping with database and financial models)I got a great perspective that I will never forget about thinking like an accountant or analyst or AP clerk or whoever the "client" was and really learning their business needs, talking and thinking in their language (not IT acronyms!) and putting myself in their shoes. It wasn't always fun being in the middle between Finance and IT at all times, but it was a great experience.- Jeff |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-07 : 21:33:14
|
| Wow Jeff, really similar experience to what I've had. There's no question that that kind of business experience makes for an extremely valuable developer.Be One with the OptimizerTG |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-03-07 : 21:53:10
|
| I'm currently doing this side of the application at the moment..So here's my 2 cents..Views (coupled with good security) are pure gold for this..I expose a list of all possible views to the user in a QA type interface.. They drag the view to the "SQL" space and the app generates the SQL from INFORMATION_SCHEMA views.The user then MUST parse the SQL.. (SET PARSEONLY ON). They can execute the statement but no more than 1000 are returned... I sneakly add a SET ROWCOUNT statement in front of it.If they like what they see, they can save the TSQL with optional date parameters as a permanent "report". I am using the DevExpress XtraGrid for this.. It has the beautiful feature of being able to create and populate columns from any datasource PLUS be able to serialise the column layout into an XML file... Did I also mention the grid prints without a line of code... Write a report? Do it yourself (L)user!DavidMA front-end is something that tries to violate a back-end. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-08 : 09:19:10
|
| David -- that sounds very cool --- I like it. It really makes a big difference when you write more generic tools to give the users the power to get what they want, instead of you having to provide it all for them. Everyone wins.- Jeff |
|
|
|
roger_james_jr
Starting Member
7 Posts |
Posted - 2005-03-08 : 18:22:41
|
| thanks everyone for your input.Roger |
|
|
|
johnjohn
Starting Member
1 Post |
Posted - 2005-03-18 : 13:05:33
|
| Hi, I read the theads here and found it's very interesting topic. And I am in a similiar situation and need your expertise.My company has a webbased application server based on SQL Server 2000, which has 235 tables. we have around 20 clients use this application, and their data are stored in the same database, but they can only access their own data and can not go beyond the box.Recently, one client want to explorer the ability to run their own crystal report (in client side) based on our database (in our side). What client want is "we expose all their data, and they write and run the query". couples of solution we can think about:1) replicate the database, and clean up the database copy, for example, only keep one client's data and remove others based on some criteria. we run this process (replicate/cleanup) bi-hourly or daily. The disadvantage is "if other clients also want this service, the replicate/cleanup process will make the server very very busy.2) create store procedure which accept the parameter such as "client name", and the output is the data ONLY for that client. Currently what I thought is creating stored procedure for each tables, and end up with 235 stored procedure. Is there any way that "only several stored procedures are required to fulfil the same task"?Any comment/recommendation is highly appreciatedHave a wonderful weekend. thanks,Johnjohn |
|
|
|
|
Stored Procedures or Views