InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 22:54:36 GMT20http://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxThis is going to be a long reply, mostly because of much quoting, bear with me .[quote][b]Evil Kraig F (5/8/2013)[/b][quote][b]peter-757102 (5/3/2013)[/b][hr]I would argue that most applications, notably the smaller to medium custom written, are better implemented using tight binding between database and application. For ease of development as well as performance and flexibility reasons and then some possibly beyond the scope of your own particular needs and experience![/quote]Development is eased with direct binding but performance is rarely assisted. Also, you expose yourself to a number of dangers that dynamic SQL poses. I have worked in very small shops where they made practically six calls to 4 tables in a single database... and that's all they ever really needed. The tables could have been denormalized, but no reason, really. In this case... the proc is STILL the better choice. You've encapsulated the call for troubleshooting, removed injection concerns, and have better security separation between front and back end.Additionally, tight binding and flexibility rarely go hand in hand. If you mean the flexibility for the devs to work in only their programming language to send code to SQL, then I'd agree. If you mean future programming flexibility, I'd disagree. Any change to schema would require you to re-write multiple code locations instead of a single proc acting as a function call for the front end.[/quote]Performance is assisted because you can create better tuned (dynamic) queries without procedural overhead in the database. Procedural logic to make SQL code adaptive is more efficiently handled in a language suited to procedural programming.Another advantage of tight binding is that the SQL code that is written can use the same declared values for status (and other) codes as is used by the rest of the application. There still needs to be a match between these declarations and the actual status table contents of course. But at least you do not end up with unmaintainable constants in the SQL code as you would be forced to when writing stored procs! Firing unneeded queries happens when people write code not knowing what that code really does behind the abstractions they use. This is the danger of abstraction and misuse is promoted by constructs like LINQ and ORM's (and yes, this is true for every hidden abstraction, thus stored procedures as well). Developers are using these abstractions most often because they never learned proper SQL. Which is a shame as any half decent level developer should know quite a bit of advanced SQL (I intend to write a piece on that someday, also for internal use). That there exists of a lot of bad database related code in applications "out there" just has another root cause then tight binding.And just to be clear, I want developers to write SQL and become good at it! It promotes awareness of the data and structure they operate on and leads to better understanding, choices and in the end...better developers too. Just for this alone, schema awareness is essential and shielding the developer from it, is a very bad path to follow!Your argument that changing the schema is easier with a stored proc, is comparing apples to oranges. That same stored proc can be implemented effectively in a single method in the application for application specific databases. And when a schema changes significantly, the application needs to change just the same! Not to mention the advantages of having that code in the same version control as the application![quote][b]Evil Kraig F (5/8/2013)[/b][quote]If some other application needs access to a specific part of the database you can fall back on stored procedures and provide customized access rights just for them to functionally provide what is needed securely and without being schema bound there.[/quote]And yet if the procs are already written you don't even need to do that.[/quote]You are thinking a one size fits all thought. What i write is that only in the rare exception (for most apps) you need a strored proc for security reasons, and only then you have to write one. Then a response of "it would be already there" doesn't make much sense! How would always writing procedures be better?[quote][b]Evil Kraig F (5/8/2013)[/b][quote]In a public discussion of this matter it helps to acknowledge not every service is like facebook or a non-client specific application that is deployed over and over and just customized by third parties. Most applications I bet are custom written against a non-public database schema designs where the database is provided as part of the service an application provides and serviced by the application provider and no-one else.[/quote]You're correct, and that's the majority of my work. Now, are the front ends public or private depends on what we're doing. Most companies these days have some form of portal for their clientelle, and thus you must allow for eventual exposure of some kind of data to them. Now, is the majority of the work being done in the back? Sure.Out of curiousity, have you ever worked with DB2? DB2 required the kind of tight binding you're speaking of. There's a reason it's basically out of the market except in legacy systems.[/quote]I have not worked with DB2. At the same time I do not get the feel you understand where I am coming from. An application consist of several layers, one is the data access layer (DAL for short) and even that can be subdivided in more layers.What I am talking about that that at least the lowest layer of the DAL, there is 100% awareness of the database schema. Everything acting directly on one entity is found there, including constants related to the schema. Things spanning functional groups of entities can be found in higher DAL layers.The decoupling between schema and interface in this approach does not happen at the application/database boundary, but instead above the DAL (which resides in the application). It is the interface where you generally find things out of direct alignment with the schema. Additional models supporting those interfaces represent the solution to this disconnect and keep both model groups clean. The DAL (database or otherwise) should not be inflicted with changes every time some part of the user interface is re-arranged.[quote][b]Evil Kraig F (5/8/2013)[/b][quote]It just means business logic constraints that do not fit this declarative pattern can also be implemented in the application layer without any real drawback. But with the benefits this other approach brings, such as much better and efficient dynamic SQL and richer state management.[/quote]You'll have to define 'richer state management' to me. However, I agree with there being rules that are more efficient in the app layer than in the database layer. The reverse is also true. I'm more than happy to discuss with devs on the teams I assist which I feel would be best where and why. Particularly formatting/string manipulation, display, and row to row computations are certainly best done in an iterative environment instead of a set based one. Aggregations, data organization and associations, filtering, and usually mathmatics are normally best done at the DB level.[/quote]We are on the same page here I think. What I mean with “richer” is handling anything beyond the capabilities of the relational model and constraints. Think of keeping columns from different entities that are somehow related, in sync. You can write triggers for this too, but as usual it depends on the specifics if that is a good solution or not.[quote][b]Evil Kraig F (5/8/2013)[/b][hr]However, there is a significant drawback if you just want to look at time to process a call to the DB. A proper string scraper in the front end to remove all possibilities of injection to your particular system can take anywhere from 15 to 20 milliseconds. No, that's not long. Every parameter for every call however has to be ran through that process. It's just one place where you're easing the time for development and placing it on the time to process. [/quote]15-20 milliseconds sounds like an awful lot to me, what I have in mind will be nowhere near even one millisecond! And the cost of parameter passing you will have is identical for stored procedure calls! And dynamic SQL, processing stored procedures isn’t free either and generally procedural logic is not efficient in SQL. [quote][b]Evil Kraig F (5/8/2013)[/b][hr]Another thing is every dynamic call to the DB has to be uniquely compiled. It can't re-use cache plans. Now, cached plans have some of their own quirks but under 99.9% of the situations they're just fine. [/quote]You seem to assume that dynamic SQL send from the application DAL is somehow not parameterized. To be clear, all parameters not explicitly configured otherwise, are parameterized in what I am describing. It is just that all the sores of coding this can be removed from the programmer without alienating him/her from the actual schema and the SQL.[quote][b]Evil Kraig F (5/8/2013)[/b][hr]Thirdly if the app is 'overhandling' data, you're dealing with a lot of unnecessary network traffic and waits.[/quote]Anything you put in a stored procedure has a counterpart in the application DAL. Thus only in situations where one stored procedure would call another stored procedure would there be extra traffic. If that is significant, then it is time to lift the combined logic to a higher level in the DAL and write specialized code for handling this situation in one step. Same as you would do with stored procedures really.[quote][b]Evil Kraig F (5/8/2013)[/b][hr]Finally, and my most vehement argument, is eventually one of those little apps grows up. Your 'prototype' becomes the best thing since sliced bread and someone in accounting just dumped every spreadsheet for the last 30 years into it over a weekend. Now, since you've cut so many corners, you're looking at re-designing the entire thing into optimal calls. Wouldn't it have made sense just to have done it 'right' in the first place, and then tweak for volume? [/quote]You are misrepresenting the situation, there were no corners cut! What you will find in your stored procedure solution you now find in the application DAL, neatly isolated. Even more properly isolated then with procedures actually, since the DAL consists of several layers. The application does still the same, thus the models for the interface remain intact too.[quote][b]Evil Kraig F (5/8/2013)[/b][hr][quote]As for the requirement of dynamic SQL, nearly all application back-offices need this in spades for search and/or real-time reporting purposes. [/quote]Catch All Searches, I agree. And they can be done under the hood without significant problems. Real Time reporting you'll have to be a bit more explicit to me about. If you mean real time from the data, I hook SSRS reports to procs all day long off our OLTP system and they're used quite effeciently. Prior to SSRS ASP.NET served similar purposes for us, and so did MS Access with passthrough queries. Now, if you're talking dynamically built user reports, we're in a whole different conversation. [/quote]We don’t use catch all queries for general list views, filter conditions and sorting are added dynamically based on user input. Often we have reporting functionality build on aggregate tables and even user definable reports. Both type of systems work exclusively by generating SQL code dynamically on the fly.[quote][b]Evil Kraig F (5/8/2013)[/b][hr]A) That's not common. Most firms expect Tech to build them for them. Part of why SSRS 2k8 went more dev driven than user driven, like SSRS 2k5 was.B) If you've got power users at that level invest some time in SSAS and PowerPivot. It's a lot easier.[/quote]SSRS is awful from what I seen of it, I wouldn’t dare to sell such a solution if my life depended on it (ok, maybe I would in that extreme, but you get the idea)! It’s so disconnected from what a user really needs and totally not part of the application regular users use. It also requires its own database, backup procedures, users, has a bad interface, you name it. Nothing I would like to use as a developer either, it is just really bad all-around. In short application specific reporting functionality never needs this, let alone the dependencies that come with it.[quote][b]Evil Kraig F (5/8/2013)[/b][hr][quote]If I compare the issues we have in our applications developed this way and the number we find others have, it is staggering! We never have the issues related parties have (being unable to get anything data related to be consistent and/or correct). And it is not for lack of having to do complex stuff, it is just a matter of knowing what you do and have the basics in place, plus the proper knowledge level of SQL (this last part is an area that is pretty thin out there I am afraid).[/quote]If you have the proper level of SQL, putting it in a proc is no more difficult than writing the query and wrapping it in CREATE PROC @param1 INT, @param2 VARCHAR(50) AS ... GO[/quote]That is just overhead and does not allow for maintainable SQL code. It does however allow you to apply procedure lever security, which as I made clear most single purpose applications databases really do not require. The body of the stored procedure as you describe here can be executed as a batch just the same without wrapping a redundant interface around it. In the end it is not about what is more difficult, it is about that develops easier and is cleaner. Having access to constants in an application DAL is one benefit, and source control is another. From a developer perspective having a stored procedure to call would still mean calling a proxy method for that stored procedure, which in turn is really a proxy for the SQL batch inside the procedure. That chain is overhead and complexity. Then consider aspects like error reporting when a query fails. The application being aware of the query allows much more detailed error reporting, allowing things to be fixed faster!I have to say the C# environment I recently started developing in is quite underdeveloped the area of database access, as are the ORMs I seen for it so far (I think an ORM is for the most part a bad idea anyway). Microsoft and maybe the industry as a whole choose the path of least resistance and addressed symptoms instead of curing causes of bad code (that being lack of basic knowledge).I think they have been on this path since the late 90s when garbage collection based languages gained traction. Since that event, I feel most code has become less clean, less efficient, is worse maintainable and has more bugs (be it of another kind, I never had memory leaks back in those days either tho). Everyone and their donkey can now write a few lines of code and get away with it for some time until what is made, simply implodes.[quote][b]Evil Kraig F (5/8/2013)[/b][hr][quote]Part of the reason is that most developers do not think in data, but code and at the same time DBA’s generally have the reverse mindset.[/quote]I couldn't agree with this specific statement more. Thus why I get 'enforced' rules of using things like CI for database scripts in storage. Ever try to CI an index column order modification, and not have it rebuild the index EVERY time the CI is ran?[/quote]Can’t say I have as I do not know of CI. I did look it up and maybe you mean CodeIgniter for PHP? I simply haven’t worked with that, and I wouldn’t as I don’t like PHP at all for various reasons. I haven’t checked the latest versions to see if they bettered their life.[quote][b]Evil Kraig F (5/8/2013)[/b][hr][quote] Both parties like to have total control and freedom in their own domain and comfort zone! That does not mix and it would be elevating if more people would step a bit further outside their own domain and comfort zone and gain relevant experience and insights that way.[/quote]Does it help you if you knew that I started as a programmer? That it helps me understand what my devs need? That I still give them bloody havoc when they start sending dynamic trash down the pipe that ignores the indexes in place and they're scanning a 400 gig audit table? That last is mostly because they didn't bother to wrap it in sql_execute with parameters properly and didn't have me do a code review than because they wrote crap SQL. Sometimes you need to scan. Sometimes there's ways to do it without shared locking the entire table and thus locking the triggers that are trying to write to it from the main table.[/quote]I know what you mean, where I work there is no separate DBA role but a relatively high level of SQL knowledge and I expect new members to skill up too. Some problems simply require awareness of the schema and the insight to update the schema if that is beneficial. Other problems like SQL injection, query parameterization and when explicitly not wanted, no parameterization (for say a status value to take advantage of a filtered index), require a proper method of writing for and executing of SQL statements. This is where I found LINQ to SQL, LINQ in general and ORMs to be a major obstacle in nearly every aspect.[quote][b]Evil Kraig F (5/8/2013)[/b][hr][quote]Sorry for the long text, but after reading the latest discussion I don’t want to have people feel there is only one right way in what seems of this class between dba titans.[/quote]I agree, there is not only a single right way... if your machines can handle an unoptimized load on your database(s). Those also aren't the times when you need a SQL Dev professional like the majority of us who hang around here. [/quote]Not sure I agree with that, scaling issues can arise even with moderate growth in data. That is why even as a developer you need to know SQL and understand relational databases and not use them for dumb storage. Often you hear the mantra “premature optimization is evil”, where in practice it is mostly applied by people that create horrible inefficient solutions, that with the same amount of effort but more knowledge would run much better! Therefore my mantra is “performance by design” , much more positive![quote][b]Evil Kraig F (5/8/2013)[/b][hr]There are cases where I allow my front ends to send me down dynamic SQL. Particular cases where there is an exact scenario that makes for difficult or non-optimal T-SQL coding and it won't break security or expose the company awkwardly if the front end login information is compromised. However, data storage and retrieval logic will never fit directly into object/instance logic, you'll always require widgets of some form. Procs and their ilk are generally the most secure method and allow for both logic reusage and portability as well as separation of components during troubleshooting issues at 3 in the morning when some client 8 hours east of you on the planet is screaming holy rebellions because they can't check their cat's color for today.If you want to ease development by avoiding them, that's your shop's call. Just hope the powers that be will give you time to rebuild from scratch once it gets out of scope.[/quote]I work on several projects that have activity in all time zones and that are “mission critical” for their particular customers. Granted the load we have is not that of say Facebook or twitter, but we do not face the issues you describe because we do not have that model mismatch to begin with! If something significant changes we do refactor the relevant code. We know exactly what needs to be changed and where.Fri, 10 May 2013 04:48:14 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxEDIT: This is a textwall, I know, sorry for that but I got on a roll. Peter, a response to you starts about 1/3 of the way down. /EDIT[quote][b]L' Eomot Inversé (5/3/2013)[/b][hr]I guess it depends what you call horrible. In my experience most developers write SQL that I would be ashamed to put my name to, but then so do most DBAs; The good developers and the good DBAs both write excellent SQL, nothing like that.[/quote]I fear there's a bit of semantisicm in there but I get your drift. What I wrote five years ago I would consider horrible today. However, there may be a locality difference here. DBAs are just another title for SQL Devs here in the states, and they're 'run of the mill' for me. App devs tend to write multiple nested loops and the like because of their familiarity with that form of processing and data manipulation.[quote]Data protection law is pretty fierce in Europe (including UK), and many organisations are paranoid about it. I'm inclined to believe that paranoid caution is the right approach. ... Maybe the only solution is to altogether prevent people getting large chunks of data. I've not had to deal with extremely sensitive stuff as opposed not really sensitive personally identifiable data (I've tried to instill paranoia in the non-technical management even over that).[/quote]Some of this was definately from the misunderstanding of how you intend to use views. I'll get more into that below, but most of my contracts lately have been incredibly sensitive data sets... it's part of why I avoid the discussions completely on 'how easy it is to find people' from a few bits of data. I don't need the temptation. ;-)[quote]Dynamic SQL??? What on earth would that be for? I think you've misunderstood badly. I certainly don't want one-size-fits-all, every system I've been involved with since about 2000 has ended up (in some cases after I've overcome a lot of resistance from the backwoodsmen who want to embed DML statements directly in C++) as a system where the only thing the database presents to the outside world is a bunch of stored procedures.[/quote]Definately misunderstood, but I think we're coming to the point where our approaches were radically different to deal with the same problem.[quote]I had misunderstood your earlier posts to say that you though views were a bad idea and it would be better to give the app access to the base schema; so my posts were arguing that using views was better than that, which seems to have given you the impression that I thought views were the right way to go. Talk about cross-misunderstandings![/quote]*facepalm* Oh, lords yes. That was definately NOT what I meant! :-P[quote]Tightly bound app-db integration is pure poison. If you have that you can't make upgrades of DB and app independent, and for me that was absolutely essential (a company goes bust if you can't do it [without] issue)....That I don't understand - you seem to be contradicting yourself: you will do all in your power to avoid the app and the db being wound tightly together, but you don't mind not having narrow modular boundaries? You can completely decouple the app and database tiers without narrow modular boundaries? [/quote]Okay, I apparently had no idea of what the definition of a 'narrow modular boundary' was... and possibly still don't. Now, how did I deal with the multiple app front ends being 'updated' in pieces for a 24/7 five nines system? We used schemas bound to logins and made sure the front end code simply called for procs, not dbo.procs. Any proc that could work between two versions stayed as 'dbo'. Any proc that was a version output/input change (parameters or output columns/datatypes) was dbo for the old version, and schema'd to the new version. The schema would come in, get its own procs in its schema first, and then default to dbo if there wasn't one for its default schema name. Same way tables work. The next upgrade shuffled that schema down to the DBO level.More than a single version being supported except during switchovers is where I was reacting to, not the logistics behind a five nines system upgrades, which is a different scenario. I've never had to try to support four+ versions off the same schema, and it's not something I've ever really deeply thought about. @Peter:[quote][b]peter-757102 (5/3/2013)[/b][hr]I would argue that most applications, notably the smaller to medium custom written, are better implemented using tight binding between database and application. For ease of development as well as performance and flexibility reasons and then some possibly beyond the scope of your own particular needs and experience![/quote]Development is eased with direct binding but performance is rarely assisted. Also, you expose yourself to a number of dangers that dynamic SQL poses. I have worked in very small shops where they made practically six calls to 4 tables in a single database... and that's all they ever really needed. The tables could have been denormalized, but no reason, really. In this case... the proc is STILL the better choice. You've encapsulated the call for troubleshooting, removed injection concerns, and have better security separation between front and back end.Additionally, tight binding and flexibility rarely go hand in hand. If you mean the flexibility for the devs to work in only their programming language to send code to SQL, then I'd agree. If you mean future programming flexibility, I'd disagree. Any change to schema would require you to re-write multiple code locations instead of a single proc acting as a function call for the front end.[quote]If some other application needs access to a specific part of the database you can fall back on stored procedures and provide customized access rights just for them to functionally provide what is needed securely and without being schema bound there.[/quote]And yet if the procs are already written you don't even need to do that.[quote]In a public discussion of this matter it helps to acknowledge not every service is like facebook or a non-client specific application that is deployed over and over and just customized by third parties. Most applications I bet are custom written against a non-public database schema designs where the database is provided as part of the service an application provides and serviced by the application provider and no-one else.[/quote]You're correct, and that's the majority of my work. Now, are the front ends public or private depends on what we're doing. Most companies these days have some form of portal for their clientelle, and thus you must allow for eventual exposure of some kind of data to them. Now, is the majority of the work being done in the back? Sure.Out of curiousity, have you ever worked with DB2? DB2 required the kind of tight binding you're speaking of. There's a reason it's basically out of the market except in legacy systems.[quote]It just means business logic constraints that do not fit this declarative pattern can also be implemented in the application layer without any real drawback. But with the benefits this other approach brings, such as much better and efficient dynamic SQL and richer state management.[/quote]You'll have to define 'richer state management' to me. However, I agree with there being rules that are more efficient in the app layer than in the database layer. The reverse is also true. I'm more than happy to discuss with devs on the teams I assist which I feel would be best where and why. Particularly formatting/string manipulation, display, and row to row computations are certainly best done in an iterative environment instead of a set based one. Aggregations, data organization and associations, filtering, and usually mathmatics are normally best done at the DB level.However, there is a significant drawback if you just want to look at time to process a call to the DB. A proper string scraper in the front end to remove all possibilities of injection to your particular system can take anywhere from 15 to 20 milliseconds. No, that's not long. Every parameter for every call however has to be ran through that process. It's just one place where you're easing the time for development and placing it on the time to process.Another thing is every dynamic call to the DB has to be uniquely compiled. It can't re-use cache plans. Now, cached plans have some of their own quirks but under 99.9% of the situations they're just fine.Thirdly if the app is 'overhandling' data, you're dealing with a lot of unnecessary network traffic and waits.Finally, and my most vehement argument, is eventually one of those little apps grows up. Your 'prototype' becomes the best thing since sliced bread and someone in accounting just dumped every spreadsheet for the last 30 years into it over a weekend. Now, since you've cut so many corners, you're looking at re-designing the entire thing into optimal calls. Wouldn't it have made sense just to have done it 'right' in the first place, and then tweak for volume?[quote]As for the requirement of dynamic SQL, nearly all application back-offices need this in spades for search and/or real-time reporting purposes. [/quote]Catch All Searches, I agree. And they can be done under the hood without significant problems. Real Time reporting you'll have to be a bit more explicit to me about. If you mean real time from the data, I hook SSRS reports to procs all day long off our OLTP system and they're used quite effeciently. Prior to SSRS ASP.NET served similar purposes for us, and so did MS Access with passthrough queries. Now, if you're talking dynamically built user reports, we're in a whole different conversation. A) That's not common. Most firms expect Tech to build them for them. Part of why SSRS 2k8 went more dev driven than user driven, like SSRS 2k5 was.B) If you've got power users at that level invest some time in SSAS and PowerPivot. It's a lot easier.[quote]If I compare the issues we have in our applications developed this way and the number we find others have, it is staggering! We never have the issues related parties have (being unable to get anything data related to be consistent and/or correct). And it is not for lack of having to do complex stuff, it is just a matter of knowing what you do and have the basics in place, plus the proper knowledge level of SQL (this last part is an area that is pretty thin out there I am afraid).[/quote]If you have the proper level of SQL, putting it in a proc is no more difficult than writing the query and wrapping it in CREATE PROC @param1 INT, @param2 VARCHAR(50) AS ... GO[quote]Part of the reason is that most developers do not think in data, but code and at the same time DBA’s generally have the reverse mindset.[/quote]I couldn't agree with this specific statement more. Thus why I get 'enforced' rules of using things like CI for database scripts in storage. Ever try to CI an index column order modification, and not have it rebuild the index EVERY time the CI is ran?[quote] Both parties like to have total control and freedom in their own domain and comfort zone! That does not mix and it would be elevating if more people would step a bit further outside their own domain and comfort zone and gain relevant experience and insights that way.[/quote]Does it help you if you knew that I started as a programmer? That it helps me understand what my devs need? That I still give them bloody havoc when they start sending dynamic trash down the pipe that ignores the indexes in place and they're scanning a 400 gig audit table? That last is mostly because they didn't bother to wrap it in sql_execute with parameters properly and didn't have me do a code review than because they wrote crap SQL. Sometimes you need to scan. Sometimes there's ways to do it without shared locking the entire table and thus locking the triggers that are trying to write to it from the main table.[quote]Sorry for the long text, but after reading the latest discussion I don’t want to have people feel there is only one right way in what seems of this class between dba titans.[/quote]I agree, there is not only a single right way... if your machines can handle an unoptimized load on your database(s). Those also aren't the times when you need a SQL Dev professional like the majority of us who hang around here. There are cases where I allow my front ends to send me down dynamic SQL. Particular cases where there is an exact scenario that makes for difficult or non-optimal T-SQL coding and it won't break security or expose the company awkwardly if the front end login information is compromised. However, data storage and retrieval logic will never fit directly into object/instance logic, you'll always require widgets of some form. Procs and their ilk are generally the most secure method and allow for both logic reusage and portability as well as separation of components during troubleshooting issues at 3 in the morning when some client 8 hours east of you on the planet is screaming holy rebellions because they can't check their cat's color for today.If you want to ease development by avoiding them, that's your shop's call. Just hope the powers that be will give you time to rebuild from scratch once it gets out of scope.Wed, 08 May 2013 16:40:23 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]L' Eomot Inversé (5/3/2013)[/b][hr]Dynamic SQL??? What on earth would that be for? ... , every system I've been involved with since about 2000 has ended up (in some cases after I've overcome a lot of resistance from the backwoodsmen who want to embed DML statements directly in C++) as a system where the only thing the database presents to the outside world is a bunch of stored procedures.[/quote][quote][b]L' Eomot Inversé (5/3/2013)[/b][hr]Tightly bound app-db integration is pure poison. If you have that you can't make upgrades of DB and app independent, and for me that was absolutely essential (a company goes bust if you can't do it issue).[/quote]My personal experience differs…radically and I figure I am working in another branch of software development then you are. Your single "hard core" approach to databases (purposely provocative description) can be damaging to projects too. It depends on the particular application one works on/with and the requirements. I would argue that most applications, notably the smaller to medium custom written, are better implemented using tight binding between database and application. For ease of development as well as performance and flexibility reasons and then some possibly beyond the scope of your own particular needs and experience!If some other application needs access to a specific part of the database you can fall back on stored procedures and provide customized access rights just for them to functionally provide what is needed securely and without being schema bound there. The only situation where this happened to me was when two third party applications for the same customer as ours needed piggybacking on existing login accounts stored in that database of our application. A few stored procedures and a token system was all it took to implement this secure.In a public discussion of this matter it helps to acknowledge not every service is like facebook or a non-client specific application that is deployed over and over and just customized by third parties. Most applications I bet are custom written against a non-public database schema designs where the database is provided as part of the service an application provides and serviced by the application provider and no-one else.By no means this translates to "not needing declarative referential integrity applied to it its fullest" in the database. Some people seem to think of the database just as a necessary place to store data and keep it as dumb as possible…consider me NOT one of them! It just means business logic constraints that do not fit this declarative pattern can also be implemented in the application layer without any real drawback. But with the benefits this other approach brings, such as much better and efficient dynamic SQL and richer state management.And no, I hate ORMs with a passion, not referring to the utter SQL crap those deliver today either.As for the requirement of dynamic SQL, nearly all application back-offices need this in spades for search and/or real-time reporting purposes. If I compare the issues we have in our applications developed this way and the number we find others have, it is staggering! We never have the issues related parties have (being unable to get anything data related to be consistent and/or correct). And it is not for lack of having to do complex stuff, it is just a matter of knowing what you do and have the basics in place, plus the proper knowledge level of SQL (this last part is an area that is pretty thin out there I am afraid).Part of the reason is that most developers do not think in data, but code and at the same time DBA’s generally have the reverse mindset. Both parties like to have total control and freedom in their own domain and comfort zone! That does not mix and it would be elevating if more people would step a bit further outside their own domain and comfort zone and gain relevant experience and insights that way. Sorry for the long text, but after reading the latest discussion I don’t want to have people feel there is only one right way in what seems of this class between dba titans .Fri, 03 May 2013 08:39:47 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]Evil Kraig F (5/1/2013)[/b][hr]Last thing first. :-)[/quote]In this case that's the right order to tackle them.[quote]Without different viewpoints one can never learn. I'd prefer to try to continue the discussion to at least be sure we understand each other, even if we may disagree at the end. I will, however, strive for more clarity.[/quote]Yes, I agree. And your latest post has made me understand much better where you are at, and realise where you think I am at. I now think we are both in the same place, apart from minor incidentals (eg how horrible are app devs, or what is a narrow modular interface).[quote]While in my experience most app developers DO write horrible SQL, ...[/quote]I guess it depends what you call horrible. In my experience most developers write SQL that I would be ashamed to put my name to, but then so do most DBAs; I don't call that SQL horrible, because to me "horrible" means "much worse than the usual run of the mill", but this is run of teh mill SQL and of course nothing can be much worse than itself. The good developers and the good DBAs both write excellent SQL, nothing like that.[quote]Properly requested information from a view that expands through the optimizer properly is no worse than most other calls. It's the extra stuff that comes along for the ride that concerns me.[/quote] That extra stuff annoys me too, if there is any. Whoever wrote the query needs to learn to use filters, and maybe whoever wrote the view needs educating too. The fact that extra stuff can happen is the only reason I object to SELECT * (I don't object to it when it can't pull in extra unwanted stuff, as is often true for diagnostic code).[quote]..., I was more thinking ANY direct access to large volume data is illegal, in any form. SEC not so much but being able to pull up patient records in mass can be a significant concern. There are ways to address this, I agree, but many places I've worked at err on the side of paranoid caution.[/quote]Data protection law is pretty fierce in Europe (including UK), and many organisations are paranoid about it. I'm inclined to believe that paranoid caution is the right approach. Given how utterly useless various anonymising schemes have turned out to be we can't trust the "experts" on what it's safe to let out, and given how frequently some idiot parks unencrypted data on a pen drive or pad or laptop and leaves it somewhere public we can't trust data users to care about data protection either. Maybe the only solution is to altogether prevent people getting large chunks of data. I've not had to deal with extremely sensitive stuff as opposed not really sensitive personally identifiable data (I've tried to instill paranoia in the non-technical management even over that).[quote]This, to me, sounds like you're more familiar with coding a one size fits all view so the app can come at it with multiple approaches without having to design a proc for each call to the database. My concern with this is if your app is going to do this, it has to use dynamic SQL. To me, that should be the exception for a particular circumstance, not a design method.[/quote]Dynamic SQL??? What on earth would that be for? I think you've misunderstood badly. I certainly don't want one-size-fits-all, every system I've been involved with since about 2000 has ended up (in some cases after I've overcome a lot of resistance from the backwoodsmen who want to embed DML statements directly in C++) as a system where the only thing the database presents to the outside world is a bunch of stored procedures. [quote][quote]..., so that most of the time when an upgrade requires both schema changes and app changes I can upgrade the base schema before I upgrade the app instead of having to synchronize the upgrades - this is often useful in ensuring that you only have to support one version of the base schema even if your customers are using 4 different releases of the app.[/quote]This is not a concern I would have thought to attempt to address via views, but then my experience is always to release a SQL upgrade script (with a rollback handy) for each front end app deployment. I don't expect the app and the database to go out of sync that way.[/quote]So you never have to cope with a couple of servers and many hundreds of clients, no down-time permitted, client can be upgraded only when it is not in use; client is normally in use 24/7, which occassional periods of typically 4 hours where it is not in use; there is never a situation where many clients are simultaneously not in use; client upgrade is over net from servers, and will sometimes involve a complete reimage of client. Server upgrade has to be one server at a time, since there won't otherwise be enough servers to maintain acceptable response time. I think that's the situation when you have to design for server and client being out of sync.[quote]This is where I think we're differing wholesale. Unless I'm ordered to by on high or it makes sense to cure a problem, my app devs would never even see a view unless they're writing their own procs, too. [/quote]That's what makes me think we are both on the same page, just misunderstanding. For me, using views to isolate the app from the schema is very much a second choice, something I won't do unless I'm ordered from on-high not to take everything to a procedural interface. I had misunderstood your earlier posts to say that you though views were a bad idea and it would be better to give the app access to the base schema; so my posts were arguing that using views was better than that, which seems to have given you the impression that I thought views were the right way to go. Talk about cross-misunderstandings![quote]Dynamic SQL, transactional concerns, data volume... there's been so many problems with a tightly wound app-db integration that I do everything in my power to avoid it and make sure my app devs get exactly what they need with a simple proc call.[/quote]Tightly bound app-db integration is pure poison. If you have that you can't make upgrades of DB and app independent, and for me that was absolutely essential (a company goes bust if you can't do it issue).[quote]Which leads us to how we got into a different set of assumptions about who would be using this type of optimization:[quote]My main example is a system which has extremely intelligent, competent, and experienced people working to a set of rules which rigidly enforces best practise modularity. It is of course useful that this defends you from the idiot in marketing or PR who turns an ad-hoc query into the foundation of his whole function, but that's not the important property - support of narrow modular boundaries is. Without those narrow modular boundaries, you will very quickly have either a product that is so slow to evolve that your competitors eat up all your business or an product so complex and unwieldly and riddled with kludges that it costs the earth to support it.[/quote]I've found that neither of these are the case. I can easily modify data pulls without an app change via procedure to adjust for business rule changes, and with reasonable naming conventions and a quick proc name pull from the code by your app tier it's simplistic to get to a particular piece of code when necessary. Also, by completely de-coupling the app and database tiers via procedures, you allow for simpler troubleshooting of particular components when the old 'oh gods it's slow now!' problems arise.[/quote]That I don't understand - you seem to be contradicting yourself: you will do all in your power to avoid the app and the db being wound tightly together, but you don't mind not having narrow modular boundaries? You can completely decouple the app and database tiers without narrow modular boundaries? That doesn't make any sense - if you have narrow modular boundaries two components can't be tightly wound up in each other; if you don't, they can and almost certainly are. That's the definition of a narrow modular boundary: a set of interfaces which gets the job done while preventing the two components from getting wound up in each other.[quote]As long as your SQL developers aren't lazing around, they should be able to keep up with app building on (in my experience) a ratio of about 1 SQL:4 App. [/quote]Not terribly productive SQL developers then. I generally did far better than that :laugh:Fri, 03 May 2013 07:17:20 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxLast thing first. :-)[quote][b]L' Eomot Inversé (5/1/2013)[/b][hr]So we can either continue to try to understand each other - which may be difficult, given the different backgrounds - or we can just accept that we have done different things and therefor have needed to take different approaches to doing them.[/quote]Without different viewpoints one can never learn. I'd prefer to try to continue the discussion to at least be sure we understand each other, even if we may disagree at the end. I will, however, strive for more clarity.[quote]I too have concerns about using views, and have a very strong preference for providing a procedural interface to the apps - not because I think the app developers will write bad SQL (at least it won't be any worse than what most DBAs write, in my experience) but because they provide a functionally less constrained interface, hence poorer modularity. But of course views provide a functionally more constrained interface than does direct access to base tables, because that direct access allows the app to be dependent on the base schemata, ie there is no module boundary at all between the database and the app.[/quote]While in my experience most app developers DO write horrible SQL, but otherwise I agree with what's above.[quote]Data width is no more a concern with views than it is with base tables - and possible less of a concern because if a sloppy app developer writes select * against a view he gets only the columns which are in the select list of the view, whereas if he writes select * against a join between base tables he gets all columns in those tables.[/quote]This is a place where I think we're starting to disconnect. In my experience, views tend to end up overloaded with more joins than are necessary for any particular query. This is more what I meant by data width, and it was a horrible term. Properly requested information from a view that expands through the optimizer properly is no worse than most other calls. It's the extra stuff that comes along for the ride that concerns me.[quote]Security is potentially better with views than with direct access to base tables, so direct access to base tables is more often illegal in production that access through views. [/quote]When I mentioned this, I was more thinking ANY direct access to large volume data is illegal, in any form. SEC not so much but being able to pull up patient records in mass can be a significant concern. There are ways to address this, I agree, but many places I've worked at err on the side of paranoid caution.[quote]I think we are talking about different things here: "keeping views the same constantly" doesn't have any clear meaning to me. What I want is freedom to change the base schema without changing the apps - so I want to present the same view to the app when the base schema changes, and have no objection to having to rewrite the view in order to handle the new base schema.[/quote]We're saying the same thing, just with different words. The view being the same is it's output that needs to stay consistent. Apps shouldn't be that close to the data layer in most circumstances, at least via the design philosophies I'm comfortable with.[quote]I also want freedom to change the apps view of the data without changing the base schema - for example I may want to allow the app to see historical data which it couldn't see before, which might mean either changing a view to add some new columns or adding a new view.[/quote]This, to me, sounds like you're more familiar with coding a one size fits all view so the app can come at it with multiple approaches without having to design a proc for each call to the database. My concern with this is if your app is going to do this, it has to use dynamic SQL. To me, that should be the exception for a particular circumstance, not a design method.[quote]And I also want to be able to add columns to a view and have the old version of the app continue to work without change, so that most of the time when an upgrade requires both schema changes and app changes I can upgrade the base schema before I upgrade the app instead of having to synchronize the upgrades - this is often useful in ensuring that you only have to support one version of the base schema even if your customers are using 4 different releases of the app.[/quote]This is not a concern I would have thought to attempt to address via views, but then my experience is always to release a SQL upgrade script (with a rollback handy) for each front end app deployment. I don't expect the app and the database to go out of sync that way.[quote]These are all the classical reasons for wanting strong modular boundaries and concealing the internals of one component from other components so that developers can't accidentally create unwanted dependencies between modules and thus place unwanted constraints the way the business can develop and evolve.[quote]This is where I think we're differing wholesale. Unless I'm ordered to by on high or it makes sense to cure a problem, my app devs would never even see a view unless they're writing their own procs, too. Dynamic SQL, transactional concerns, data volume... there's been so many problems with a tightly wound app-db integration that I do everything in my power to avoid it and make sure my app devs get exactly what they need with a simple proc call.Which leads us to how we got into a different set of assumptions about who would be using this type of optimization:[quote]My main example is a system which has extremely intelligent, competent, and experienced people working to a set of rules which rigidly enforces best practise modularity. It is of course useful that this defends you from the idiot in marketing or PR who turns an ad-hoc query into the foundation of his whole function, but that's not the important property - support of narrow modular boundaries is. Without those narrow modular boundaries, you will very quickly have either a product that is so slow to evolve that your competitors eat up all your business or an product so complex and unwieldly and riddled with kludges that it costs the earth to support it.[/quote]I've found that neither of these are the case. I can easily modify data pulls without an app change via procedure to adjust for business rule changes, and with reasonable naming conventions and a quick proc name pull from the code by your app tier it's simplistic to get to a particular piece of code when necessary. Also, by completely de-coupling the app and database tiers via procedures, you allow for simpler troubleshooting of particular components when the old 'oh gods it's slow now!' problems arise.As long as your SQL developers aren't lazing around, they should be able to keep up with app building on (in my experience) a ratio of about 1 SQL:4 App. [quote]It's clear that we are not both addressing the same concerns. I think that's because of different experience.I'm coming from a product development view, where the schemata, middleware, apps, and front ends all went together and were all sold to lots of customers who demanded new features, 24X7 availability, 24/7 support, minimal unscheduled downtime risk (we couldn't guarantee the system would continue to run if vaporised by a thermonuclear device, so we didn't claim zero unscheduled downtime risk), regular upgrades, conformance with data protection regulations, very user-friendly and enjoyable end-user interface that would conform with all the latest fads and fashions, all of which meant rapid change which in turn meant very thoroughly modular design and construction was essential. You seem to be coming from a situation where you have at any time a single user community, with a well understood requirement and rare and slow change, so rare and slow that complex interdependencies between app layer and data interface layer and base schema are perfectly acceptable and modularity doesn't much matter. [/quote]I also come from such a location, but I haven't worked in vendorware. All of my development and design has been either in-house custom software or web-portal backends. Rare and slow changes however are not part of either of those design philosophies, however, though your milage may vary depending on what shop you're at. Well understood requirements are rare, though I've been at a few places that were pretty good at it. Most places you're iteratively deploying because users don't want to look at 'old dev data', they want to see what they put in yesterday... even if they could put in stuff from yesterday in dev.I disagree however that it's a complex interdependency. It's proc. *Best caveman voice* You call proc, I deliver data. Dependency done. *end voice* No app interface to the data directly in any way. I think that's the significant disconnect we have. I don't personally care how close the app [i]devs[/i] get to the data model and understanding it. The [i]app[/i] should never be that close.Wed, 01 May 2013 13:56:13 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]Evil Kraig F (4/29/2013)[/b][hr]At the same time, I rarely take this approach for app devs to access my data because of all the intrinsic concerns that views bring into play, one of which is the one you mention. Another is data width, one of my biggest concerns. Additionally, it's against most best practices and depending on your security concerns, straight up illegal in production. The fact that I have usually dealt with medical and/or securities and retirement data through my career may have colored my opinion in this regard.[/quote]I too have concerns about using views, and have a very strong preference for providing a procedural interface to the apps - not because I think the app developers will write bad SQL (at least it won't be any worse than what most DBAs write, in my experience) but because they provide a functionally less constrained interface, hence poorer modularity. But of course views provide a functionally more constrained interface than does direct access to base tables, because that direct access allows the app to be dependent on the base schemata, ie there is no module boundary at all between the database and the app. Data width is no more a concern with views than it is with base tables - and possible less of a concern because if a sloppy app developer writes select * against a view he gets only the columns which are in the select list of the view, whereas if he writes select * against a join between base tables he gets all columns in those tables. Security is potentially better with views than with direct access to base tables, so direct access to base tables is more often illegal in production that access through views. [quote]Trying to keep views the same constantly is a nice to have to me, but I have no issue with ripping a view to pieces and rebuilding it if I need to, as a view is merely a subquery container for most usage and should be able to be traced in sys.sql_modules. If your app coders are blazing ahead of your sql resources, then you've unbalanced your development team. You've over-hired on app guys (or maybe you've just got a couple of superstars). It's time to get them more support... even if you have to let go of an app guy to get your ratio(s) right.[/quote]I think we are talking about different things here: "keeping views the same constantly" doesn't have any clear meaning to me. What I want is freedom to change the base schema without changing the apps - so I want to present the same view to the app when the base schema changes, and have no objection to having to rewrite the view in order to handle the new base schema. I also want freedom to change the apps view of the data without changing the base schema - for example I may want to allow the app to see historical data which it couldn't see before, which might mean either changing a view to add some new columns or adding a new view. Neither of these things strike me as "keeping views the same constantly". And I also want to be able to add columns to a view and have the old version of the app continue to work without change, so that most of the time when an upgrade requires both schema changes and app changes I can upgrade the base schema before I upgrade the app instead of having to synchronize the upgrades - this is often useful in ensuring that you only have to support one version of the base schema even if your customers are using 4 different releases of the app. These are all the classical reasons for wanting strong modular boundaries and concealing the internals of one component from other components so that developers can't accidentally create unwanted dependencies between modules and thus place unwanted constraints the way the business can develop and evolve.[quote]So...[quote]Given these real world circumstances, where queries are written by people who don't know the underlying schemmata. proper declarative referential integrity enforcement gives the optimiser a great assist in getting the best possible performance out of the system for us.[/quote]You're using a shop where inexperienced coders are relying on workarounds for ad-hoc queries as your example of when to use this optimization.[/quote]My main example is a system which has extremely intelligent, competent, and experienced people working to a set of rules which rigidly enforces best practise modularity. It is of course useful that this defends you from the idiot in marketing or PR who turns an ad-hoc query into the foundation of his whole function, but that's not the important property - support of narrow modular boundaries is. Without those narrow modular boundaries, you will very quickly have either a product that is so slow to evolve that your competitors eat up all your business or an product so complex and unwieldly and riddled with kludges that it costs the earth to support it.[/quote]I just feel like we're addressing the wrong concern with this. I do appreciate the very detailed explanation however of where something like this could actually come to use... as a stopgap until they fix things properly.I've edited this twice now and I think it finally reads correctly. I'm honestly not trying to come off as an ***, but I just can't see people at the level of skill you're discussing having a clue this could help them, nor them looking for it in the first place. Anyone with enough skill to know and/or remember the intricacies of the optimizer at this level and requires to use this for your use-case is either insanely overwhelmed with workload or is in the first steps of cleanup when they arrive at a new site.[/quote]I think it's pretty clear that when you wrote that you hadn't understood what I was talking about. It seems equally clear that I haven't a clue what you are getting at. It's clear that we are not both addressing the same concerns. I think that's because of different experience.I'm coming from a product development view, where the schemata, middleware, apps, and front ends all went together and were all sold to lots of customers who demanded new features, 24X7 availability, 24/7 support, minimal unscheduled downtime risk (we couldn't guarantee the system would continue to run if vaporised by a thermonuclear device, so we didn't claim zero unscheduled downtime risk), regular upgrades, conformance with data protection regulations, very user-friendly and enjoyable end-user interface that would conform with all the latest fads and fashions, all of which meant rapid change which in turn meant very thoroughly modular design and construction was essential. You seem to be coming from a situation where you have at any time a single user community, with a well understood requirement and rare and slow change, so rare and slow that complex interdependencies between app layer and data interface layer and base schema are perfectly acceptable and modularity doesn't much matter. So we can either continue to try to understand each other - which may be difficult, given the different backgrounds - or we can just accept that we have done different things and therefor have needed to take different approaches to doing them.edit: spelling and quote tagsWed, 01 May 2013 08:36:21 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]Grant Fritchey (4/29/2013)[/b][hr]True. If you don't need those joins, you're better off writing the query without them. But, if we are getting down to the point where people have properly built queries, then tuning and optimization changes. You don't need things like the simplification optimizations. [i]But, WAY too many people are not writing their queries correctly and those optimizations help them.[/i] I don't recommend this instead of tuning queries.[/quote]Okay, I can follow and agree with that logic. I guess feeding someone with the firehose instead of step by step MIGHT be construed as a little overboard. :-)@Tom (Didn't quote for length):Well, you're quite correct there, it's a narrow perspective. However, someone who's writing against views and can't see base schema also won't be able to adjust the FKs or see the execution plan, so this article isn't really written towards them in my mind. It's written for the person trying to cater to them, which means they're already pretty deep in the optimizer to care about this topic at all. However, you [i]are[/i] showing a real world example of where this makes an actual difference, as the alternate would be to write umpteen different views depending on exactly what data the developer was trying to access.At the same time, I rarely take this approach for app devs to access my data because of all the intrinsic concerns that views bring into play, one of which is the one you mention. Another is data width, one of my biggest concerns. Additionally, it's against most best practices and depending on your security concerns, straight up illegal in production. The fact that I have usually dealt with medical and/or securities and retirement data through my career may have colored my opinion in this regard.You're right, however, that ad-hoc queries written against the base schema or the views are difficult to monitor at best. By their nature, however, ad-hocs are supposed to be one-offs. Your business should understand that. If you're ad-hoc'ing the same query at the end of every month, it's no longer ad-hoc. It's requirements you haven't given to your tech department.Trying to keep views the same constantly is a nice to have to me, but I have no issue with ripping a view to pieces and rebuilding it if I need to, as a view is merely a subquery container for most usage and should be able to be traced in sys.sql_modules. If your app coders are blazing ahead of your sql resources, then you've unbalanced your development team. You've over-hired on app guys (or maybe you've just got a couple of superstars). It's time to get them more support... even if you have to let go of an app guy to get your ratio(s) right.So...[quote]Given these real world circumstances, where queries are written by people who don't know the underlying schemmata. proper declarative referential integrity enforcement gives the optimiser a great assist in getting the best possible performance out of the system for us.[/quote]You're using a shop where inexperienced coders are relying on workarounds for ad-hoc queries as your example of when to use this optimization. You're right, they exist, and when I get there I try to fix that problem. I understand your point, and you're correct within that venue. That the venue exists is a whole different issue I probably shouldn't be derailing the topic with, so I won't go too deep unless the conversation hangs a left turn there.I just feel like we're addressing the wrong concern with this. I do appreciate the very detailed explanation however of where something like this could actually come to use... as a stopgap until they fix things properly.I've edited this twice now and I think it finally reads correctly. I'm honestly not trying to come off as an ***, but I just can't see people at the level of skill you're discussing having a clue this could help them, nor them looking for it in the first place. Anyone with enough skill to know and/or remember the intricacies of the optimizer at this level and requires to use this for your use-case is either insanely overwhelmed with workload or is in the first steps of cleanup when they arrive at a new site.Mon, 29 Apr 2013 17:52:12 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]Evil Kraig F (4/26/2013)[/b][hr][quote][b]Grant Fritchey (4/26/2013)[/b][hr][quote][b]TheGreenShepherd (4/26/2013)[/b][hr]Don't mean to pile on here, but yeah. I fail to understand why someone would query this data in this fashion. Yeah, it improves performance for that query but it's unlikely that you'd ever actually write that query.[/quote]Here's a [url=http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/]blog post I wrote up[/url] that gives a different example, probably more realistic, to show how foreign keys help. They really do.[/quote]Grant, unfortunately, I'm of the same mind as the above. I checked out your blog post and really, there's not a lot of difference. If you don't have RI in place, you have to go to those tables to make sure you have data limiters, I agree. Once you have RI in place everything you did was off the 'p' table, including the where clause. He did a where exists, you did a join, but the limitation logic is the same.[/quote]Craig, I think you are seeing things froma very narrow perspective: a perspective where the people who write queries (a) write them against base tables, (b) know all the base table schemata. This is very different from the situation where application developers write queries against views and are not permitted to know the base schemata. In the latter situation, the optimiser is presented with a query whose from clause includes view expansions, which may be joining tables that it can eliminate because the particular query doesn't use anything from those tables.Yes, it's very nice from the point of view of a DBA to be able to design all the queries himself - no app devs write queries, there is no facility which provides for ad hoc queries, and only queries written by the DBA team are ever run. But in the real world it isn't always like that. I'm a great believer in proper modularity and levels of abstractrion, and I don't believe an app developer should know the base schemata (mostly because if he does he'll write stuff that depends on them, so that further evolution of the base schemata becomes impossible - and this includes the case where what was supposed to be an ad hoc query is now the most important thing used by the marketing department or by the publicity department or by some one else with a lot of muscle); and I prefer to bury the schemata behind a stored procedure interface, rather than a view interface, in the hope of preclusing ad-hoc queries produced by all sorts of random people; but I can't guarantee to achieve that, and may have to provide some views that expose the data but not any teh structure of any part of any schema for which I have no real guarantee that the structure will not need to change in a few years time to enable us to accept new requirements, cope with new regulations, and enforce new business rules. Given that the requirement changes, the base schemata will need to change, so even if I start out exposing a view per base table (usually a fairly stupid idea, but not always) those views might rapidly cease to be in one to one correspondance with base tables.Given these real world circumstances, where queries are written by people who don't know the underlying schemmata. proper declarative referential integrity enforcement gives the optimiser a great assist in getting the best possible performance out of the system for us.Mon, 29 Apr 2013 16:50:03 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxaccidental post - deletedMon, 29 Apr 2013 16:24:03 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]Evil Kraig F (4/26/2013)[/b][hr][quote][b]Grant Fritchey (4/26/2013)[/b][hr][quote][b]TheGreenShepherd (4/26/2013)[/b][hr]Don't mean to pile on here, but yeah. I fail to understand why someone would query this data in this fashion. Yeah, it improves performance for that query but it's unlikely that you'd ever actually write that query.[/quote]Here's a [url=http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/]blog post I wrote up[/url] that gives a different example, probably more realistic, to show how foreign keys help. They really do.[/quote]Grant, unfortunately, I'm of the same mind as the above. I checked out your blog post and really, there's not a lot of difference. If you don't have RI in place, you have to go to those tables to make sure you have data limiters, I agree. Once you have RI in place everything you did was off the 'p' table, including the where clause. He did a where exists, you did a join, but the limitation logic is the same.FK's don't help where it matters, which is getting data from the connected tables. Well, they don't help anymore than a tuned index does. All trusted FKs allow for is a shortcircuit on join logic used as restrictors that you don't even need to include in the query if you have FKs in the first place.Well, that's what they're there for, so I'm okay with that. But to me, this isn't really 'optimization'. This is using a tool, similar to indexes, that will slow some things down and speed up others, and can be overused. If you're always associating two tables on the same field to restrict rows, using an FK to enforce it and being able to avoid the churn is probably a good idea for that restriction pattern. If you're connecting up for data, you might as well just use an index and truck on without the constant overhead of the FK.[/quote]True. If you don't need those joins, you're better off writing the query without them. But, if we are getting down to the point where people have properly built queries, then tuning and optimization changes. You don't need things like the simplification optimizations. But, WAY too many people are not writing their queries correctly and those optimizations help them. I don't recommend this instead of tuning queries.Mon, 29 Apr 2013 04:07:58 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxI missed this somehow when it first came out - or at least I don't recall it. It's a good and interesting article, but I think it would have been better if it had used queries against a join view for the example - it would have reduced the number of silly comments I suspect.Sat, 27 Apr 2013 12:59:21 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxNice article. Apparently in the editing of the article content, some minor inconsistencies were left in toward the end. In the paragraph that begins "Let's make it a bit more interesting," you refer to the members table which should be the SalesOrderdetail table, and your reference to TeamID column should actually be CustomerID column.Sat, 27 Apr 2013 05:58:06 GMTbriankwartlerhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxVery nice write up. Thanks for posting it!Fri, 26 Apr 2013 16:36:46 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]Grant Fritchey (4/26/2013)[/b][hr][quote][b]TheGreenShepherd (4/26/2013)[/b][hr]Don't mean to pile on here, but yeah. I fail to understand why someone would query this data in this fashion. Yeah, it improves performance for that query but it's unlikely that you'd ever actually write that query.[/quote]Here's a [url=http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/]blog post I wrote up[/url] that gives a different example, probably more realistic, to show how foreign keys help. They really do.[/quote]Grant, unfortunately, I'm of the same mind as the above. I checked out your blog post and really, there's not a lot of difference. If you don't have RI in place, you have to go to those tables to make sure you have data limiters, I agree. Once you have RI in place everything you did was off the 'p' table, including the where clause. He did a where exists, you did a join, but the limitation logic is the same.FK's don't help where it matters, which is getting data from the connected tables. Well, they don't help anymore than a tuned index does. All trusted FKs allow for is a shortcircuit on join logic used as restrictors that you don't even need to include in the query if you have FKs in the first place.Well, that's what they're there for, so I'm okay with that. But to me, this isn't really 'optimization'. This is using a tool, similar to indexes, that will slow some things down and speed up others, and can be overused. If you're always associating two tables on the same field to restrict rows, using an FK to enforce it and being able to avoid the churn is probably a good idea for that restriction pattern. If you're connecting up for data, you might as well just use an index and truck on without the constant overhead of the FK.Fri, 26 Apr 2013 14:50:31 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]arnipetursson (4/26/2013)[/b][hr]I agree. It is a tautological example. You would not need an "exists" check if you have a foreign key constraint.[/quote]Though I know that I have used this logic myself and simplified my queries because I knew that a foreign key guaranteed the behavior I desired, in retrospect, I question if that was my best strategy. By including the "exists" I am, first of all, making clear of what my target data should consist which may help someone reading my code to better understand my intentions. Most importantly, my query will return the correct data even if someone removes the foreign key constraint and adds invalid data into the table. While I shouldn't have to worry about that possibility, in the real world, databases are not always locked down and strictly controlled by a conscientious DBA and the possibility of a change like a constraint going missing is hardly unheard of. In such an environment there are still plenty of paths to catastrophe but a little redundancy can be fairly low-cost insurance against some of the more common screw-ups, particularly if the optimizer reduces any performance impact.- LesFri, 26 Apr 2013 13:13:03 GMTlnolandhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxThank you for a very timely (for me) and most excellent post. I had all the right indexes and FK constraints set-up on my web application database. When I checked, I found 7 (out of 23) FK contraints were not trusted. Thank you also to Peter for pointing out why I wasn't able to get them trusted (Not for Replication). Note, if you use the table designer FK relationships dialog box, the property is called "Enforce for Replication" and I had to mark it as "Yes" to get SS to check and start trusting the FK constraints. All the trusted constraints already had this marked as "yes" in the the Table Designer FK relationships dialog box. Also, I didn't have to drop the FK constraint and re-create it, simply changing the "enforce for replication" to "yes" and telling SS to "check existing data on creation or re-enabling" to yes and save the table was enough for SS to start trusting these constraints. After doing this for all previously untrusted 7 constraints, the page load times in my web application have gone from an agonising 10 seconds down to 3 seconds! A tremendous improvement!!However, there's still room for more improvement in my app, it would be great to see sub 1 second page load times on a fast client network connection. I'm sure the hardware's capable, I just need to find where the bottlenecks are and remove them! Articles like this definitely help!!Fri, 26 Apr 2013 12:51:48 GMTkulwant.bhogalhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxNice post!Fri, 26 Apr 2013 12:01:37 GMTGlen Cooperhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]TheGreenShepherd (4/26/2013)[/b][hr]Don't mean to pile on here, but yeah. I fail to understand why someone would query this data in this fashion. Yeah, it improves performance for that query but it's unlikely that you'd ever actually write that query.[/quote]There are a few tables in some of the more active databases in my current shop that do not have foreign keys. When I asked about them, or just about any other design curiosity, the answer is always 'it was done years ago by consultants who are long gone'. SInce there is no ROI in fixing things like this, they just stay there. Now, I can see that at some point in the future, a problem may pop up, and someone will say that we need a foreign key. So it's nice to know that if the performance question comes up, this will save a whole lot of head scratching.Fri, 26 Apr 2013 10:28:57 GMTlptechhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]TheGreenShepherd (4/26/2013)[/b][hr]Don't mean to pile on here, but yeah. I fail to understand why someone would query this data in this fashion. Yeah, it improves performance for that query but it's unlikely that you'd ever actually write that query.[/quote]Here's a [url=http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/]blog post I wrote up[/url] that gives a different example, probably more realistic, to show how foreign keys help. They really do.Fri, 26 Apr 2013 10:08:34 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxI agree. It is a tautological example. You would not need an "exists" check if you have a foreign key constraint.However the part on "not_trusted" is very useful.And of course foreign key constraints (that are "trusted") in addition to indexes help the optimizer make better decisions.Fri, 26 Apr 2013 09:59:45 GMTarnipeturssonhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxDon't mean to pile on here, but yeah. I fail to understand why someone would query this data in this fashion. Yeah, it improves performance for that query but it's unlikely that you'd ever actually write that query.Fri, 26 Apr 2013 09:52:28 GMTTheGreenShepherdhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxI realize that it's a 3 year old article but I missed it previously. Thought I'd take a minute to say "very nicely done" and excellent tip. Thanks for taking the time tow write it.Fri, 26 Apr 2013 07:54:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxVery interesting. I've been playing around a bit, here's my test script so far: -[code="sql"]USE tempdb;SET NOCOUNT ON;-- CONDITIONALLY DROP TABLESIF object_id('test_Details') IS NOT NULLBEGIN DROP TABLE test_Details;END;IF object_id('test') IS NOT NULLBEGIN DROP TABLE test;END;-- CREATE TEST TABLECREATE TABLE test (test_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(36));-- CREATE TEST_DETAILS TABLECREATE TABLE test_Details (test_Details_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, test_ID INT NOT NULL, Info VARCHAR(36));CREATE NONCLUSTERED INDEX nc_test_Details ON test_Details (test_ID);-- FILL TEST TABLE WITH 50 RANDOM ROWSINSERT INTO test (Name)SELECT TOP 50REPLACE(CAST(NEWID() AS VARCHAR(36)) COLLATE Latin1_General_BIN2,'-','')FROM master.sys.columns sc1 CROSS JOIN master.sys.columns sc2 CROSS JOIN master.sys.columns sc3;-- CREATE FOREIGN KEY RELATIONSHIPALTER TABLE test_Details WITH NOCHECK ADD CONSTRAINT FK_test_Details_test_ID_test FOREIGN KEY (test_ID) REFERENCES test(test_ID);-- FILL TEST_DETAILS TABLE WITH 1,000,000 RANDOM ROWSINSERT INTO test_Details(test_ID, Info)SELECT TOP 1000000(ABS(CHECKSUM(NEWID())) % 50) + 1,REPLACE(CAST(NEWID() AS VARCHAR(36)) COLLATE Latin1_General_BIN2,'-','')FROM master.sys.columns sc1 CROSS JOIN master.sys.columns sc2 CROSS JOIN master.sys.columns sc3;-- DECLARE HOLDER VARIABLE AND TIMING VARIABLESDECLARE @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;-- CHECK TRUST STATESELECT QUOTENAME(OBJECT_NAME(Parent_Object_ID)) AS tblName, QUOTENAME(name) AS constraintName, is_not_trustedFROM sys.foreign_keys;-- CLEAR DOWN CACHE TO ENSURE CACHING DOESN'T AFFECT PERFORMANCE TESTSDBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SELECT @StartTime = GETDATE();SELECT @HOLDER = td.test_Details_IDFROM test_Details tdWHERE EXISTS (SELECT 1 FROM test t WHERE t.test_ID = td.test_ID);SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('NOT TRUSTED Duration: %s',0,1,@Duration) WITH NOWAIT;-- MAKE CONSTRAINT TRUSTEDALTER TABLE test_Details WITH CHECK CHECK CONSTRAINT FK_test_Details_test_ID_test;-- CHECK TRUST STATESELECT QUOTENAME(OBJECT_NAME(Parent_Object_ID)) AS tblName, QUOTENAME(name) AS constraintName, is_not_trustedFROM sys.foreign_keys;-- CLEAR DOWN CACHE TO ENSURE CACHING DOESN'T AFFECT PERFORMANCE TESTSDBCC FREEPROCCACHE WITH NO_INFOMSGS;DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;SELECT @StartTime = GETDATE();SELECT @HOLDER = td.test_Details_IDFROM test_Details tdWHERE EXISTS (SELECT 1 FROM test t WHERE t.test_ID = td.test_ID);SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);RAISERROR('TRUSTED Duration: %s',0,1,@Duration) WITH NOWAIT;[/code]Which reports: -[code="plain"]NOT TRUSTED Duration: 00:00:00:770TRUSTED Duration: 00:00:00:153[/code]Fri, 26 Apr 2013 07:11:33 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxVery nice post, and something else I will use when Executives try to tell me that I should be creating new databases to store data that is related to data in our main database, because relational theory... is just a theory... :)Fri, 26 Apr 2013 07:07:39 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxA very interesting article, but I have a question. Surely if you have a trusted foreign key constraint in place between two tables, you should not write such an "if exists" check, as by doing so you are effectively not trusting that SQL Server has managed the foreign key relationship properly?Fri, 26 Apr 2013 06:15:11 GMTChris Tunsteadhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]ralbers (4/26/2013)[/b][hr]This article is very helpfull. One question: is it also advisable to use these constraints in sqlserver2005? thanx,Riet Albers[/quote]Yes. Same rules.Fri, 26 Apr 2013 05:23:32 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxThis article is very helpfull. One question: is it also advisable to use these constraints in sqlserver2005? thanx,Riet AlbersFri, 26 Apr 2013 04:40:05 GMTralbershttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]neeraj.it (4/25/2013)[/b][hr]Based on my experience of a database where million of records are present and being operated for inserted/updated/deleted in a year. We were having proper constraints for data integrity purpose and there was no problem at all in intial time, but later on DML operations getting slower and reached to unacceptable level sometimes failed to execute. Then we removed the constraints to get performance back in the database.Hence for SELECT operation definitely there is a benefit else DML operations will be slow later on based on amount of data.Thanks[/quote]But now your system is operating without a safety net. You could be getting dirty data. In fact, as data increases the chances of having it dirty increase pretty radically too. Instead of just dropping constraints, you should have identified what was causing the poor performance directly (scans because you needed an index or something along those lines).Fri, 26 Apr 2013 03:36:44 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]neeraj.it (4/25/2013)[/b][hr]Based on my experience of a database where million of records are present and being operated for inserted/updated/deleted in a year. We were having proper constraints for data integrity purpose and there was no problem at all in intial time, but later on DML operations getting slower and reached to unacceptable level sometimes failed to execute. Then we removed the constraints to get performance back in the database.Hence for SELECT operation definitely there is a benefit else DML operations will be slow later on based on amount of data.Thanks[/quote]Are those DML operations for individual records or large sets? And if for sets...even as large as full table updates?And what is the size of tables being referrenced? Many tables being refferenced are lookup tables for things like record status. These are critical to keep correct or else all business logic on top turns to jelly. I would never sacrefice this sort of RI for speed gains in this area.If there is a clear situation to advice no referential constraints in, it would be prudent to scope that advice properly. Not every FK constraint has identical importance if you are forced to choose!Did you check if there were needles updates being done on the foreign key fields (like no changing values)? I ain't exactly certain what happens when you update a field with the same identical value. For indexes i think i know the index update doesn't get done as it is not needed. But for foreign key constraint checks....i am not certain. It could even different between SQL Server editions.It is worth a few tests, as throwing out RI rules just to gain speed sounds like the last thing one wants to do!Fri, 26 Apr 2013 02:35:41 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxHi all,I tried the same script but in both cases I got the same execution plan scanning the 2 clustered indexes.So did I miss anything here? As this will be helpful for tuning my database.Thanks,IyadFri, 26 Apr 2013 01:33:33 GMTiyad.tabellohttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxBased on my experience of a database where million of records are present and being operated for inserted/updated/deleted in a year. We were having proper constraints for data integrity purpose and there was no problem at all in intial time, but later on DML operations getting slower and reached to unacceptable level sometimes failed to execute. Then we removed the constraints to get performance back in the database.Hence for SELECT operation definitely there is a benefit else DML operations will be slow later on based on amount of data.ThanksThu, 25 Apr 2013 22:53:15 GMTneeraj.ithttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxhiI agree that if i am not using referential integrity i will be ending with dirty data. But when thedb is structured as no master record deletion and having the integrity check as business rules at the time of insertion. We can avoid use of foreign keys at those transactions tables.Since transactions tables will always have more reference columns. Also, transaction tables will be mainly used for insert/update operations. Direct Retrieval from transactions tables will be less on large size databases/tables which involves in many insert/update operations. Also, if we retrieve the information from transactions table, the no of records will always be very less which can be handled by indexes. Good execution plan is not required in tat situation.Yes i may be missing something. My understandings on fks are limited. Since i ever used it much in our DB. I will definitely think about using more fks/constraints. Thanks to all for sharing your views on my opinion.RegardsVMSSanthoshMon, 15 Nov 2010 09:48:43 GMTvmssanthoshhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]vmssanthosh (11/15/2010)[/b][hr]I didnt say index is a replacement for foreign key. I says that query performance depends on the indexes not on referential integrity components. even it helps in generating a good execution plan, we always have the option of changing the execution plan using the query hints.So why we need to sacrifice the insert/update operations performance.When we define our transactions tables with more foreign keys/constraints, it in turn reduce the insert/update operation performance.Most of the transactions details will be retrieved using the index columns. then it will be joined with other references to get appropriate information. So retrieving data from transactions table will always be faster and it needs less/no optimization plans.RegardsVMSSanthosh[/quote]But, query hints aren't magic. You can't control every aspect of the optimizer. You can't tell it to ignore joins that it just doesn't need with any hint, but the optimizer can ignore joins because of referential integrity. We are talking two radically different things here, and I think you're missing it. There are performance improvements offered by referential integrity. That's not saying it's magic either, but you can't just dismiss it out of hand. You need to understand what you're losing if you get rid of it. That's not even talking about the potential for dirty data that most businesses would prefer to avoid.Mon, 15 Nov 2010 09:25:48 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxI didnt say index is a replacement for foreign key. I says that query performance depends on the indexes not on referential integrity components. even it helps in generating a good execution plan, we always have the option of changing the execution plan using the query hints.So why we need to sacrifice the insert/update operations performance.When we define our transactions tables with more foreign keys/constraints, it in turn reduce the insert/update operation performance.Most of the transactions details will be retrieved using the index columns. then it will be joined with other references to get appropriate information. So retrieving data from transactions table will always be faster and it needs less/no optimization plans.RegardsVMSSanthoshMon, 15 Nov 2010 09:17:36 GMTvmssanthoshhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]vmssanthosh (11/15/2010)[/b][hr]hiWe mostly do bulk loads in our databases. nearly a lakh data will get inserted/updated in our databases everyday. In those cases, fk relationship will degrade the insert/update operation. We handles the referential integrity at business rules. Also, we dont have any delete operation for any of our master tables. we does active/deactive for our master tables.Our indexes are taking care of query performance and we uses index hints to do the same if required.According to me, it depends upon the way we are maintaining the databases. My point is it should not misguide the people tat fk's improves the database performance. Most of the transactional databases doesnt require fk relationship.RegardsVMSSanthosh[/quote]Actually, FK's do increase performance. The optimizer will know, and understand, that you have an enforced relationship and take advantage of that information in ways that simply having an index in place will not provide. The fact that you're also talking about using hints as a casual and standard method suggests you have a number of tuning opportunities that you might not be exploring. I'd suggest digging a bit deeper in understanding how this stuff works.Mon, 15 Nov 2010 07:41:25 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxI can agree with your point of view where it concerns your own database usage, which I cannot see as typical of relational databases in general. Rebulding all data every day surely is not the norm as many databases are simple too big to even try anything like that or need to be accessible 24/7.The only situation where I can personally see foreign keys as not/less needed is in a staging database to feed a dimensional data warehouse. Anyway, an index is not a replacement for a foreign key, nor is the other way around.Mon, 15 Nov 2010 07:30:11 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxI guess you didn't read what everyone has written.DougMon, 15 Nov 2010 07:27:13 GMTDouglas Osborne-456728http://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxhiWe mostly do bulk loads in our databases. nearly a lakh data will get inserted/updated in our databases everyday. In those cases, fk relationship will degrade the insert/update operation. We handles the referential integrity at business rules. Also, we dont have any delete operation for any of our master tables. we does active/deactive for our master tables.Our indexes are taking care of query performance and we uses index hints to do the same if required.According to me, it depends upon the way we are maintaining the databases. My point is it should not misguide the people tat fk's improves the database performance. Most of the transactional databases doesnt require fk relationship.RegardsVMSSanthoshMon, 15 Nov 2010 07:21:31 GMTvmssanthoshhttp://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspx[quote][b]vmssanthosh (11/15/2010)[/b][hr]hiI have a debate over this concept.Foreign key and check constraints are referential integrity components. How we can say this will increase the query performance. Yes it may increase the performance in some aspects but not always. Query performance always depends upon the indexes getting used. not with the referential integrity components.If i dont have any physical delete operation in my database. Why do i need a foreign key/check constraint, which is going to degrade the performance of insert/update operations.Please correct me if i am wrong.RegardsVMSSanthosh[/quote]You are mostly correct, but you do read the data far more often then you write it, correct? It is in this situation that the optimizer can be better with absolute certainty about the relationships. Hence it is often good to have referential integrity with foreign keys that are trusted.Where the optimizer can do a better job is in eliminating joins that are not required when the foreign keys are trusted. Imagine a view that uses inner joins to combine attributes from several normalised tables and present it as one table. If a consuming query that uses this view and only requests attributes from one or two tables, then the query optimizer can only remove the unneeded joins if there are trusted foreign keys to these tables that are used in the view. It can do so, because it knows implicitly the records in the unneeded tables do exist, which would affect the outcome of the query if they did not.Without trusted relationships, the optimiser must make a plan that joins the unused tables too, just to make sure a record can be part of the result set. This is the type of real situation you see performance benefits of having trusted relationships.Mon, 15 Nov 2010 06:52:38 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic1008156-2810-1.aspxhiI have a debate over this concept.Foreign key and check constraints are referential integrity components. How we can say this will increase the query performance. Yes it may increase the performance in some aspects but not always. Query performance always depends upon the indexes getting used. not with the referential integrity components.If i dont have any physical delete operation in my database. Why do i need a foreign key/check constraint, which is going to degrade the performance of insert/update operations.Please correct me if i am wrong.RegardsVMSSanthoshMon, 15 Nov 2010 06:42:15 GMTvmssanthosh