Stored Procedures
-
Should applications, which are for solely reporting purposes, rely on stored procedures or should the logic for the report be done on the app side? For example, if I need: Employees who have worked on Tuesday at least 5 out of 9 weeks prior to DateX, and also worked day after DateX, and have worked more than 90 days in the past 12 months If no DBA's are involved and I am the only developer, I can either 1. Write a stored procedure which will return the employee records which fit the above criteria OR 2. The client can get all the Employee records from the Employee table and Hours from the Hours table for the past 12 months and then perform the search for the criteria on the client side Which option is better? How much business logic should we write on the db side--is there really any limit or convention?
CodingYoshi Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07
-
Should applications, which are for solely reporting purposes, rely on stored procedures or should the logic for the report be done on the app side? For example, if I need: Employees who have worked on Tuesday at least 5 out of 9 weeks prior to DateX, and also worked day after DateX, and have worked more than 90 days in the past 12 months If no DBA's are involved and I am the only developer, I can either 1. Write a stored procedure which will return the employee records which fit the above criteria OR 2. The client can get all the Employee records from the Employee table and Hours from the Hours table for the past 12 months and then perform the search for the criteria on the client side Which option is better? How much business logic should we write on the db side--is there really any limit or convention?
CodingYoshi Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07
You won't be limited to client-side searching if you avoid sprocs. Clientside searches can also be initiated from inline SQL.
CodingYoshi wrote:
Which option is better? How much business logic should we write on the db side--is there really any limit or convention?
There are several, some of them are being defended religiously. I'd recommend using dOOdads or LINQ. dOOdads has the advantage that you can use the MyGeneration application to generate the framework and sprocs, and place the business-logic in the predefined BL-layer. For bonus-points, put the BL in it's own DLL. LINQ has the advantages of simplicity and elegance. --edit--
CodingYoshi wrote:
Employees who have worked on Tuesday at least 5 out of 9 weeks prior to DateX, and also worked day after DateX, and have worked more than 90 days in the past 12 months
These go by preferation in the database; if you decide to write another frontend (web, or a newly purchased control-library) then you'll already have your (by then already tested) BL in place. This also avoids duplication of the code. You got multiple ways to implement this. From a trigger, using a
RaiseError(with a low severity, or it will close the connection) to fire an event back to the UI. You could also use a sproc that returns a message or an error-id; I personally prefer to raise a non-severe error. Those things that can't be caught in the conceptual model of the database go in the BL-layer of your application. Those are the BL-rules that state to send a twitter-message or shut down a remote workstation. I'm curious to the other answers that will appear on this question :)I are troll :)
modified on Wednesday, July 8, 2009 8:57 AM
-
You won't be limited to client-side searching if you avoid sprocs. Clientside searches can also be initiated from inline SQL.
CodingYoshi wrote:
Which option is better? How much business logic should we write on the db side--is there really any limit or convention?
There are several, some of them are being defended religiously. I'd recommend using dOOdads or LINQ. dOOdads has the advantage that you can use the MyGeneration application to generate the framework and sprocs, and place the business-logic in the predefined BL-layer. For bonus-points, put the BL in it's own DLL. LINQ has the advantages of simplicity and elegance. --edit--
CodingYoshi wrote:
Employees who have worked on Tuesday at least 5 out of 9 weeks prior to DateX, and also worked day after DateX, and have worked more than 90 days in the past 12 months
These go by preferation in the database; if you decide to write another frontend (web, or a newly purchased control-library) then you'll already have your (by then already tested) BL in place. This also avoids duplication of the code. You got multiple ways to implement this. From a trigger, using a
RaiseError(with a low severity, or it will close the connection) to fire an event back to the UI. You could also use a sproc that returns a message or an error-id; I personally prefer to raise a non-severe error. Those things that can't be caught in the conceptual model of the database go in the BL-layer of your application. Those are the BL-rules that state to send a twitter-message or shut down a remote workstation. I'm curious to the other answers that will appear on this question :)I are troll :)
modified on Wednesday, July 8, 2009 8:57 AM
Thanks. I understand I can use LINQ but my question is whether to put the logic within the stored procedure or put it in the DAL (using LINQ) or whatever else. Anyone else?
CodingYoshi Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07
-
Should applications, which are for solely reporting purposes, rely on stored procedures or should the logic for the report be done on the app side? For example, if I need: Employees who have worked on Tuesday at least 5 out of 9 weeks prior to DateX, and also worked day after DateX, and have worked more than 90 days in the past 12 months If no DBA's are involved and I am the only developer, I can either 1. Write a stored procedure which will return the employee records which fit the above criteria OR 2. The client can get all the Employee records from the Employee table and Hours from the Hours table for the past 12 months and then perform the search for the criteria on the client side Which option is better? How much business logic should we write on the db side--is there really any limit or convention?
CodingYoshi Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07
I think the answer is, it depends. There are arguments for and against using stored procs. Arguments for: (i) A stored proc may well perform better. Dragging lots of data out of a database across a network onto an app server for further processing may well be a slow operation. Moving the logic down into the stored proc means that you are processing the data at source, without the need to transfer it across the network, which can sometimes be a big saving. (ii) the stored procedure logic can be reused from several different clients. Maybe you have one application written in VB, another in Java, both needing to access this same summary data. If you put all that logic into a stored proc, you only need to write it once. Arguments against: (i) SQL is not a procedural language. You can sometimes find yourself struggling with cursors and loops and temporary tables trying to work around the limitations of SQL. In those cases you might be better off doing it in the high level language of your choice (C#, Java, VB, Ruby, Python, whatever) where this sort of complex logic is easier to code. (Some modern databases let you write your stored procs in a language like C# or Java, which changes the landscape somewhat.) (ii) Some development shops like to minimise the number of languages they have to learn and support. This is particularly true if people are using some sort of data abstraction layer so that programmers are hidden from having to know SQL (LINQ, Hibernate, iBATIS, ActiveRecord, etc). Putting some of the logic into C# (or whatever your language is) and some into SQL means that either one developer has to know both languages, or the work is now split across two developers (who have to coordinate to make sure they are working in line with each other). I can't tell you which option to use for your application because I don't know enough about your requirements, your development environment or even your personal taste, all of which play a part in the decision.
-
Should applications, which are for solely reporting purposes, rely on stored procedures or should the logic for the report be done on the app side? For example, if I need: Employees who have worked on Tuesday at least 5 out of 9 weeks prior to DateX, and also worked day after DateX, and have worked more than 90 days in the past 12 months If no DBA's are involved and I am the only developer, I can either 1. Write a stored procedure which will return the employee records which fit the above criteria OR 2. The client can get all the Employee records from the Employee table and Hours from the Hours table for the past 12 months and then perform the search for the criteria on the client side Which option is better? How much business logic should we write on the db side--is there really any limit or convention?
CodingYoshi Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07
I'm prejudiced - need to get that out of the way first. I alway put the logic for a report in the stored proc, this for and I can make TSQL sit up and beg. Having said that I would not consider this a report, unless the structure of the request is stable which I don't think this one is. This, to me, is more of an analysis issue. I want to get at the database and find these type of people. We use SSRS and have a base rule NO intelligence in the report other than setting the parameter for the proc to use. Having said that the rule is flexible, any business will have a requirement that just has to be met and then we build a report just for that requirement and the logic could be anywhere that suits the developer. This type of request from a user would not even make it to the report development stage. We use ActiveQueryBuilder[^] and RadarSoft[^] cubes to give the user access to the data. Both have been wrapped up and get referenced by our apps. Radar Soft has a fairly steep learning curve but is well worth it. Query builder is just TSQL in the UI. I do not allow a set of key words to be passed in and they only have RO access to the database. these also allow us to implement another rule, no report longer than about 2 pages - the rest is a data dump.
Never underestimate the power of human stupidity RAH
-
Should applications, which are for solely reporting purposes, rely on stored procedures or should the logic for the report be done on the app side? For example, if I need: Employees who have worked on Tuesday at least 5 out of 9 weeks prior to DateX, and also worked day after DateX, and have worked more than 90 days in the past 12 months If no DBA's are involved and I am the only developer, I can either 1. Write a stored procedure which will return the employee records which fit the above criteria OR 2. The client can get all the Employee records from the Employee table and Hours from the Hours table for the past 12 months and then perform the search for the criteria on the client side Which option is better? How much business logic should we write on the db side--is there really any limit or convention?
CodingYoshi Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07
Thanks for the answers, it really has helped! After some research I have gathered the following: I think if the logic is not too complicated, the developer is comfortable with sql scripting, and has access to work on the db side then sometimes it is better to put the logic in the stored proc. This, just as David has pointed out, saves us the cost of transferring huge amount of data across. However, if the data is not simply for reporting purposes but it also might be updated, deleted etc. then it is not a good idea. Furthermore, we always have to be aware that if the app is to be installed on mobile device then we can not use the code because some compact database providers do not support sql stored procedures, for example, SQL CE. The other disadvantage is if we want to know why an employee did not qualify (did not work on Tuesday etc.) then things become a little messy. I mean there are still ways to get around it but they are not the best.
CodingYoshi Artificial Intelligence is no match for Human Stupidity.