|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stored Procs vs VIEWS: Seeking Comparisoncomparison: Stored Procs are faster than submitted SQL because Stored Procs are precompiled. Fine, but there's a third alternative, which I haven't seen mentioned at all. How do Stored Procedures compare with VIEWS? Now we have some pretty complex SQL statements (sample below) and it's obvious to me that having this precompiled into a Stored Proc is much more efficient than submitting this repetitively as a SQL request. But what if this is defined in a database VIEW? With this scenario, the submitted SQL is greatly simplified to SELECT * FROM VMSGRECIPIENTS2 WHERE ... How does this now compare with the equivalent Stored Procedure which would contain the same SQL definition as the VIEW, but which would pass in the selection criteria as parameters, rather than as a WHERE clause? Do you know of any articles which explictly compare Stored Procs to their equivalent VIEWS? (Of course I am aware of the many things which can be done via Stored Procs, which cnanot be done via VIEWS. That is not my issue. My issue concerns read-only operations which can be performed either via a VIEW or a Stored Proc. I'm formulating a database design policy and I'd be very grateful for any guidance with this specific issue.) Thank you very much for your advice. - Joe Geretz - CREATE VIEW "VMSGRECIPIENTS2" ( Columns... ) AS SELECT Columns... FROM MSGRECIPIENTS LEFT JOIN MSGMASTER ON (MSGMASTER.MSGID = MSGRECIPIENTS.MSGID) LEFT JOIN MSGROUTING ON (MSGROUTING.MSGID = MSGRECIPIENTS.MSGID) AND (MSGROUTING.ROUTINGID = MSGRECIPIENTS.ROUTINGID) LEFT JOIN PERSON ON (MSGMASTER.PERSONID = PERSON.PERSONID) LEFT JOIN USERGROUPS ON (USERGROUPS.GROUPID = MSGRECIPIENTS.ENTITYID) LEFT JOIN GROUPMEMBERSHIP ON (GROUPMEMBERSHIP.GROUPID = USERGROUPS.GROUPID) LEFT JOIN MSGPRIORITIES ON (MSGROUTING.PRIORITYID = MSGPRIORITIES.PRIORITYID) LEFT JOIN MSGSUBJECTS ON (MSGROUTING.SUBJECTID = MSGSUBJECTS.SUBJECTID) LEFT JOIN DOCUMENTS ON (MSGMASTER.ATTACHMENTS = DOCUMENTS.DOCUMENTID) GO > Every article which I've seen proposing Stored Procedures makes the same Views are not used for improving performance (unless you are talking about > comparison: Stored Procs are faster than submitted SQL because Stored > Procs are precompiled. Fine, but there's a third alternative, which I > haven't seen mentioned at all. How do Stored Procedures compare with > VIEWS? indexed views or distributed partitioned views). Even there, you can use stored procedures to access the view, instead of just saying "SELECT * FROM ViewName" in your application code. A common misconception is that a view somehow caches or stores the data that it returns, and this is not the case (again, in "normal" views). Typically, views are used to reduce the complexity of a table's schema or the relationship of multiple tables, for example a view called CustomerData might include the customerID from Customers, the primary address from the CustomerAddresses table, and the most recent order from the Orders table. Now, a user writing a SELECT statement (or a stored procedure that uses a SELECT statement) doesn't have to understand all of those relationships. Another typical purpose for a view is to restrict access to certain data. For example, in the Employees database, there is probably at least one table with 401k/salary information that you don't want exposed to all users. So you might have very restricted permissions on the table itself, and then create a view that leaves out those columns, and give more ready access to that view so that the average user can still get a list of all the employees, but won't be able to see which ones make more money... > read-only operations which can be performed either via a VIEW or a Stored I think that's a very narrow and limited view, no pun intended. You can > Proc. certainly use both -- stored procedures for performance/encapsulation, and views for schema/query/relationship simplification and/or security. Thanks Aaron, for your enlightening reply.
> I think that's a very narrow and limited view, no pun intended. You can Actually, I'm trying to be as broadminded as possible. However, our team of > certainly use both -- stored procedures for performance/encapsulation, and > views for schema/query/relationship simplification and/or security. half-a-dozen developers is in a position where we need to impose standards. We can't simply allow developers to individually adopt whatever approach they happen to prefer. So, in the sense that standards are a limiting factor, you are correct; that's what I'm trying to do. However, I don't intend to be fanatical by any means. Aside from the performance issue, VIEWS provide a few significant advantges: 1. They are flexible and can easily accommodate any WHERE clause. 2. They are absolutely transparent in terms of being READ-ONLY operations 3. They are comprised of ANSII SQL which is portable between database vendors. Neither of these are true of Stored Procedures. For a Stored Proc to be flexible, it must implement logic to determine which parameters are being supplied. And a Stored Proc which returns a recordset might be subsequently modified to update data, in a way which might not be obvious to maintenance programmers. And Stored Proc dialect is very often vendor specific, rendering the Stored Proc non-portable. I'm thinking of imposing the following standard: My Preferred Policy: --------------------- 1. READ-ONLY operations are performed via VIEWS 2. UPDATE operations performed via Stored Procs As opposed to: Techie Performance Policy: ---------------------------- 1. All operations performed via Stored Procs Although, if someone could demonstrate to me that Stored Procedure performance is significantly greater than that which is provided by a similar VIEW, I might feel that compelling enough to shift from My Preferred Policy to implement all operations via Stored Procedures (the Techie Performance Policy). So far, I'm not at all convinced that this is the case, or that even if it were, that the performance benefit would outweigh other considerations. If I understand you correctly, you feel that my preferred policy is reasonable? Thanks, - Joe Geretz - Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:OgN7W65kFHA.1464@TK2MSFTNGP14.phx.gbl... >> Every article which I've seen proposing Stored Procedures makes the same >> comparison: Stored Procs are faster than submitted SQL because Stored >> Procs are precompiled. Fine, but there's a third alternative, which I >> haven't seen mentioned at all. How do Stored Procedures compare with >> VIEWS? > > Views are not used for improving performance (unless you are talking about > indexed views or distributed partitioned views). Even there, you can use > stored procedures to access the view, instead of just saying "SELECT * > FROM ViewName" in your application code. A common misconception is that a > view somehow caches or stores the data that it returns, and this is not > the case (again, in "normal" views). > > Typically, views are used to reduce the complexity of a table's schema or > the relationship of multiple tables, for example a view called > CustomerData might include the customerID from Customers, the primary > address from the CustomerAddresses table, and the most recent order from > the Orders table. Now, a user writing a SELECT statement (or a stored > procedure that uses a SELECT statement) doesn't have to understand all of > those relationships. > > Another typical purpose for a view is to restrict access to certain data. > For example, in the Employees database, there is probably at least one > table with 401k/salary information that you don't want exposed to all > users. So you might have very restricted permissions on the table itself, > and then create a view that leaves out those columns, and give more ready > access to that view so that the average user can still get a list of all > the employees, but won't be able to see which ones make more money... > >> read-only operations which can be performed either via a VIEW or a Stored >> Proc. > > I think that's a very narrow and limited view, no pun intended. You can > certainly use both -- stored procedures for performance/encapsulation, and > views for schema/query/relationship simplification and/or security. > > 1. They are flexible and can easily accommodate any WHERE clause. I'm not sure that this is always a benefit. You may have business rules that should leave out certain types of queries, e.g. where salary > 100000 or where 1=1... > 2. They are absolutely transparent in terms of being READ-ONLY operations Careful here. Views *can* be updateable, and users will try, believe me, especially if the base tables are obscured from them and/or they don't know that what they're interfacing with is, in fact, a view. > 3. They are comprised of ANSII SQL which is portable between database Well, if you think you're on the verge of jumping between platforms, this > vendors. might be an issue. In the real world (not Celko's world), this happens far less frequently than you would think, given how often this is mentioned as a priority (usually by Celko). I hear Access or MSDE to SQL Server once in a while, and the occasional MySQL to SQL Server. Oracle <-> SQL Server or DB2 <-> SQL Server? Not once, in as many years as I have been an engineer. That doesn't mean it never happens, but due to its relative scarcity, I don't think it should be priority #1 in every single project that involves any kind of database. Personally, I don't buy the general argument that you must always think this way; I think you need to weight the disadvantages of using pure, platform-independent techniques against the likelihood that you will ever change platforms. Anyway, using stored procedures does not preclude you from making a graceful port, even if that is a likely scenario. I would argue that it makes it easier. If you make the assumption that changing the syntax of a stored procedure is the biggest problem you will face in such a monumental platform change, never mind changing all these direct SELECTs from views buried throughout your application code (with the possibility of proprietary syntax just as likely there as in a stored procedure), I think that you will learn a lot when you do go ahead with this alleged port. > flexible, it must implement logic to determine which parameters are being I'm not sure why you think it is difficult to have optional parameters to > supplied. your stored procedures? I'd say about half of the stored procedures I write have optional parameters, and this has never seemed like a chore to me, and certainly not compelling enough to make me think that I should create a view so that I can use "easier" dynamic where clause construction in my application code or in the middle tier somewhere. > And a Stored Proc which returns a recordset might be subsequently modified This sounds like a process issue. The application could just as easily > to update data, in a way which might not be obvious to maintenance > programmers. write an ad hoc UPDATE statement that the VIEW designers are unaware of. This sounds more like a process problem than a technology decision point. > And Stored Proc dialect is very often vendor specific, rendering the If the stored proc is just a SELECT that could be replaced by a view, then > Stored Proc non-portable. it could just as easily be replaced by a view that uses the same proprietary and vendor-specific syntax (e.g. GETDATE(), CONVERT(), ISNULL(), UDFs). Again, I don't see how this makes you better prepared for a port, since the majority of the *structure* (not the query code) will either be unchanged or a simple grep. The kinds of things that can get you into trouble in a SQL query can bite you exactly the same way whether you use a view, or a stored procedure, or ad hoc SQL. > My Preferred Policy: I still don't see the benefit of having application code say "SELECT * FROM > --------------------- > 1. READ-ONLY operations are performed via VIEWS CustomersView" instead of "EXEC GetCustomers". (And don't think that SELECT * won't happen, which is another thing you can control better with procs.) > Although, if someone could demonstrate to me that Stored Procedure Where did you get the idea that a view will improve performance? As I said > performance is significantly greater than that which is provided by a > similar VIEW, before, the data / query plan for a view is not cached anymore, the only benefit to the view is that you don't have to type it all out. The query is still analyzed and executed as the code in the view is read, just as if you had passed in an ad hoc SQL statement that looks just like the view. You make the mistaken assumption that they are interchangeable. They are not. They both exist for their own individual reasons; this is not a case of 6 of one or a half dozen of the other. > So far, I'm not at all convinced that this is the case, Maybe you need to look at it the other way. Convince yourself that you do (or don't) get any performance benefit from views over straight SQL code. Do some significant research on your own systems, comparing throughput, I/O, timings, memory usage etc. when you run all of your queries through procs, views, and passed-in ad hoc SQL. > If I understand you correctly, you feel that my preferred policy is No. I don't like ad hoc SQL (regardless of whether it references views or > reasonable? base tables) in application code. Period. And while Celko will disagree with me about the priority of preparing yourself for an eventual port, I'm farily certain he will agree with me that ad hoc SQL has no place in the application/presentation tiers... You seem to be trying to force an aritificially strong case for views based on your perception. Because of this, I don't have any confidence whatsoever that I will convince you otherwise. But I do hope that I have at least spurred you to investigate some of your assumptions and think about the other issues I have mentioned. >> 1. They are flexible and can easily accommodate any WHERE clause. True, but we're a VB development shop. What is the compelling argument to> > I'm not sure that this is always a benefit. You may have business rules > that should leave out certain types of queries, e.g. where salary > 100000 > or where 1=1... implement our business rules in T-SQL (in the database) rather than in VB (business logic tier)? Should applications be constructed of a UI tier (in either ASP or VB depending on the nature of the app) with the business processing implemented with T-SQL inside the database? >> 2. They are absolutely transparent in terms of being READ-ONLY operations You misunderstood me. What I meant to say is that if a programmer sees> > Careful here. Views *can* be updateable, and users will try, believe me, > especially if the base tables are obscured from them and/or they don't > know that what they're interfacing with is, in fact, a view. SELECT * from VW_MYVIEW in code, they KNOW that no update is being performed. (Assuming that VW_MYVIEW is a VIEW. But if I have a developer naming their Stored Procs VW_ANYTHING, they're outta here! ;-) But wherever a Stored Proc is called, even if it returns a recordset and the application context suggests a strict READ ONLY operation, developers can't really can't be sure unless they dive down into the SPROC to see what's going on. > Well, if you think you're on the verge of jumping between platforms, this Absolutely, I concede this point. This is not necessarily priority #1. Just> might be an issue. In the real world (not Celko's world), this happens > far less frequently than you would think, given how often this is > mentioned as a priority (usually by Celko). I hear Access or MSDE to SQL > Server once in a while, and the occasional MySQL to SQL Server. Oracle > <-> SQL Server or DB2 <-> SQL Server? Not once, in as many years as I > have been an engineer. > That doesn't mean it never happens, but due to its relative scarcity, I > don't think it should be priority #1 in every single project that involves > any kind of database. one thing to think about when weighing all the arguments. But there ARE numerous things to consider, not just raw performance implications. (And just to prove that I can be objective when weighing the issues, we just *did* complete a migration from Interbase to SQL Server. Tables, VIEWS, ANSI SQL statements migrated naturally, but Triggers (we didn't have many SProcs back then) and idiotic operations like SQL concatenation syntaxes were an immense effort. However, looking forward I am willing to concede that this is unlikely to happen again in the foreseeable future. Although it is a possibility. Our software is a commercial product and we'd not like to lose a sale because we'd be unable to accommodate an Oracle environment.) > I still don't see the benefit of having application code say "SELECT * Dou you know what is happening in GetCustomers? I don't. Is any data> FROM CustomersView" instead of "EXEC GetCustomers". updating taking place? Who knows? But if you say SELECT * FROM CustomersVIEW I guarantee that no updating is taking place. > You seem to be trying to force an aritificially strong case for views Hmm. We definitely have two different perspectives looking at the same> based on your perception. thing. It seems to me, that you are strongly in favor of Stored Procedures. But why? Is it the performance factor? Is performance necessarily priority #1 for all scenarios? - Joe Geretz - > True, but we're a VB development shop. What is the compelling argument to Most would argue that those rules belong in the database, not in VB code. > implement our business rules in T-SQL (in the database) rather than in VB > (business logic tier)? Ugh. Some would argue the opposite. I think you know where my opinion sits. > a Stored Proc is called, even if it returns a recordset and the If they're that concerned that a procedure with a name like GetCustomers > application > context suggests a strict READ ONLY operation, developers can't really > can't > be sure unless they dive down into the SPROC to see what's going on. might do more stuff behind their backs, never mind that someone might have maliciously coded such a procedure to do evil things, it only takes 5 seconds to issue an sp_helptext and see if there is just a SELECT or if there is something more. > But there ARE numerous things to consider, not just raw performance Absolutely. I outlined several advantages for using stored procedures > implications. earlier. This does not mean "don't use views" I just feel very strongly that you can use views where appropriate *and* still abide by the commandment "thine apps shalt access data via procedures"... > Dou you know what is happening in GetCustomers? I don't. Again, if this really is a concern among your developers, they can issue sp_helptext. If they ask for a procedure that returns a resultset, and they suspect that you might have done something different/extra (or find out that you did), then I would say, again, that this is more of a process problem than a symptom of a poor choice in technology. > It seems to me, that you are strongly in favor of Stored Procedures. No, there are several things... separation of business/data logic from the > But why? Is it the performance factor? application code. Elimination of re-writing and re-factoring of code... not only to prevent multiple people from writing (potentially different) queries that are meant to do the same things but don't, but also going and changing all of that ad hoc code in all places instead of one place. > Is performance necessarily priority Absolutely not. In fact, that is not my overriding or only reason for using > #1 for all scenarios? SPs, because anyone can write an SP that performs like a dog (in other words, putting a bad query in a stored procedure does not magically make it a good query). In your original post, that is the only factor you mentioned, so that is what I focused on first. You still sound like you have two options: (a) use views, or (b) use stored procedures. It doesn't seem like I am making any headway with the link of thinking that you can (c) use both??? A Hi Aaron,
Again, thanks for sharing your perspective. I found the following statement to be very interesting: > No, there are several things... separation of business/data logic from the Interesting. The classic n-tier scenario involves three tiers; UI, Business > application code. and Data. Were I to collapse these into two physical tiers, (e.g. classic thick Windows Client / Server architecture) I'd be first inclined to propose a UI / Business tier (the Win32 Client) and Data tier (e.g. SQL Server), rather than a UI layer and a Business / Data layer as you propose. I don't think it's useful to go much further down this road, except to concede that both architectures have advantages and disadvantages and each application should be evaluated on its own merits. > You still sound like you have two options: (a) use views, or (b) use I don't know why you feel this way. Did I not state clearly that my > stored procedures. It doesn't seem like I am making any headway with the > link of thinking that you can (c) use both??? preferred policy is to use a mix of both of these, with just one clear guideline on when one would be used versus that other? My Preferred Policy: --------------------- 1. READ-ONLY operations are performed via VIEWS 2. UPDATE operations performed via Stored Procs Some might feel that the guidelines are too restrictive. On the other hand, we have found guidelines in general to be extremely useful in creating a consistent environment where development tasks and code modules are easily intechangable between developers. In a nutshell - there are good standards and bad standards. But worst of all, are no standards. - Joe Geretz - Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:ewmI2j9kFHA.4012@TK2MSFTNGP10.phx.gbl... >> True, but we're a VB development shop. What is the compelling argument to >> implement our business rules in T-SQL (in the database) rather than in VB >> (business logic tier)? > > Most would argue that those rules belong in the database, not in VB code. > Ugh. Some would argue the opposite. I think you know where my opinion > sits. > >> a Stored Proc is called, even if it returns a recordset and the >> application >> context suggests a strict READ ONLY operation, developers can't really >> can't >> be sure unless they dive down into the SPROC to see what's going on. > > If they're that concerned that a procedure with a name like GetCustomers > might do more stuff behind their backs, never mind that someone might have > maliciously coded such a procedure to do evil things, it only takes 5 > seconds to issue an sp_helptext and see if there is just a SELECT or if > there is something more. > >> But there ARE numerous things to consider, not just raw performance >> implications. > > Absolutely. I outlined several advantages for using stored procedures > earlier. This does not mean "don't use views" I just feel very strongly > that you can use views where appropriate *and* still abide by the > commandment "thine apps shalt access data via procedures"... > >> Dou you know what is happening in GetCustomers? I don't. > > Again, if this really is a concern among your developers, they can issue > sp_helptext. If they ask for a procedure that returns a resultset, and > they suspect that you might have done something different/extra (or find > out that you did), then I would say, again, that this is more of a process > problem than a symptom of a poor choice in technology. > >> It seems to me, that you are strongly in favor of Stored Procedures. >> But why? Is it the performance factor? > > No, there are several things... separation of business/data logic from the > application code. Elimination of re-writing and re-factoring of code... > not only to prevent multiple people from writing (potentially different) > queries that are meant to do the same things but don't, but also going and > changing all of that ad hoc code in all places instead of one place. > >> Is performance necessarily priority >> #1 for all scenarios? > > Absolutely not. In fact, that is not my overriding or only reason for > using SPs, because anyone can write an SP that performs like a dog (in > other words, putting a bad query in a stored procedure does not magically > make it a good query). In your original post, that is the only factor you > mentioned, so that is what I focused on first. > > You still sound like you have two options: (a) use views, or (b) use > stored procedures. It doesn't seem like I am making any headway with the > link of thinking that you can (c) use both??? > > A > > 1. READ-ONLY operations are performed via VIEWS My suggestion remains to have both read and write data access be performed > 2. UPDATE operations performed via Stored Procs through stored procedures. You have yet to convince me of any good reason for read access to be performed via views that outweighs any (never mind all) of the benefits of using stored procedures (possibly in addition to views). I think you are spending far too much time over-analyzing how your developers will feel about what a stored procedure is doing other than being an interface to retrieve data. Perhaps this is due to a bad experience, perhaps paranoia, not sure. For making your application more robust and easier to use, and if you continue to insist on using views as a replacement for (instead of an enhancement to) stored procedures, then please at least study the potential use of indexed views... > In a nutshell - there are good standards and bad standards. But worst of I don't think I ever suggested that you shouldn't have a standard. I just > all, are no standards. don't agree with the one that you have already clearly decided on. But hey, it's your application, and your set of developers. You asked for input, I provided it, it's unfortunate that all of the time I've spent in this thread has accomplished nothing. :-( > You asked for input, I provided it, it's unfortunate that all of the time If you measure accomplishment as the exchange of information, and afford > I've spent in this thread has accomplished nothing. :-( your audience enough credibility to allow them to make their own determination according to their particular needs, then you need not feel your efforts misspent at all. :-) If, on the other hand, you measure your accomplishment by whether or not you manage to pursuade your audience to march in lockstep in accordance with your own personal approach, then indeed you have accomplished nothing. :-( It all depends on your perspective. Thank you for your insights which you have provided. - Joe Geretz - Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eWpoMG%23kFHA.3312@tk2msftngp13.phx.gbl... >> 1. READ-ONLY operations are performed via VIEWS >> 2. UPDATE operations performed via Stored Procs > > My suggestion remains to have both read and write data access be performed > through stored procedures. You have yet to convince me of any good reason > for read access to be performed via views that outweighs any (never mind > all) of the benefits of using stored procedures (possibly in addition to > views). I think you are spending far too much time over-analyzing how > your developers will feel about what a stored procedure is doing other > than being an interface to retrieve data. Perhaps this is due to a bad > experience, perhaps paranoia, not sure. > > For making your application more robust and easier to use, and if you > continue to insist on using views as a replacement for (instead of an > enhancement to) stored procedures, then please at least study the > potential use of indexed views... > >> In a nutshell - there are good standards and bad standards. But worst of >> all, are no standards. > > I don't think I ever suggested that you shouldn't have a standard. I just > don't agree with the one that you have already clearly decided on. But > hey, it's your application, and your set of developers. You asked for > input, I provided it, it's unfortunate that all of the time I've spent in > this thread has accomplished nothing. :-( >
Show quote
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message First, Stored Procedures have only a very small performance advantage over news:OR6S7q6kFHA.3552@TK2MSFTNGP10.phx.gbl... >> 1. They are flexible and can easily accommodate any WHERE clause. > .... > > No. I don't like ad hoc SQL (regardless of whether it references views or > base tables) in application code. Period. And while Celko will disagree > with me about the priority of preparing yourself for an eventual port, I'm > farily certain he will agree with me that ad hoc SQL has no place in the > application/presentation tiers... > > You seem to be trying to force an aritificially strong case for views > based on your perception. Because of this, I don't have any confidence > whatsoever that I will convince you otherwise. But I do hope that I have > at least spurred you to investigate some of your assumptions and think > about the other issues I have mentioned. > parameterized SQL. So if you don't build your WHERE clause with string concatenation (which you shouldn't do anyway for security reasons), then there's very little to choose between ad-hoc SQL and stored procedures. SQL Server simply hashes your incoming query and looks up a previously stored and optimized plan. Second, I usually use both views and stored procedures. Broadly speaking here block diagrams of three database access designs which can work. Each requires a different mix of skills and technologies and each has its own pitfalls. But depending on the team, the skillsets and the application technology each one merits consideration. 1 Stored Procedure Facade Model --------------------------------------- Application Code --------------------------------------- Stored Procedures --------------------------------------- |Views | Table Valued UDFs BaseTables--------------------------------------- --------------------------------------- 2 Mixed Model ------------------------------------------------ Application Code ------------------------------------------------ Stored Procedures |Views | Table Valued UDFs ------------------------------------------------ BaseTables ------------------------------------------------ 3 Direct Access Model ------------------------------------------------ Application Code ------------------------------------------------ Automated Mapping Tool ------------------------------------------------ BaseTables ------------------------------------------------ David > First, Stored Procedures have only a very small performance advantage over And nowhere did I say that performance is the most important, never mind the > parameterized SQL. only, advantage to using stored procedures. :-) The OP seemed to be focusing on performance as the only deciding factor between views and stored procedures, and only when challenged on that fact did other pros/cons come up. A > The OP seemed to be focusing on performance as the only deciding factor Maybe you misundestood me. My evaluation is NOT based only on performance. > between views and stored procedures, and only when challenged on that fact > did other pros/cons come up. If that were the only issue, I'd be slam-dunk in favor of Stored Procedures. Even though there seems to be controversy on whether Stored Procs do indeed yield better performance, I don't see anyone asserting that they are slower. So if performance were the only factor I'd have decided on Stored Procedures by now. But performance is *not* the only factor I am considering on either side of the equation. The previous post (my response to your earlier post) which I sent a couple hours ago mysteriously vanished, but I've since reposted. If you have some time to add additional elaboration to my response to your earlier post, I'd be most appreciative. Thanks! - Joe Geretz - >> if you think you're on the verge of jumping between platforms, this might be an issue. In the real world (not Celko's world), this happens far less frequently than you would think, given how often this is mentioned as a priority (usually by Celko). << "Celko's world" ?? I will wait until you do a Data Warehouse and runinto exactly how much of a priority data and code migration can be :) > "Celko's world" ?? I will wait until you do a Data Warehouse and run I have worked in data warehouse projects, and just like large OLTP > into exactly how much of a priority data and code migration can be :) applications, there was never any such thing as a willy-nilly "oh let's switch to something else"... Anyway, let's leave those discussions for the OLAP/data warehousing groups. I thought we were talking about OLTP here? >> My Preferred Policy: Joseph, Joseph, Joseph....>> ... >> As opposed to: >> Techie Performance Policy: Your use of the word "Techie" has a few possible meanings - none of which I think are positive. "My policy" vs "Techy policy" suggests that you are not very technically literate (somewhat, but not very strong here), but you are also trying to impose your views on people who are very technically literate (at least moreso than yourself). That's much like a hospital administrator (MBA type with no med school background) telling the doctors how to operate on the patients. You will soon be resented if you are not already resented by your "techie" subordinates as YOUR own fears and lack of expertise in THEIR knowledge domain comes to limit what they do in some rather rather uninformed ways (ininformed as defined by your intentions). There are other ways to implement standards. Trying to go to more restrictive technologies, which is apparently why you like VIEWS so much (misguided as your perception is), is a very Ludite thing to be doing (look it up - it will describe your tendencies). Rather than trying to control your developers and DBAs, why not ASK THEM (who know way more than you do in their respective knowledge domains) for THEIR IDEAS for streamlining things and for imposing standards. Perhaps they will suggest peer code reviews, a department-wide standard for stored procedures AND views where each is appropriate, etc. These people know how to do their job. They need someone in your position to FACILITATE communication and COORDINATE development of standards amongst them; NOT limit what and how they do things in significant and rediculous ways (like coming up with mandate described in your My Preferred Policy). If you don't trust your developers and feel you need to control them in order to compensate for your lack of expertise, then perhaps it's time for you to read the book, "Becoming a Technical Leader" by Geral M. Weinberg. -F Show quote "Joseph Geretz" <jgeretz@nospam.com> wrote in message news:%23vBreY6kFHA.3312@tk2msftngp13.phx.gbl... > Thanks Aaron, for your enlightening reply. > >> I think that's a very narrow and limited view, no pun intended. You can >> certainly use both -- stored procedures for performance/encapsulation, >> and views for schema/query/relationship simplification and/or security. > > Actually, I'm trying to be as broadminded as possible. However, our team > of half-a-dozen developers is in a position where we need to impose > standards. We can't simply allow developers to individually adopt whatever > approach they happen to prefer. So, in the sense that standards are a > limiting factor, you are correct; that's what I'm trying to do. However, I > don't intend to be fanatical by any means. > > Aside from the performance issue, VIEWS provide a few significant > advantges: > > 1. They are flexible and can easily accommodate any WHERE clause. > 2. They are absolutely transparent in terms of being READ-ONLY operations > 3. They are comprised of ANSII SQL which is portable between database > vendors. > > Neither of these are true of Stored Procedures. For a Stored Proc to be > flexible, it must implement logic to determine which parameters are being > supplied. And a Stored Proc which returns a recordset might be > subsequently modified to update data, in a way which might not be obvious > to maintenance programmers. And Stored Proc dialect is very often vendor > specific, rendering the Stored Proc non-portable. > > I'm thinking of imposing the following standard: > > My Preferred Policy: > --------------------- > 1. READ-ONLY operations are performed via VIEWS > 2. UPDATE operations performed via Stored Procs > > As opposed to: > > Techie Performance Policy: > ---------------------------- > 1. All operations performed via Stored Procs > > Although, if someone could demonstrate to me that Stored Procedure > performance is significantly greater than that which is provided by a > similar VIEW, I might feel that compelling enough to shift from My > Preferred Policy to implement all operations via Stored Procedures (the > Techie Performance Policy). So far, I'm not at all convinced that this is > the case, or that even if it were, that the performance benefit would > outweigh other considerations. > > If I understand you correctly, you feel that my preferred policy is > reasonable? > > Thanks, > > - Joe Geretz - > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:OgN7W65kFHA.1464@TK2MSFTNGP14.phx.gbl... >>> Every article which I've seen proposing Stored Procedures makes the same >>> comparison: Stored Procs are faster than submitted SQL because Stored >>> Procs are precompiled. Fine, but there's a third alternative, which I >>> haven't seen mentioned at all. How do Stored Procedures compare with >>> VIEWS? >> >> Views are not used for improving performance (unless you are talking >> about indexed views or distributed partitioned views). Even there, you >> can use stored procedures to access the view, instead of just saying >> "SELECT * FROM ViewName" in your application code. A common >> misconception is that a view somehow caches or stores the data that it >> returns, and this is not the case (again, in "normal" views). >> >> Typically, views are used to reduce the complexity of a table's schema or >> the relationship of multiple tables, for example a view called >> CustomerData might include the customerID from Customers, the primary >> address from the CustomerAddresses table, and the most recent order from >> the Orders table. Now, a user writing a SELECT statement (or a stored >> procedure that uses a SELECT statement) doesn't have to understand all of >> those relationships. >> >> Another typical purpose for a view is to restrict access to certain data. >> For example, in the Employees database, there is probably at least one >> table with 401k/salary information that you don't want exposed to all >> users. So you might have very restricted permissions on the table >> itself, and then create a view that leaves out those columns, and give >> more ready access to that view so that the average user can still get a >> list of all the employees, but won't be able to see which ones make more >> money... >> >>> read-only operations which can be performed either via a VIEW or a >>> Stored Proc. >> >> I think that's a very narrow and limited view, no pun intended. You can >> certainly use both -- stored procedures for performance/encapsulation, >> and views for schema/query/relationship simplification and/or security. >> > > Hi Frankie,
> coming up with mandate described in your My Preferred Policy). If you I guess that someone who makes gross assumptions about another individual > don't trust your developers and feel you need to control them in order to > compensate for your lack of expertise, then perhaps it's time for you to > read the book, "Becoming a Technical Leader" by Geral M. Weinberg. without having anywhere near a sound basis for making these assumptions is probably the last person to advise me on becoming any sort of leader, technical or otherwise. With almost two decades of development work behind me, I'm in an excellent position to *balance* the logistics of team development with the technical realities of the environment in which we operate. And for the record, I am proud to call myself a Techie. From my perspective, I'd be more likely to use the term Paper-Pusher as a pejorative. Thanks for the free advice. I'm sure it's worth every penny! - Joe Geretz - Show quote "Frankie" <A@B.COM> wrote in message news:eDEE0v8kFHA.320@TK2MSFTNGP09.phx.gbl... >>> My Preferred Policy: >>> ... >>> As opposed to: >>> Techie Performance Policy: > > Joseph, Joseph, Joseph.... > > Your use of the word "Techie" has a few possible meanings - none of which > I think are positive. "My policy" vs "Techy policy" suggests that you are > not very technically literate (somewhat, but not very strong here), but > you are also trying to impose your views on people who are very > technically literate (at least moreso than yourself). That's much like a > hospital administrator (MBA type with no med school background) telling > the doctors how to operate on the patients. You will soon be resented if > you are not already resented by your "techie" subordinates as YOUR own > fears and lack of expertise in THEIR knowledge domain comes to limit what > they do in some rather rather uninformed ways (ininformed as defined by > your intentions). There are other ways to implement standards. Trying to > go to more restrictive technologies, which is apparently why you like > VIEWS so much (misguided as your perception is), is a very Ludite thing to > be doing (look it up - it will describe your tendencies). Rather than > trying to control your developers and DBAs, why not ASK THEM (who know way > more than you do in their respective knowledge domains) for THEIR IDEAS > for streamlining things and for imposing standards. Perhaps they will > suggest peer code reviews, a department-wide standard for stored > procedures AND views where each is appropriate, etc. These people know how > to do their job. They need someone in your position to FACILITATE > communication and COORDINATE development of standards amongst them; NOT > limit what and how they do things in significant and rediculous ways (like > coming up with mandate described in your My Preferred Policy). If you > don't trust your developers and feel you need to control them in order to > compensate for your lack of expertise, then perhaps it's time for you to > read the book, "Becoming a Technical Leader" by Geral M. Weinberg. > > -F > > > > "Joseph Geretz" <jgeretz@nospam.com> wrote in message > news:%23vBreY6kFHA.3312@tk2msftngp13.phx.gbl... >> Thanks Aaron, for your enlightening reply. >> >>> I think that's a very narrow and limited view, no pun intended. You can >>> certainly use both -- stored procedures for performance/encapsulation, >>> and views for schema/query/relationship simplification and/or security. >> >> Actually, I'm trying to be as broadminded as possible. However, our team >> of half-a-dozen developers is in a position where we need to impose >> standards. We can't simply allow developers to individually adopt >> whatever approach they happen to prefer. So, in the sense that standards >> are a limiting factor, you are correct; that's what I'm trying to do. >> However, I don't intend to be fanatical by any means. >> >> Aside from the performance issue, VIEWS provide a few significant >> advantges: >> >> 1. They are flexible and can easily accommodate any WHERE clause. >> 2. They are absolutely transparent in terms of being READ-ONLY operations >> 3. They are comprised of ANSII SQL which is portable between database >> vendors. >> >> Neither of these are true of Stored Procedures. For a Stored Proc to be >> flexible, it must implement logic to determine which parameters are being >> supplied. And a Stored Proc which returns a recordset might be >> subsequently modified to update data, in a way which might not be obvious >> to maintenance programmers. And Stored Proc dialect is very often vendor >> specific, rendering the Stored Proc non-portable. >> >> I'm thinking of imposing the following standard: >> >> My Preferred Policy: >> --------------------- >> 1. READ-ONLY operations are performed via VIEWS >> 2. UPDATE operations performed via Stored Procs >> >> As opposed to: >> >> Techie Performance Policy: >> ---------------------------- >> 1. All operations performed via Stored Procs >> >> Although, if someone could demonstrate to me that Stored Procedure >> performance is significantly greater than that which is provided by a >> similar VIEW, I might feel that compelling enough to shift from My >> Preferred Policy to implement all operations via Stored Procedures (the >> Techie Performance Policy). So far, I'm not at all convinced that this is >> the case, or that even if it were, that the performance benefit would >> outweigh other considerations. >> >> If I understand you correctly, you feel that my preferred policy is >> reasonable? >> >> Thanks, >> >> - Joe Geretz - >> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in >> message news:OgN7W65kFHA.1464@TK2MSFTNGP14.phx.gbl... >>>> Every article which I've seen proposing Stored Procedures makes the >>>> same comparison: Stored Procs are faster than submitted SQL because >>>> Stored Procs are precompiled. Fine, but there's a third alternative, >>>> which I haven't seen mentioned at all. How do Stored Procedures compare >>>> with VIEWS? >>> >>> Views are not used for improving performance (unless you are talking >>> about indexed views or distributed partitioned views). Even there, you >>> can use stored procedures to access the view, instead of just saying >>> "SELECT * FROM ViewName" in your application code. A common >>> misconception is that a view somehow caches or stores the data that it >>> returns, and this is not the case (again, in "normal" views). >>> >>> Typically, views are used to reduce the complexity of a table's schema >>> or the relationship of multiple tables, for example a view called >>> CustomerData might include the customerID from Customers, the primary >>> address from the CustomerAddresses table, and the most recent order from >>> the Orders table. Now, a user writing a SELECT statement (or a stored >>> procedure that uses a SELECT statement) doesn't have to understand all >>> of those relationships. >>> >>> Another typical purpose for a view is to restrict access to certain >>> data. For example, in the Employees database, there is probably at least >>> one table with 401k/salary information that you don't want exposed to >>> all users. So you might have very restricted permissions on the table >>> itself, and then create a view that leaves out those columns, and give >>> more ready access to that view so that the average user can still get a >>> list of all the employees, but won't be able to see which ones make more >>> money... >>> >>>> read-only operations which can be performed either via a VIEW or a >>>> Stored Proc. >>> >>> I think that's a very narrow and limited view, no pun intended. You can >>> certainly use both -- stored procedures for performance/encapsulation, >>> and views for schema/query/relationship simplification and/or security. >>> >> >> > > Well put, Joe.
I don't think that there is as much of a performance benefit to stored procedures as there used to be in earlier versions of SQL Server, so performance is not really an issue. However, your argument for portability as an decision factor in using Views vs Stored Procs is betrayed by your own personal policy; if you're using procs to INSERT or UPDATE data, then there is no guarantee that those same procs will run on any other database server either. You may have to rewrite them as well, so apart from saving yourself the keystrokes on your SELECT procedures, you haven't really gained anything by NOT using stored procedures for SELECT statements (in regards to portability). <snip> Dou you know what is happening in GetCustomers? I don't. Is any data updating taking place? Who knows? But if you say SELECT * FROM CustomersVIEW I guarantee that no updating is taking place. </snip> I would have thought that a VB programmer (or is it developer, or Techie, or architect? :) ) would have more appreciation for the whole "black box" approach to a stored procedure; I agree that it's a bit of a crossover of business logic into the data tier, and that may be enough for the theorists to HATE stored procedures, but unless you're willing to fully develop your business logic tier in between your application and your data layer, stored procedures do give you some flexibility and code re-use without passing straight SQL from your app. If you write stored procedures that use vendor-specific SQL, you can optimize the performance of your application on that database platform. In other words, your app may SMOKE on SQL Server, and not run at all on Oracle. Your competitor may have an app that runs OK on either platform; which is better for your potential customers? The best scenario would be for you to invest not in a portable option, but in different versions of your application that are optimized for their respecitive database platforms. Much more expensive to implement, but the ideal solution. I guess if you're concerned about portability, I would choose neither view nor stored procedure; instead, I would probably build a map within your business logic tier that maps the appropriate application objects and methods to the correct data entities (you could use views to simply the schema, but if you're inserting and updating tables, why bother?). You'll still be passing complex SQL statements from this middle tier to your data source (without the potential gain of database-specific optimization), but your data source would be hidden from the application (and thus more portable), and your business logic would be isolated from your datasource. /nothing more to say; just pointing out the obvious. //not an OOP developer, but I eat lunch with them. ///I like slashes.... Stu Hi Stu,
> portability as an decision factor in using Views vs Stored Procs is Well, that's the difference between a decision factor and a decision. > betrayed by your own personal policy; if you're using procs to INSERT > or UPDATE data, then there is no guarantee that those same procs will > run on any other database server either. Decisions are based on a number of factors which are often mutually contradictory. There are two options: 1. Don't make any decision. (I've seen this approach to often in corporate settings ;-) 2. Make the decision which accommodates the most important factors. I've taken approach #2. I did state above that portability was not my #1 factor. Although it is *a* factor and my approach is valid. I can either end up with 100 stored procedures or 50 views and 50 stored procedures. Although the latter isn't the perfect migration scenario, it's a much better position than the former. > "black box" approach to a stored procedure; I agree that it's a bit of That's it in a nutshell. I did comment above in this thread about my > a crossover of business logic into the data tier, and that may be preference for UI/Business & Data tiers over UI & Business/Data tiers. - Joe Geretz - Show quote "Stu" <stuart.ainswo***@gmail.com> wrote in message news:1122598607.305500.213400@g44g2000cwa.googlegroups.com... > Well put, Joe. > > I don't think that there is as much of a performance benefit to stored > procedures as there used to be in earlier versions of SQL Server, so > performance is not really an issue. However, your argument for > portability as an decision factor in using Views vs Stored Procs is > betrayed by your own personal policy; if you're using procs to INSERT > or UPDATE data, then there is no guarantee that those same procs will > run on any other database server either. You may have to rewrite them > as well, so apart from saving yourself the keystrokes on your SELECT > procedures, you haven't really gained anything by NOT using stored > procedures for SELECT statements (in regards to portability). > > <snip> > Dou you know what is happening in GetCustomers? I don't. Is any data > updating taking place? Who knows? But if you say SELECT * FROM > CustomersVIEW > I guarantee that no updating is taking place. > </snip> > > I would have thought that a VB programmer (or is it developer, or > Techie, or architect? :) ) would have more appreciation for the whole > "black box" approach to a stored procedure; I agree that it's a bit of > a crossover of business logic into the data tier, and that may be > enough for the theorists to HATE stored procedures, but unless you're > willing to fully develop your business logic tier in between your > application and your data layer, stored procedures do give you some > flexibility and code re-use without passing straight SQL from your app. > > > If you write stored procedures that use vendor-specific SQL, you can > optimize the performance of your application on that database platform. > In other words, your app may SMOKE on SQL Server, and not run at all > on Oracle. Your competitor may have an app that runs OK on either > platform; which is better for your potential customers? The best > scenario would be for you to invest not in a portable option, but in > different versions of your application that are optimized for their > respecitive database platforms. Much more expensive to implement, but > the ideal solution. > > I guess if you're concerned about portability, I would choose neither > view nor stored procedure; instead, I would probably build a map within > your business logic tier that maps the appropriate application objects > and methods to the correct data entities (you could use views to simply > the schema, but if you're inserting and updating tables, why bother?). > You'll still be passing complex SQL statements from this middle tier to > your data source (without the potential gain of database-specific > optimization), but your data source would be hidden from the > application (and thus more portable), and your business logic would be > isolated from your datasource. > > /nothing more to say; just pointing out the obvious. > //not an OOP developer, but I eat lunch with them. > ///I like slashes.... > > Stu > Joseph Geretz wrote:
> 1. Don't make any decision. (I've seen this approach to often in corporate so what is your #1 factor? So far all I've gotten from this chain is> settings ;-) > 2. Make the decision which accommodates the most important factors. > > I've taken approach #2. I did state above that portability was not my #1 > factor. Although it is *a* factor and my approach is valid. I can either end > up with 100 stored procedures or 50 views and 50 stored procedures. Although > the latter isn't the perfect migration scenario, it's a much better position > than the former. > that it ain't portabilty, and it ain't performance. If it's your adherence to the design model of seperating business logic from the data tier AND the UI tier (which realistically translates into potability as a decision factor), then your policy also doesn't meet that requirement. You're still mucking around in the database with half your code, and why is that better than doing all of your code in the database (or none of it)? If your #1 factor is to have a standard in place that you and your developers can live with, that's OK. From what I've gathered here, you make the decision, and that should be enough reason to implement it (and to expect adherance). You don't need approval from me or anyone else; it's the bottom line that matters, and if "Your Personal Policy" helps you get a good product out the door that makes money for your company, who cares if you're logically consistent in your reasoning behind that policy? Granted, it's not my shop. And it's really easy for me (and the others) to arm-chair quarterback (but that's what you asked for when you posted to this newsgroup); I'm just pointing out the logical weaknesses in your argument, not the practical ones (and, to be fair, most arguments for using only stored procedures suffer from similar logical weaknesses). From a practical perspective, I think the idea of a standardized development policy is a great idea, and the requirement that you're suggesting is not onerous. I just think that the reasons you've given for deciding to implement said policy have either a) not been clearly stated, b) misinterpreted by me, or c) failed to lead to the logical conclusion that using views is prefereable to a stored procedure. I think from an n-tier design perspective, I'm leaning more toward removing business logic from the database altogether, and relying on Business Objects to map and interface with the data source; however, from a practical perspective, I'm uncomfortable with passing raw SQL statements over the wire between my business objects and my database. Not sure why, but I am. I'd be more likely to continue to use stored procedures to SELECT, INSERT, UPDATE and DELETE from my tables/views. May not be logical, but it works for me. :) Stu> so what is your #1 factor? There is no single overwhelming #1 factor as such. (does there always have to be?) There are a number of factors which I consider: Listed in alphabetic order: * Ease of Development * Ease of Maintenance * Performance * Portability > So far all I've gotten from this chain is Well, actually it's more of a compromise between the considerations listed. > that it ain't portabilty, and it ain't performance. I suppose, some might order the factors above into a list; #1, #2 and #3 and #4 and then choose a strategy optimized for Factor #1, and the heck with the other issues. In this case, I don't see any one issue as being so overwhelmingly positive and I don't see any one approach as being so overwhelmingly detrimental. Which is why I'm happy with a compromise approach. Unfortunately, my world doesn't consist exclusively of absolutes. > If your #1 factor is to have a standard in place that you and your Up until now, I've found that making good decisions contribute toward the > developers can live with, that's OK. From what I've gathered here, you > make the decision, and that should be enough reason to implement it > (and to expect adherance). You don't need approval from me or anyone > else; it's the bottom line that matters, and if "Your Personal Policy" > helps you get a good product out the door that makes money for your > company, who cares if you're logically consistent in your reasoning > behind that policy? development of a good product, far more effectively than making bad decisions do. (And I've unfortunately seen some pretty bad decisions which have had absolutely detrimental effects on products I've worked on in the past.) So it's information, rather than approval, which I am seeking. > Granted, it's not my shop. And it's really easy for me (and the No, not exactly, see my next comment.> others) to arm-chair quarterback (but that's what you asked for when > you posted to this newsgroup); > I'm just pointing out the logical Or maybe you're presuming a question which I haven't actually asked. I asked > weaknesses in your argument, not the practical ones (and, to be fair, > most arguments for using only stored procedures suffer from similar > logical weaknesses). From a practical perspective, I think the idea of > a standardized development policy is a great idea, and the requirement > that you're suggesting is not onerous. I just think that the reasons > you've given for deciding to implement said policy have either a) not > been clearly stated, b) misinterpreted by me, or c) failed to lead to > the logical conclusion that using views is prefereable to a stored > procedure. for some basic points regarding the pros and cons of VIEWS, Stored Procs. What I haven't asked for is for others to supply conclusions based on these basic points. Because the technical points are only half (maybe even less than half) the equation. The other parts of the equation, which we haven't delved into, are the nature of the application, the development group, the long-term and short-term business goals, and a whole host of other relevant factors. So without all this information in hand, how would you expect to come up with a suitable solution? Indeed, it would be downright unreasonable of me to expect you to come up with a suitable solution for my specific situation, which is why I haven't asked for one. I have specifically NOT asked for others to fomulate my policy for me. A, it would be simplistic to assume that this is the sort of thing that we could do effectively via one thread on a newsgroup, and B, that's what they pay me for! :-) So if you see 'logical weaknesses' chalk it up to the fact that you're not in possession of *all* the facts in my scenario, nor would I presume to burden you with all of them. Although, for the purpose of discussion, we've touched on some points and I thank you very much for your input. But we certainly haven't by any stretch of the imagination gone through every single factor which is under consideration. > I think from an n-tier design perspective, I'm leaning more toward There ya go!> removing business logic from the database altogether, and relying on > Business Objects to map and interface with the data source; however, > from a practical perspective, I'm uncomfortable with passing raw SQL > statements over the wire between my business objects and my database. > Not sure why, but I am. I'd be more likely to continue to use stored > procedures to SELECT, INSERT, UPDATE and DELETE from my tables/views. > May not be logical, but it works for me. > :) Thanks for your insights!- Joe Geretz - Joseph Geretz wrote:
> > so what is your #1 factor? Ease of Development - My money would be on Stored Procs accross the> > There is no single overwhelming #1 factor as such. (does there always have > to be?) There are a number of factors which I consider: > > Listed in alphabetic order: > > * Ease of Development > * Ease of Maintenance > * Performance > * Portability > [snip rest] board - that way the people interfacing only have one route into the database that they have to follow - rather than going "Now, what am I doing here? If I'm reading data, I have to access the database in this manner, but if I'm updating, I have to access the database in a different manner" Having said that, the decision is fairly automatic for me - I construct the tables for my database, then run "The DOG" against the database. The DOG is an in-house product that runs through the schema and constructs a class for each table, along with a number of stored procs - for read, write (which will either insert or update), delete and for searching/traversing foreign key relationships, etc. I generally only have to write a few stored procs myself - for instance, a complex search which isn't based on a Primary or Foreign key. Calling the stored procs is encapsulated in the classes, so my only real contact with the database, so far as my application is concerned, is to pass the DOG Layer a connection string during startup. It's working fairly well. Views? We have a few on most databases - mostly to assist with ad-hoc queries that we have to run from QA when someone is asking questions that aren't answered by our MI system. Just my two-penneth Damien (remove the crossposts to other groups)
On Fri, 29 Jul 2005 00:17:14 -0400, Joseph Geretz wrote: >> so what is your #1 factor? Hi Joe,> >There is no single overwhelming #1 factor as such. (does there always have >to be?) There are a number of factors which I consider: > >Listed in alphabetic order: > >* Ease of Development >* Ease of Maintenance >* Performance >* Portability If I understand the discussion so far correctly, this is about the choice between a) stored proc (that might use the tables directly or use a view - or both) for changes, but views for selecting (client submits ad-hoc queries against the views), or b) stored proc (that might use the tables directly or use a view - or both) for changes AND selecting; select procs support parameters for different seaarches). Here are my opinions on the factors you mention. >* Ease of Development Switching from one approach to another will always incur a learningcurve, but in the long run, stored proc's only wins. This gives you the ability to let each programmer do what he can do best. Those specialized in the DB side get to write the stored procs, those specialized in frontend stuff need not try to get their head around SQL. Also, you only need one kind of interface between front-end and DB; the other approach requires coders to be able to code a stored proc call AND to code an ad-hoc query. >* Ease of Maintenance Again: stored procs only wins.. The arguments above apply to maintenanceas well. An extra argument is that you need to search through less code if unexpected results are returned. Run the stored proc that should have been executed with the parameters that should have been supplied. If the results are as desired, go and debug the frontend code. If they are not, go and debug the stored proc. In the stored procs + views approach, the fault might be anywhere: the view itself might be wrong, or the where clause tacked onto it in the front-end code, or the interface might handle data badly - or you might even find that the particular combination of view definition + where clause triggers some obscure SQL Server bug. >* Performance Another point in favor of stored procs. As explained in my earliermessage, there is no notable difference **IF** (and only if) the queries issued by the front-end code are good. But don't forget that the DBA has much more control over stored procs than over ad-hoc queries. What will you do if the performance degrades? With storeed procs only, it is relatively easy to pinpoint the procedure that causes the trouble, then take steps to correct it (either rewrite the proc or change the indexes). Locating the reason of the slowdown will be harder if various queries against the various views are being sent to the server - and once you find it, it'll also take more effort to correct it. Plus, since the queries are written by people not specialized in SQL Server, there is a higher chance of getting inefficient queries. >* Portability And yet another point for the stored procs. Yes, you might have torewrite the stored procs. But since there is a well-defined interface between front-end and back-end, that's it - the front-end doesn't have to change a bit. (Well, okay - the methods used to connect to the database, call the proc and receive the results might change). There are also some other factors that you didn;t mention, but that I think need to be taken into account as well * Flexibility Here, the views are the great winner. So what, if some goofy suit suddenly decides that he wants a report of all employees with age + shoe size > SQRT(salary) / 10? Just pop the appropriate WHERE clause in a standard execute-query-and-print-results program, run it and you're done. With stored procs, you'll either have to get the DBA to add a new search parameter to the read_employees proc first, before the frontend coder can write and run the program - and then he'll find out that there's been a miscommunication about the data type of the new parameter, so he'll have to change it yet again. Or you have to use the general read_all_employees proc, cursor through the results, discard all unwanted rows to list the few matching ones - probably slowing down the complete system for all other users while the program is running. * Scalability The two approaches don't differ much here. But remember the potential pitfalls listed under performance - if a system performs bad, it'll definitely NOT scale. Oh, and using stored procs only might also give you more control over the order in which tables are accessed - very important in heavily used systems to minimize the chance of deadlocks. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) On Sat, 30 Jul 2005 00:21:39 +0200, Hugo Kornelis wrote:
>(remove the crossposts to other groups) Well, I did *intend* to remove the crossposts....*cough* Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hi Hugo,
I just can't see how Stored Procs can be considered the winner in terms of ease of development / maintenance. The software IDE (in our case VB) provides Intellisense, Real-time syntax checking, a full debugging environment including break, edit and continue, as well as a whole host of other facilities. The Stored Procedure development medium is more like NotePad - it doesn't provide any of these features. I'm not considering the raw SQL as part of this analysis, indeed we like to keep SQL abstracted out of our software layer as much as possible. Which is why we use VIEWS to provide a layer of indirection between the software and the base tables, and hide the complexities of the relationships between physical tables. But when it comes to 'added-value processing' i.e. business logic, we're getting to a complexity whic I feel is best implemented in software, rather than in the database, at least from an ease of development / maintenance perspective. - Joe Geretz - Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:q69le1teudp8e9l4lgl1nvu1762hb9mdqu@4ax.com... > (remove the crossposts to other groups) > > On Fri, 29 Jul 2005 00:17:14 -0400, Joseph Geretz wrote: > >>> so what is your #1 factor? >> >>There is no single overwhelming #1 factor as such. (does there always have >>to be?) There are a number of factors which I consider: >> >>Listed in alphabetic order: >> >>* Ease of Development >>* Ease of Maintenance >>* Performance >>* Portability > > Hi Joe, > > If I understand the discussion so far correctly, this is about the > choice between > > a) stored proc (that might use the tables directly or use a view - or > both) for changes, but views for selecting (client submits ad-hoc > queries against the views), or > b) stored proc (that might use the tables directly or use a view - or > both) for changes AND selecting; select procs support parameters for > different seaarches). > > Here are my opinions on the factors you mention. > >>* Ease of Development > > Switching from one approach to another will always incur a learning > curve, but in the long run, stored proc's only wins. This gives you the > ability to let each programmer do what he can do best. Those specialized > in the DB side get to write the stored procs, those specialized in > frontend stuff need not try to get their head around SQL. Also, you only > need one kind of interface between front-end and DB; the other approach > requires coders to be able to code a stored proc call AND to code an > ad-hoc query. > >>* Ease of Maintenance > > Again: stored procs only wins.. The arguments above apply to maintenance > as well. An extra argument is that you need to search through less code > if unexpected results are returned. Run the stored proc that should have > been executed with the parameters that should have been supplied. If the > results are as desired, go and debug the frontend code. If they are not, > go and debug the stored proc. > In the stored procs + views approach, the fault might be anywhere: the > view itself might be wrong, or the where clause tacked onto it in the > front-end code, or the interface might handle data badly - or you might > even find that the particular combination of view definition + where > clause triggers some obscure SQL Server bug. > >>* Performance > > Another point in favor of stored procs. As explained in my earlier > message, there is no notable difference **IF** (and only if) the queries > issued by the front-end code are good. But don't forget that the DBA has > much more control over stored procs than over ad-hoc queries. What will > you do if the performance degrades? With storeed procs only, it is > relatively easy to pinpoint the procedure that causes the trouble, then > take steps to correct it (either rewrite the proc or change the > indexes). Locating the reason of the slowdown will be harder if various > queries against the various views are being sent to the server - and > once you find it, it'll also take more effort to correct it. Plus, since > the queries are written by people not specialized in SQL Server, there > is a higher chance of getting inefficient queries. > >>* Portability > > And yet another point for the stored procs. Yes, you might have to > rewrite the stored procs. But since there is a well-defined interface > between front-end and back-end, that's it - the front-end doesn't have > to change a bit. (Well, okay - the methods used to connect to the > database, call the proc and receive the results might change). > > There are also some other factors that you didn;t mention, but that I > think need to be taken into account as well > > * Flexibility > > Here, the views are the great winner. So what, if some goofy suit > suddenly decides that he wants a report of all employees with age + shoe > size > SQRT(salary) / 10? Just pop the appropriate WHERE clause in a > standard execute-query-and-print-results program, run it and you're > done. With stored procs, you'll either have to get the DBA to add a new > search parameter to the read_employees proc first, before the frontend > coder can write and run the program - and then he'll find out that > there's been a miscommunication about the data type of the new > parameter, so he'll have to change it yet again. Or you have to use the > general read_all_employees proc, cursor through the results, discard all > unwanted rows to list the few matching ones - probably slowing down the > complete system for all other users while the program is running. > > * Scalability > > The two approaches don't differ much here. But remember the potential > pitfalls listed under performance - if a system performs bad, it'll > definitely NOT scale. > Oh, and using stored procs only might also give you more control over > the order in which tables are accessed - very important in heavily used > systems to minimize the chance of deadlocks. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) > But when it comes to 'added-value processing' i.e. business logic, we're This statement is only true if by business logic, you don't mean business> getting to a complexity whic I feel is best implemented in software, rather > than in the database, at least from an ease of development / maintenance > perspective. rules. Business rules are constraints. Constraints should be part of the database, not part of an application. If constraints are enforced in applications, then all of those applications must enforce the constraints with the exact same logic, and no update can be allowed to occur outside of those applications. Even stored procedures must include the same constraint logic. Every update path must enforce all of the constraints. To me, it doesn't make any sense to have a whole lot of duplicate code in a system to enforce constraints, because maintenance is much more difficult. If a stored procedure can update a table, and a middleware object can update a table, then if a business rule changes, you must change the middleware object and the stored procedure. Failure to change both can allow incorrect information to be stored in the database. Show quote "Joseph Geretz" <jgeretz@nospam.com> wrote in message news:O$Vw#uXlFHA.3380@TK2MSFTNGP12.phx.gbl... > Hi Hugo, > > I just can't see how Stored Procs can be considered the winner in terms of > ease of development / maintenance. The software IDE (in our case VB) > provides Intellisense, Real-time syntax checking, a full debugging > environment including break, edit and continue, as well as a whole host of > other facilities. The Stored Procedure development medium is more like > NotePad - it doesn't provide any of these features. I'm not considering the > raw SQL as part of this analysis, indeed we like to keep SQL abstracted out > of our software layer as much as possible. Which is why we use VIEWS to > provide a layer of indirection between the software and the base tables, and > hide the complexities of the relationships between physical tables. > > But when it comes to 'added-value processing' i.e. business logic, we're > getting to a complexity whic I feel is best implemented in software, rather > than in the database, at least from an ease of development / maintenance > perspective. > > - Joe Geretz - > > "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message > news:q69le1teudp8e9l4lgl1nvu1762hb9mdqu@4ax.com... > > (remove the crossposts to other groups) > > > > On Fri, 29 Jul 2005 00:17:14 -0400, Joseph Geretz wrote: > > > >>> so what is your #1 factor? > >> > >>There is no single overwhelming #1 factor as such. (does there always have > >>to be?) There are a number of factors which I consider: > >> > >>Listed in alphabetic order: > >> > >>* Ease of Development > >>* Ease of Maintenance > >>* Performance > >>* Portability > > > > Hi Joe, > > > > If I understand the discussion so far correctly, this is about the > > choice between > > > > a) stored proc (that might use the tables directly or use a view - or > > both) for changes, but views for selecting (client submits ad-hoc > > queries against the views), or > > b) stored proc (that might use the tables directly or use a view - or > > both) for changes AND selecting; select procs support parameters for > > different seaarches). > > > > Here are my opinions on the factors you mention. > > > >>* Ease of Development > > > > Switching from one approach to another will always incur a learning > > curve, but in the long run, stored proc's only wins. This gives you the > > ability to let each programmer do what he can do best. Those specialized > > in the DB side get to write the stored procs, those specialized in > > frontend stuff need not try to get their head around SQL. Also, you only > > need one kind of interface between front-end and DB; the other approach > > requires coders to be able to code a stored proc call AND to code an > > ad-hoc query. > > > >>* Ease of Maintenance > > > > Again: stored procs only wins.. The arguments above apply to maintenance > > as well. An extra argument is that you need to search through less code > > if unexpected results are returned. Run the stored proc that should have > > been executed with the parameters that should have been supplied. If the > > results are as desired, go and debug the frontend code. If they are not, > > go and debug the stored proc. > > In the stored procs + views approach, the fault might be anywhere: the > > view itself might be wrong, or the where clause tacked onto it in the > > front-end code, or the interface might handle data badly - or you might > > even find that the particular combination of view definition + where > > clause triggers some obscure SQL Server bug. > > > >>* Performance > > > > Another point in favor of stored procs. As explained in my earlier > > message, there is no notable difference **IF** (and only if) the queries > > issued by the front-end code are good. But don't forget that the DBA has > > much more control over stored procs than over ad-hoc queries. What will > > you do if the performance degrades? With storeed procs only, it is > > relatively easy to pinpoint the procedure that causes the trouble, then > > take steps to correct it (either rewrite the proc or change the > > indexes). Locating the reason of the slowdown will be harder if various > > queries against the various views are being sent to the server - and > > once you find it, it'll also take more effort to correct it. Plus, since > > the queries are written by people not specialized in SQL Server, there > > is a higher chance of getting inefficient queries. > > > >>* Portability > > > > And yet another point for the stored procs. Yes, you might have to > > rewrite the stored procs. But since there is a well-defined interface > > between front-end and back-end, that's it - the front-end doesn't have > > to change a bit. (Well, okay - the methods used to connect to the > > database, call the proc and receive the results might change). > > > > There are also some other factors that you didn;t mention, but that I > > think need to be taken into account as well > > > > * Flexibility > > > > Here, the views are the great winner. So what, if some goofy suit > > suddenly decides that he wants a report of all employees with age + shoe > > size > SQRT(salary) / 10? Just pop the appropriate WHERE clause in a > > standard execute-query-and-print-results program, run it and you're > > done. With stored procs, you'll either have to get the DBA to add a new > > search parameter to the read_employees proc first, before the frontend > > coder can write and run the program - and then he'll find out that > > there's been a miscommunication about the data type of the new > > parameter, so he'll have to change it yet again. Or you have to use the > > general read_all_employees proc, cursor through the results, discard all > > unwanted rows to list the few matching ones - probably slowing down the > > complete system for all other users while the program is running. > > > > * Scalability > > > > The two approaches don't differ much here. But remember the potential > > pitfalls listed under performance - if a system performs bad, it'll > > definitely NOT scale. > > Oh, and using stored procs only might also give you more control over > > the order in which tables are accessed - very important in heavily used > > systems to minimize the chance of deadlocks. > > > > Best, Hugo > > -- > > > > (Remove _NO_ and _SPAM_ to get my e-mail address) > > On Sat, 30 Jul 2005 22:58:31 -0400, Joseph Geretz wrote:
>Hi Hugo, Hi Joe,> >I just can't see how Stored Procs can be considered the winner in terms of >ease of development / maintenance. (snip) Well, I don't think I can explain it better than I already did. (Except that I now see that I forgot to include the argument that it's harder to make crossreferences and do impact analysis if the code is spread out over more platforms). I'm not on a mission to convince you (or others) of my views. You asked for other people's thought on this issue, I gave you mine. I also gave you the reasons for my opinions. Now, it's up to you to either change your mind, or not. As you already said in another message: making the final decision is what YOU are paid for! ;-) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Joe,
My apologies... it's actually spelled "Luddite" (two 'd's not one). Cheers! Show quote "Joseph Geretz" <jgeretz@nospam.com> wrote in message news:eEVRbB9kFHA.2608@TK2MSFTNGP14.phx.gbl... > Hi Frankie, > >> coming up with mandate described in your My Preferred Policy). If you >> don't trust your developers and feel you need to control them in order to >> compensate for your lack of expertise, then perhaps it's time for you to >> read the book, "Becoming a Technical Leader" by Geral M. Weinberg. > > I guess that someone who makes gross assumptions about another individual > without having anywhere near a sound basis for making these assumptions is > probably the last person to advise me on becoming any sort of leader, > technical or otherwise. > > With almost two decades of development work behind me, I'm in an excellent > position to *balance* the logistics of team development with the technical > realities of the environment in which we operate. And for the record, I am > proud to call myself a Techie. From my perspective, I'd be more likely to > use the term Paper-Pusher as a pejorative. > > Thanks for the free advice. I'm sure it's worth every penny! > > - Joe Geretz - > > "Frankie" <A@B.COM> wrote in message > news:eDEE0v8kFHA.320@TK2MSFTNGP09.phx.gbl... >>>> My Preferred Policy: >>>> ... >>>> As opposed to: >>>> Techie Performance Policy: >> >> Joseph, Joseph, Joseph.... >> >> Your use of the word "Techie" has a few possible meanings - none of which >> I think are positive. "My policy" vs "Techy policy" suggests that you are >> not very technically literate (somewhat, but not very strong here), but >> you are also trying to impose your views on people who are very >> technically literate (at least moreso than yourself). That's much like a >> hospital administrator (MBA type with no med school background) telling >> the doctors how to operate on the patients. You will soon be resented if >> you are not already resented by your "techie" subordinates as YOUR own >> fears and lack of expertise in THEIR knowledge domain comes to limit what >> they do in some rather rather uninformed ways (ininformed as defined by >> your intentions). There are other ways to implement standards. Trying to >> go to more restrictive technologies, which is apparently why you like >> VIEWS so much (misguided as your perception is), is a very Ludite thing >> to be doing (look it up - it will describe your tendencies). Rather than >> trying to control your developers and DBAs, why not ASK THEM (who know >> way more than you do in their respective knowledge domains) for THEIR >> IDEAS for streamlining things and for imposing standards. Perhaps they >> will suggest peer code reviews, a department-wide standard for stored >> procedures AND views where each is appropriate, etc. These people know >> how to do their job. They need someone in your position to FACILITATE >> communication and COORDINATE development of standards amongst them; NOT >> limit what and how they do things in significant and rediculous ways >> (like coming up with mandate described in your My Preferred Policy). If >> you don't trust your developers and feel you need to control them in >> order to compensate for your lack of expertise, then perhaps it's time >> for you to read the book, "Becoming a Technical Leader" by Geral M. >> Weinberg. >> >> -F >> >> >> >> "Joseph Geretz" <jgeretz@nospam.com> wrote in message >> news:%23vBreY6kFHA.3312@tk2msftngp13.phx.gbl... >>> Thanks Aaron, for your enlightening reply. >>> >>>> I think that's a very narrow and limited view, no pun intended. You >>>> can certainly use both -- stored procedures for >>>> performance/encapsulation, and views for schema/query/relationship >>>> simplification and/or security. >>> >>> Actually, I'm trying to be as broadminded as possible. However, our team >>> of half-a-dozen developers is in a position where we need to impose >>> standards. We can't simply allow developers to individually adopt >>> whatever approach they happen to prefer. So, in the sense that standards >>> are a limiting factor, you are correct; that's what I'm trying to do. >>> However, I don't intend to be fanatical by any means. >>> >>> Aside from the performance issue, VIEWS provide a few significant >>> advantges: >>> >>> 1. They are flexible and can easily accommodate any WHERE clause. >>> 2. They are absolutely transparent in terms of being READ-ONLY >>> operations >>> 3. They are comprised of ANSII SQL which is portable between database >>> vendors. >>> >>> Neither of these are true of Stored Procedures. For a Stored Proc to be >>> flexible, it must implement logic to determine which parameters are >>> being supplied. And a Stored Proc which returns a recordset might be >>> subsequently modified to update data, in a way which might not be >>> obvious to maintenance programmers. And Stored Proc dialect is very >>> often vendor specific, rendering the Stored Proc non-portable. >>> >>> I'm thinking of imposing the following standard: >>> >>> My Preferred Policy: >>> --------------------- >>> 1. READ-ONLY operations are performed via VIEWS >>> 2. UPDATE operations performed via Stored Procs >>> >>> As opposed to: >>> >>> Techie Performance Policy: >>> ---------------------------- >>> 1. All operations performed via Stored Procs >>> >>> Although, if someone could demonstrate to me that Stored Procedure >>> performance is significantly greater than that which is provided by a >>> similar VIEW, I might feel that compelling enough to shift from My >>> Preferred Policy to implement all operations via Stored Procedures (the >>> Techie Performance Policy). So far, I'm not at all convinced that this >>> is the case, or that even if it were, that the performance benefit would >>> outweigh other considerations. >>> >>> If I understand you correctly, you feel that my preferred policy is >>> reasonable? >>> >>> Thanks, >>> >>> - Joe Geretz - >>> >>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in >>> message news:OgN7W65kFHA.1464@TK2MSFTNGP14.phx.gbl... >>>>> Every article which I've seen proposing Stored Procedures makes the >>>>> same comparison: Stored Procs are faster than submitted SQL because >>>>> Stored Procs are precompiled. Fine, but there's a third alternative, >>>>> which I haven't seen mentioned at all. How do Stored Procedures >>>>> compare with VIEWS? >>>> >>>> Views are not used for improving performance (unless you are talking >>>> about indexed views or distributed partitioned views). Even there, you >>>> can use stored procedures to access the view, instead of just saying >>>> "SELECT * FROM ViewName" in your application code. A common >>>> misconception is that a view somehow caches or stores the data that it >>>> returns, and this is not the case (again, in "normal" views). >>>> >>>> Typically, views are used to reduce the complexity of a table's schema >>>> or the relationship of multiple tables, for example a view called >>>> CustomerData might include the customerID from Customers, the primary >>>> address from the CustomerAddresses table, and the most recent order >>>> from the Orders table. Now, a user writing a SELECT statement (or a >>>> stored procedure that uses a SELECT statement) doesn't have to >>>> understand all of those relationships. >>>> >>>> Another typical purpose for a view is to restrict access to certain >>>> data. For example, in the Employees database, there is probably at >>>> least one table with 401k/salary information that you don't want >>>> exposed to all users. So you might have very restricted permissions on >>>> the table itself, and then create a view that leaves out those columns, >>>> and give more ready access to that view so that the average user can >>>> still get a list of all the employees, but won't be able to see which >>>> ones make more money... >>>> >>>>> read-only operations which can be performed either via a VIEW or a >>>>> Stored Proc. >>>> >>>> I think that's a very narrow and limited view, no pun intended. You >>>> can certainly use both -- stored procedures for >>>> performance/encapsulation, and views for schema/query/relationship >>>> simplification and/or security. >>>> >>> >>> >> >> > > >> My Preferred Policy: 1. READ-ONLY operations are performed via VIEWS--------------------- 2. UPDATE operations performed via Stored Procs << I think you got it. My only warning would be to make sure that the VIEWs make sense as tables and have a good name. When you write the procedures, follow basic software engineering practices. T-SQL is proprietary, but you can write it so that it is portable (i.e translated to the next dialect without a lot of pain). Hi Celko,
Thanks for your reply. > proprietary, but you can write it so that it is portable (i.e Do you know of any articles which discuss this aspect of writing T-SQL for > translated to the next dialect without a lot of pain). maximum portability? Thanks, - Joe Geretz - Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1122595647.940416.234830@o13g2000cwo.googlegroups.com... >>> My Preferred Policy: > --------------------- > 1. READ-ONLY operations are performed via VIEWS > 2. UPDATE operations performed via Stored Procs << > > I think you got it. My only warning would be to make sure that the > VIEWs make sense as tables and have a good name. When you write the > procedures, follow basic software engineering practices. T-SQL is > proprietary, but you can write it so that it is portable (i.e > translated to the next dialect without a lot of pain). > Turn on the FIPS Flagger to get warnings about proprietary code.
If you learn Standard SQL from the start, you do not think about the propprietary stuff; likewise, if you learn T-SQL dialect from the start, you do not think about the Standard SQL syntax. There are some tools like Swissql that will translate SQL dialects for you. > Turn on the FIPS Flagger to get warnings about proprietary code. Don't use the FIPS flagger. It is more or less useless in SQL Server 2000. I haven't got any official reply, but I have this feeling that it wasn't updated between 7.0 and 2000. There are a bunch of things that it misses. Use the mimer validator instead. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1122649280.915359.91020@o13g2000cwo.googlegroups.com... > Turn on the FIPS Flagger to get warnings about proprietary code. > > If you learn Standard SQL from the start, you do not think about the > propprietary stuff; likewise, if you learn T-SQL dialect from the > start, you do not think about the Standard SQL syntax. There are some > tools like Swissql that will translate SQL dialects for you. >  |
|||||||||||||||||||||||
Other interesting topics