InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 03:06:14 GMT20http://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Dean Cochrane (6/21/2012)[/b][hr][quote][b]patrickmcginnis59 (6/21/2012)[/b][hr]I freely admit that I'd like to be biased toward portability in the general case, but that it would take additional effort to materialize that bias in specific efforts.[/quote]I think that's the general case: who in their right mind is going to be against portability if that additional effort is low enough? Nobody. However there is a point at which the additional cost of making something portable is outweighed by the costs of the sacrifices you have to make to do so, as multiple posters upstream have noted.[/quote]I can agree with that. Its about judging the costs and benefits of portability.[quote]Relational databases are already the red-headed stepchild of the application development world. There aren't that many developers (or even application architects) who truly understand relational databases. In the SQL Server world, I will point to the raging misuse of (NOLOCK) as evidence of this (in DB2 it is WITH UR).[/quote]True, but I would like to offer a seperation of concerns here, for instance, (I believe) you can develop an application with persistance that has absolutely no relational technology at all that can still be an objectively correct design, but a race condition is still objectively a race condition no matter what technology you're using.Thu, 21 Jun 2012 08:22:06 GMTpatrickmcginnis59http://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]patrickmcginnis59 (6/21/2012)[/b][hr]I freely admit that I'd like to be biased toward portability in the general case, but that it would take additional effort to materialize that bias in specific efforts.[/quote]I think that's the general case: who in their right mind is going to be against portability if that additional effort is low enough? Nobody. However there is a point at which the additional cost of making something portable is outweighed by the costs of the sacrifices you have to make to do so, as multiple posters upstream have noted.Relational databases are already the red-headed stepchild of the application development world. There aren't that many developers (or even application architects) who truly understand relational databases. In the SQL Server world, I will point to the raging misuse of (NOLOCK) as evidence of this (in DB2 it is WITH UR).To allow such people to design 'portable' applications is just asking for trouble. Early in my career I worked on just such an application, and it was a complete disaster that drove the company into bankruptcy.Thu, 21 Jun 2012 07:57:30 GMTDean Cochranehttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxIf you want to achieve application portablity and performance, you need to design for it, and such design usually requires two separate code bases. You need to separate the Data Access Layer from the Application Layer. The biggest differences are in how each database systems stores, retieves, and manipulates data. How the application works with data should be database agnostic.If you isolate the access to the database you can port your application to a different RDBMS by writing a DAL specifically for that system. The actual application should not have to change.Thu, 21 Jun 2012 07:45:10 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]cfradenburg (6/21/2012)[/b][hr][quote][b]Grant Fritchey (6/21/2012)[/b][hr]There is a very real, and evidently apparent in more than just the SQL sphere, understanding that you can design for performance on a platform, or you can design for portability between platforms, but you can't really design for both (without forking your code, readily acknowledged there, but that reinforces the point, not negates it).[/quote]And if you can provide an example of a large DB designed for portability that performs well on multiple platforms I would love to see it. Because if there's a way to design for portability and have it perform well there's no reason not to do that.[/quote]I've worked with at least two that I know of, they apparently perform well enough to sell and use. I'm sure that portability exacted a cost, so my interest is that despite these costs, the vendor felt it was worth it, so there really is this concern out there. One thing to note is that these were application vendors where the business processing did happen in application code.Additionally its worth mentioning that there is an SQL standards effort, with several published standards. And relational design theory is itself portable.I freely admit that I'd like to be biased toward portability in the general case, but that it would take additional effort to materialize that bias in specific efforts.Thu, 21 Jun 2012 07:31:52 GMTpatrickmcginnis59http://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxLike Oracle on Windows vs. UNIX. Oracle is a pig on Windows.Thu, 21 Jun 2012 07:26:44 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Grant Fritchey (6/21/2012)[/b][hr]There is a very real, and evidently apparent in more than just the SQL sphere, understanding that you can design for performance on a platform, or you can design for portability between platforms, but you can't really design for both (without forking your code, readily acknowledged there, but that reinforces the point, not negates it).[/quote]And if you can provide an example of a large DB designed for portability that performs well on multiple platforms I would love to see it. Because if there's a way to design for portability and have it perform well there's no reason not to do that.Thu, 21 Jun 2012 07:04:20 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]patrickmcginnis59 (6/21/2012)[/b][hr][quote][b]Grant Fritchey (6/21/2012)[/b][hr]Just received an interesting comment on this over on my blog that included this:[quote]“By now, you might be able to see where I’m going in this section. I have made references above to other databases and how features are implemented differently in each. With the exception of some read-only applications, it is my contention that building a wholly database-independent application that is highly scalable is extremely hard — it is, in fact, quite impossible unless you know exactly how each database works in great detail. And, if you knew how each database worked in great detail, you’d understand that database independence is not something you really want to achieve (a very circular argument!).” Tom Kyte -Expert Oracle Database Architecture Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions[/quote]So, evidently, it's not just SQL Server people who have a "natural bias" against portability.[/quote]Can't disagree with that. Folks who have high expertise in particular database systems are bound to have that bias. Application designers on the other hand probably have a natural bias toward their application platforms and for whatever reason, a subset of them want to run their apps against different databases. For example, some parts like form interfaces are fairly generic across databases, whereas other parts of the application are probably more specific toward which database they hook up to. The more common case I see is that the vendors like portability, but their customers rarely move between databases. Where I work we're partial to one database and tend to shop for products that work on it.Also I see you put "natural bias" in quotes. Maybe the word "bias" itself has a bad connotation? How much do you like working with Oracle for instance, and if you don't, what would you like your preferences called? At least one other poster sounded like he felt I was trash talking directly at him. And I do realise that people here may not react positivelly about contrary opinions, and I'm willing to accept that I don't have to voice contrary viewpoints here, it wouldn't be a huge loss to me, as these discussions haven't ended up being that productive here anyways.[/quote]I have nothing against Oracle at all, as I've said, over & over again in this thread as well as elsewhere. Oracle is great. Just so happens, I don't work in it & it doesn't pay my bills. But, I'd be perfectly happy to work with it, DB2, MySQL, Hadoop, MongoDB or whatever, if that's where I needed to go to keep a roof over the family's heads. My issue isn't platform, it's design. And most people around here don't react badly to contrary opinions. Heck, that's what makes things fun. It's the generally ad hominem & dismissive manner in which they're delivered that's causing hackles to go up. Disagree without being disagreeable.I'm back to my point though. There is a very real, and evidently apparent in more than just the SQL sphere, understanding that you can design for performance on a platform, or you can design for portability between platforms, but you can't really design for both (without forking your code, readily acknowledged there, but that reinforces the point, not negates it).Thu, 21 Jun 2012 06:47:45 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Grant Fritchey (6/21/2012)[/b][hr]Just received an interesting comment on this over on my blog that included this:[quote]“By now, you might be able to see where I’m going in this section. I have made references above to other databases and how features are implemented differently in each. With the exception of some read-only applications, it is my contention that building a wholly database-independent application that is highly scalable is extremely hard — it is, in fact, quite impossible unless you know exactly how each database works in great detail. And, if you knew how each database worked in great detail, you’d understand that database independence is not something you really want to achieve (a very circular argument!).” Tom Kyte -Expert Oracle Database Architecture Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions[/quote]So, evidently, it's not just SQL Server people who have a "natural bias" against portability.[/quote]Can't disagree with that. Folks who have high expertise in particular database systems are bound to have that bias. Application designers on the other hand probably have a natural bias toward their application platforms and for whatever reason, a subset of them want to run their apps against different databases. For example, some parts like form interfaces are fairly generic across databases, whereas other parts of the application are probably more specific toward which database they hook up to. The more common case I see is that the vendors like portability, but their customers rarely move between databases. Where I work we're partial to one database and tend to shop for products that work on it.Also I see you put "natural bias" in quotes. Maybe the word "bias" itself has a bad connotation? How much do you like working with Oracle for instance, and if you don't, what would you like your preferences called? At least one other poster sounded like he felt I was trash talking directly at him. And I do realise that people here may not react positivelly about contrary opinions, and I'm willing to accept that I don't have to voice contrary viewpoints here, it wouldn't be a huge loss to me, as these discussions haven't ended up being that productive here anyways.Thu, 21 Jun 2012 06:27:12 GMTpatrickmcginnis59http://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxJust received an interesting comment on this over on my blog that included this:[quote]“By now, you might be able to see where I’m going in this section. I have made references above to other databases and how features are implemented differently in each. With the exception of some read-only applications, it is my contention that building a wholly database-independent application that is highly scalable is extremely hard — it is, in fact, quite impossible unless you know exactly how each database works in great detail. And, if you knew how each database worked in great detail, you’d understand that database independence is not something you really want to achieve (a very circular argument!).” Tom Kyte -Expert Oracle Database Architecture Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions[/quote]So, evidently, it's not just SQL Server people who have a "natural bias" against portability.Thu, 21 Jun 2012 04:13:31 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Jeff Moden (6/19/2012)[/b][hr][quote][b]patrickmcginnis59 (6/4/2012)[/b][hr][quote][b]GSquared (6/4/2012)[/b][hr]I've maintained for years that databases are platform-portable in the same way that continental plates are "portable". They do move, and it's usually listed as a disaster by everyone involved (I went through the Northridge earthquake in Los Angeles; wasn't fun).[/quote]I've concluded that most folks at a SQL Server specific forum are going to have a natural bias against portability in general. [/quote]Since I've worked with Oracle as well, I have to say that it's just a natural bias against not using all of the tools available in an RDBMS for the sake of something that rarely happens. For simple "CRUD" code... sure... be portable. For serious batch code, don't even think about portability because no matter what you do, it's not going to be 100% portable anyway.[/quote]I don't really have strong feelings either way, but I try to stay informed on whether SQL statements I use are specific to MSSQL and I also hope to be doing some Postgresql stuff in the future so I try to do some comparisons here and there to see what I'm in for. I also see that some vendors do want some independence from specific databases and I can understand why.Tue, 19 Jun 2012 20:45:38 GMTpatrickmcginnis59http://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]patrickmcginnis59 (6/4/2012)[/b][hr][quote][b]GSquared (6/4/2012)[/b][hr]I've maintained for years that databases are platform-portable in the same way that continental plates are "portable". They do move, and it's usually listed as a disaster by everyone involved (I went through the Northridge earthquake in Los Angeles; wasn't fun).[/quote]I've concluded that most folks at a SQL Server specific forum are going to have a natural bias against portability in general. [/quote]Since I've worked with Oracle as well, I have to say that it's just a natural bias against not using all of the tools available in an RDBMS for the sake of something that rarely happens. For simple "CRUD" code... sure... be portable. For serious batch code, don't even think about portability because no matter what you do, it's not going to be 100% portable anyway.Tue, 19 Jun 2012 18:15:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]patrickmcginnis59 (6/4/2012)[/b][hr][quote][b]GSquared (6/4/2012)[/b][hr]I've maintained for years that databases are platform-portable in the same way that continental plates are "portable". They do move, and it's usually listed as a disaster by everyone involved (I went through the Northridge earthquake in Los Angeles; wasn't fun).[/quote]I've concluded that most folks at a SQL Server specific forum are going to have a natural bias against portability in general. [/quote]Thank you for that. It's highly insulting.You don't know me. You know nothing about me. But you feel free to make that kind of statement about me?Feel free to rationalize your own biases by accusing others of ones they don't actually have. If that's what it takes to shore up your ego, that's what it takes.Mon, 04 Jun 2012 08:06:59 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]rob.lobbe-964963 (5/31/2012)[/b][hr][quote][b]Grant Fritchey (5/31/2012)[/b][hr][quote][b]Alex-668179 (5/31/2012)[/b][hr]"every table within SQL Server should have a clustered index"Fine, but should it then also always be the (preferably meaningless?) primary key? Or an index that is more likely to be used to retrieve ranges of records?[/quote]Ah, but there's the rub. Honest people disagree. But there's little disagreement that there are radical differences in the methods of storage between a clustered index and a heap. In the overriding majority of the time, use a clustered index. Also, I wouldn't focus on that "range of records" argument either. I'm not sure where that came from originally, but it's long been considered bunk. I usually focus on making my clustered index the most frequent access path to the data in a given table. That's frequently the primary key, but not always.[/quote]It's simple...the clustered index should be designed specifically to achieve - as close as possible - an [b]append[/b] pattern for all inserts. (think 'identity' column - or 'create_datetime') it doesn't need to relate to any other criteria PK or otherwise - but you'll usually have something. This way you are not trying to squeeze rows into the 'middle' of the table causing page splits etc. (all sorts of mischief to maintain the 'logical' order of rows)Where you may divert from this 'child' tables - where the 'child' rows are grouped by the 'parent' (it's an append pattern for the children - but you need to squeeze in the parent/child sets. (think Invoice/InvoiceRow - basically an append, but will need to squeeze in an InvoiceRow if you add one later) - this benefits retrieval as all the invoiceRows - for a given invoice - will be together (and in fewer pages to read)[/quote]That's true if your most expensive operations are inserts. Either most frequent or high expense per insert.If selects have you highest cost, then you want your clustered index to support them more effectively.Mon, 04 Jun 2012 08:02:47 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]GSquared (6/4/2012)[/b][hr]I've maintained for years that databases are platform-portable in the same way that continental plates are "portable". They do move, and it's usually listed as a disaster by everyone involved (I went through the Northridge earthquake in Los Angeles; wasn't fun).[/quote]I've concluded that most folks at a SQL Server specific forum are going to have a natural bias against portability in general. Mon, 04 Jun 2012 07:36:07 GMTpatrickmcginnis59http://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxI've maintained for years that databases are platform-portable in the same way that continental plates are "portable". They do move, and it's usually listed as a disaster by everyone involved (I went through the Northridge earthquake in Los Angeles; wasn't fun).Mon, 04 Jun 2012 07:25:59 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxWhile I can somewhat sympathize with the portability concern, I feel that it's a Cost/Benefit type of situation, and in many if not most cases, it will probably cost more performance wise to implement a solution from a generic perspective than it would cost to take full advanatage of the tools available on the implementation platform and then peform an unexpected migration to another platform years later. Besides, the cost of an actual migration is usually not cheap anyway so what's a few more hours/dollars when it comes to doing it right.The one thing that I despise about programming web based UI's is the number of different platform nuances that have to be accounted for.I'm certainly not going to use a screwdriver to drive a nail or a hammer to sink a screw, so why should I shortcut my code simply because it "might" be requried to run on another platform some day?Fri, 01 Jun 2012 12:09:33 GMTMad Hackerhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Grant Fritchey (5/31/2012)[/b][hr][quote][b]Alex-668179 (5/31/2012)[/b][hr]"every table within SQL Server should have a clustered index"Fine, but should it then also always be the (preferably meaningless?) primary key? Or an index that is more likely to be used to retrieve ranges of records?[/quote]Ah, but there's the rub. Honest people disagree. But there's little disagreement that there are radical differences in the methods of storage between a clustered index and a heap. In the overriding majority of the time, use a clustered index. Also, I wouldn't focus on that "range of records" argument either. I'm not sure where that came from originally, but it's long been considered bunk. I usually focus on making my clustered index the most frequent access path to the data in a given table. That's frequently the primary key, but not always.[/quote]It's simple...the clustered index should be designed specifically to achieve - as close as possible - an [b]append[/b] pattern for all inserts. (think 'identity' column - or 'create_datetime') it doesn't need to relate to any other criteria PK or otherwise - but you'll usually have something. This way you are not trying to squeeze rows into the 'middle' of the table causing page splits etc. (all sorts of mischief to maintain the 'logical' order of rows)Where you may divert from this 'child' tables - where the 'child' rows are grouped by the 'parent' (it's an append pattern for the children - but you need to squeeze in the parent/child sets. (think Invoice/InvoiceRow - basically an append, but will need to squeeze in an InvoiceRow if you add one later) - this benefits retrieval as all the invoiceRows - for a given invoice - will be together (and in fewer pages to read)Thu, 31 May 2012 16:49:09 GMTrob.lobbe-964963http://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Dean Cochrane (5/31/2012)[/b][hr][quote]...and many people who should know better treat the relational database as nothing more than a file system with built in backup capability.[/quote]This is, to my experience, more often the case than not. I've seen some amazing things in the wilds of vendor and even internal applications.Thu, 31 May 2012 12:31:27 GMTbenjamin.reyeshttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Paul Hunter (5/31/2012)[/b][hr]I hear the argument "we don't want to be locked in" from the app dev folks - the same ones that aren't afraid to be locked in to their programming language and/or OS. It's a "logic free" argument.[/quote]+1Thu, 31 May 2012 12:11:59 GMTPeter Maloofhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]patrickmcginnis59 (5/31/2012)[/b][hr][quote][b]Grant Fritchey (5/31/2012)[/b][hr][quote][b]patrickmcginnis59 (5/31/2012)[/b][hr][quote][b]Grant Fritchey (5/31/2012)[/b]I'm with you. Fast enough is fast enough.But, I remember when a friend of mine (wish I could say I did this) spotted a problematic query in one of the overnight load processes. He did some tweaks and something that took 6 hours was cut down to 3 minutes. It's the kind of win you dream about finding. And the solution was to take advantage of a particular set of functionality offered by SQL Server while the original load process had been written by an Oracle DBA (a very capable and good Oracle DBA, don't misunderstand my message) who used an approach common to both platforms. Tweaking to get a 5% improvement in speed.. Yeah, that's probably crazy. But that doesn't mean that any or all tweaks should be ignored.[/quote]So what takes 6 hours in Oracle that takes 3 minutes in SQL server? [/quote]Lordy no. Oh dear. I sure hope that was a joke.[/quote]Its not a joke. You're posting that he cut a query from 6 hours to 3 minutes based on a Microsoft feature. Now I can accept if theres an equivalent Oracle feature that would have also cut it down to 3 minutes, and can readily accept that there are material differences in implementations, or that possibly an Oracle implementer simply lacked training to implement on SQL server. What I'm curious on is the difference between 6 hours and 3 minutes and what stopped the Oracle guy from finding out himself how to cut the time?For example, did the Oracle guy believe the process would also take 6 hours on an Oracle server?[/quote]I'm sorry, again. I tried hard to be careful about the language.I'm not implying there was anything wrong with Oracle or the Oracle DBA. In fact, the Oracle DBA was a very capable person. Oracle is a very capable system.The problem was on SQL Server. SQL Server was running extremely slowly. SQL Server was processing in 6 hours. The process was written by someone who was an Oracle expert, but not a SQL Server expert. They made choices that worked in SQL Server, but they didn't work well in SQL Server. The fix was to change the query to use a function (and I don't recall exactly what it was, this was two years ago and I wasn't the hero of the story, so I just don't remember, sorry again) that worked better within SQL Server because it used functionality unique to SQL Server. It was not meant to imply a shortcoming in the Oracle person or Oracle. None of this was ever meant to question Oracle, Oracle DBAs, or the capabilities of either the Oracle software or the people who manage Oracle. But, just as Oracle has functions that make Oracle run faster, and those functions don't exist in SQL Server, there are similar things in SQL Server. That's all I was trying to point out. Instead, I've somehow stepped into "Oracle Sucks" and that was so very, very much not my intention. I'm sorry. I apologize.Thu, 31 May 2012 12:07:33 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]patrickmcginnis59 (5/31/2012)[/b][hr][quote][b]Grant Fritchey (5/31/2012)[/b]I'm with you. Fast enough is fast enough.But, I remember when a friend of mine (wish I could say I did this) spotted a problematic query in one of the overnight load processes. He did some tweaks and something that took 6 hours was cut down to 3 minutes. It's the kind of win you dream about finding. And the solution was to take advantage of a particular set of functionality offered by SQL Server while the original load process had been written by an Oracle DBA (a very capable and good Oracle DBA, don't misunderstand my message) who used an approach common to both platforms. Tweaking to get a 5% improvement in speed.. Yeah, that's probably crazy. But that doesn't mean that any or all tweaks should be ignored.[/quote]So what takes 6 hours in Oracle that takes 3 minutes in SQL server? [/quote]That isn't what he said. He said that a query written by an Oracley guy using Oracley syntax on a SQL Server box took 6 hours, which when rewritten by a guy savvy in SQL Servery syntax realized dramatic gains.Thu, 31 May 2012 11:59:04 GMTDean Cochranehttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Grant Fritchey (5/31/2012)[/b][hr][quote][b]patrickmcginnis59 (5/31/2012)[/b][hr][quote][b]Grant Fritchey (5/31/2012)[/b]I'm with you. Fast enough is fast enough.But, I remember when a friend of mine (wish I could say I did this) spotted a problematic query in one of the overnight load processes. He did some tweaks and something that took 6 hours was cut down to 3 minutes. It's the kind of win you dream about finding. And the solution was to take advantage of a particular set of functionality offered by SQL Server while the original load process had been written by an Oracle DBA (a very capable and good Oracle DBA, don't misunderstand my message) who used an approach common to both platforms. Tweaking to get a 5% improvement in speed.. Yeah, that's probably crazy. But that doesn't mean that any or all tweaks should be ignored.[/quote]So what takes 6 hours in Oracle that takes 3 minutes in SQL server? [/quote]Lordy no. Oh dear. I sure hope that was a joke.[/quote]Its not a joke. You're posting that he cut a query from 6 hours to 3 minutes based on a Microsoft feature. Now I can accept if theres an equivalent Oracle feature that would have also cut it down to 3 minutes, and can readily accept that there are material differences in implementations, or that possibly an Oracle implementer simply lacked training to implement on SQL server. What I'm curious on is the difference between 6 hours and 3 minutes and what stopped the Oracle guy from finding out himself how to cut the time?For example, did the Oracle guy believe the process would also take 6 hours on an Oracle server?Thu, 31 May 2012 11:52:50 GMTpatrickmcginnis59http://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]patrickmcginnis59 (5/31/2012)[/b][hr][quote][b]Grant Fritchey (5/31/2012)[/b]I'm with you. Fast enough is fast enough.But, I remember when a friend of mine (wish I could say I did this) spotted a problematic query in one of the overnight load processes. He did some tweaks and something that took 6 hours was cut down to 3 minutes. It's the kind of win you dream about finding. And the solution was to take advantage of a particular set of functionality offered by SQL Server while the original load process had been written by an Oracle DBA (a very capable and good Oracle DBA, don't misunderstand my message) who used an approach common to both platforms. Tweaking to get a 5% improvement in speed.. Yeah, that's probably crazy. But that doesn't mean that any or all tweaks should be ignored.[/quote]So what takes 6 hours in Oracle that takes 3 minutes in SQL server? [/quote]Lordy no. Oh dear. I sure hope that was a joke.Thu, 31 May 2012 11:34:12 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxAs Depeche Mode sang - everything counts in large amounts."So what takes 6 hours in Oracle that takes 3 minutes in SQL server? "I'd like to know too and I'd like to know what was done on the Oracle side (if still in place) to cure the six hour time of a task.Thu, 31 May 2012 11:18:42 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Grant Fritchey (5/31/2012)[/b]I'm with you. Fast enough is fast enough.But, I remember when a friend of mine (wish I could say I did this) spotted a problematic query in one of the overnight load processes. He did some tweaks and something that took 6 hours was cut down to 3 minutes. It's the kind of win you dream about finding. And the solution was to take advantage of a particular set of functionality offered by SQL Server while the original load process had been written by an Oracle DBA (a very capable and good Oracle DBA, don't misunderstand my message) who used an approach common to both platforms. Tweaking to get a 5% improvement in speed.. Yeah, that's probably crazy. But that doesn't mean that any or all tweaks should be ignored.[/quote]So what takes 6 hours in Oracle that takes 3 minutes in SQL server? Thu, 31 May 2012 11:16:23 GMTpatrickmcginnis59http://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Grant Fritchey (5/31/2012)[/b][hr][quote][b]donnapatriciakelly (5/31/2012)[/b][hr]Coming late to this, but a couple of thoughts, if I may?I work in the Business Intelligence area, and deal with all sorts of source systems. Getting data out in a reasonably performant way is important, as is processing the overnight loads (sure, there are many variants, but this is still the most common way to go).Mostly, I don't care about performance. I only care if (say) my overnight run time is a significant proportion (>20%?) of my available overnight window. By way of comparison, a District General's Hospital records take about 6-7 minutes to run, and a world-class supermarket's 100,000,000 transactions take about 5 hours against a 120 Terabyte warehouse. In the latter case, I keep an eye on things. In the former case, I don't. On the gripping hand, there are [i]extremely[/i] few applications in the extremely large class, but many bread-and-butter situations.What's important to me is that my SQL Server guy's time is not wasted pursuing performance. For bread-and-butter, that means keeping the focus on meeting business needs, not playing with performance tweaks. There is no cost-benefit return in making the 6 minute batch run twice as fast, so that the overnight window has improved from 6 hours 52 minutes to 6 hours 57 minutes.The focus needs to be on business benefit, not techie fun.That said, [i]reporting[/i] performance is critically important. But there's a different skill set required here. All reporting out of the RDBMS is overnight batch, and who cares how long it takes, as long as it is in the business users' inboxes before 7. Ad-hoc reporting is a different kettle of fish. That's done out of the data warehouse . . . which in the Microsoft world, means an Analysis Services hypercube. Of course, indexing is irrelevant to that.My central point is that worrying about common denominator code is perhaps not where the attention need be focused. If it's fast enough, that's good enough. The only cardinal sin regarding performance is not observing the rule [b]never keep the user waiting[/b]!Cheers, Donna[/quote] I'm with you. Fast enough is fast enough.But, I remember when a friend of mine (wish I could say I did this) spotted a problematic query in one of the overnight load processes. He did some tweaks and something that took 6 hours was cut down to 3 minutes. It's the kind of win you dream about finding. And the solution was to take advantage of a particular set of functionality offered by SQL Server while the original load process had been written by an Oracle DBA (a very capable and good Oracle DBA, don't misunderstand my message) who used an approach common to both platforms. Tweaking to get a 5% improvement in speed.. Yeah, that's probably crazy. But that doesn't mean that any or all tweaks should be ignored.[/quote]I will agree, chasing 5% improvements my be crazy, but it also has to be taken in perspective. A 5% improvement in a routine called a few times a day may not be that important, but a 5% improvement in performance of a routine called thousands of times an hour may be different.Thu, 31 May 2012 11:06:47 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]donnapatriciakelly (5/31/2012)[/b][hr]Coming late to this, but a couple of thoughts, if I may?I work in the Business Intelligence area, and deal with all sorts of source systems. Getting data out in a reasonably performant way is important, as is processing the overnight loads (sure, there are many variants, but this is still the most common way to go).Mostly, I don't care about performance. I only care if (say) my overnight run time is a significant proportion (>20%?) of my available overnight window. By way of comparison, a District General's Hospital records take about 6-7 minutes to run, and a world-class supermarket's 100,000,000 transactions take about 5 hours against a 120 Terabyte warehouse. In the latter case, I keep an eye on things. In the former case, I don't. On the gripping hand, there are [i]extremely[/i] few applications in the extremely large class, but many bread-and-butter situations.What's important to me is that my SQL Server guy's time is not wasted pursuing performance. For bread-and-butter, that means keeping the focus on meeting business needs, not playing with performance tweaks. There is no cost-benefit return in making the 6 minute batch run twice as fast, so that the overnight window has improved from 6 hours 52 minutes to 6 hours 57 minutes.The focus needs to be on business benefit, not techie fun.That said, [i]reporting[/i] performance is critically important. But there's a different skill set required here. All reporting out of the RDBMS is overnight batch, and who cares how long it takes, as long as it is in the business users' inboxes before 7. Ad-hoc reporting is a different kettle of fish. That's done out of the data warehouse . . . which in the Microsoft world, means an Analysis Services hypercube. Of course, indexing is irrelevant to that.My central point is that worrying about common denominator code is perhaps not where the attention need be focused. If it's fast enough, that's good enough. The only cardinal sin regarding performance is not observing the rule [b]never keep the user waiting[/b]!Cheers, Donna[/quote] I'm with you. Fast enough is fast enough.But, I remember when a friend of mine (wish I could say I did this) spotted a problematic query in one of the overnight load processes. He did some tweaks and something that took 6 hours was cut down to 3 minutes. It's the kind of win you dream about finding. And the solution was to take advantage of a particular set of functionality offered by SQL Server while the original load process had been written by an Oracle DBA (a very capable and good Oracle DBA, don't misunderstand my message) who used an approach common to both platforms. Tweaking to get a 5% improvement in speed.. Yeah, that's probably crazy. But that doesn't mean that any or all tweaks should be ignored.Thu, 31 May 2012 11:01:45 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]donnapatriciakelly (5/31/2012)[/b][hr]Coming late to this, but a couple of thoughts, if I may?I work in the Business Intelligence area, and deal with all sorts of source systems. Getting data out in a reasonably performant way is important, as is processing the overnight loads (sure, there are many variants, but this is still the most common way to go).Mostly, I don't care about performance. I only care if (say) my overnight run time is a significant proportion (>20%?) of my available overnight window. By way of comparison, a District General's Hospital records take about 6-7 minutes to run, and a world-class supermarket's 100,000,000 transactions take about 5 hours against a 120 Terabyte warehouse. In the latter case, I keep an eye on things. In the former case, I don't. On the gripping hand, there are [i]extremely[/i] few applications in the extremely large class, but many bread-and-butter situations.What's important to me is that my SQL Server guy's time is not wasted pursuing performance. For bread-and-butter, that means keeping the focus on meeting business needs, not playing with performance tweaks. There is no cost-benefit return in making the 6 minute batch run twice as fast, so that the overnight window has improved from 6 hours 52 minutes to 6 hours 57 minutes.The focus needs to be on business benefit, not techie fun.That said, [i]reporting[/i] performance is critically important. But there's a different skill set required here. All reporting out of the RDBMS is overnight batch, and who cares how long it takes, as long as it is in the business users' inboxes before 7. Ad-hoc reporting is a different kettle of fish. That's done out of the data warehouse . . . which in the Microsoft world, means an Analysis Services hypercube. Of course, indexing is irrelevant to that.My central point is that worrying about common denominator code is perhaps not where the attention need be focused. If it's fast enough, that's good enough. The only cardinal sin regarding performance is not observing the rule [b]never keep the user waiting[/b]!Cheers, Donna[/quote]I'm going to have to take a slight exception to this; If it's fast enough, that's good enough. Maybe it is fast enough in an environment with a few thousand rows, but what happens when the volumn increases from a few thousand to several hundred thousand rows? If this isn't taken into account during development, it may have to be taken care of later when the application isn't performing as it needs to.Thu, 31 May 2012 10:47:53 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxComing late to this, but a couple of thoughts, if I may?I work in the Business Intelligence area, and deal with all sorts of source systems. Getting data out in a reasonably performant way is important, as is processing the overnight loads (sure, there are many variants, but this is still the most common way to go).Mostly, I don't care about performance. I only care if (say) my overnight run time is a significant proportion (>20%?) of my available overnight window. By way of comparison, a District General's Hospital records take about 6-7 minutes to run, and a world-class supermarket's 100,000,000 transactions take about 5 hours against a 120 Terabyte warehouse. In the latter case, I keep an eye on things. In the former case, I don't. On the gripping hand, there are [i]extremely[/i] few applications in the extremely large class, but many bread-and-butter situations.What's important to me is that my SQL Server guy's time is not wasted pursuing performance. For bread-and-butter, that means keeping the focus on meeting business needs, not playing with performance tweaks. There is no cost-benefit return in making the 6 minute batch run twice as fast, so that the overnight window has improved from 6 hours 52 minutes to 6 hours 57 minutes.The focus needs to be on business benefit, not techie fun.That said, [i]reporting[/i] performance is critically important. But there's a different skill set required here. All reporting out of the RDBMS is overnight batch, and who cares how long it takes, as long as it is in the business users' inboxes before 7. Ad-hoc reporting is a different kettle of fish. That's done out of the data warehouse . . . which in the Microsoft world, means an Analysis Services hypercube. Of course, indexing is irrelevant to that.My central point is that worrying about common denominator code is perhaps not where the attention need be focused. If it's fast enough, that's good enough. The only cardinal sin regarding performance is not observing the rule [b]never keep the user waiting[/b]!Cheers, DonnaThu, 31 May 2012 10:30:41 GMTdonnapatriciakellyhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxCan someone, anyone, get the Microsoft SQL team to talk to the Microsoft Dynamics Great Plains team?Can you say database best practices?Thu, 31 May 2012 10:07:30 GMTMichael L Johnhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Steve Jones - SSC Editor (5/31/2012)[/b][hr][quote][b]jfogel (5/31/2012)[/b][hr] Our app did not try to use say ANSI only SQL, the "mode" is detected and either Oracle or SQL Server specific code is executed within executables. For reports we had two sets of procedures, one with Oracle syntax and one with SQL Server so we did not rely on one set of code to be all things to all people. Was that a lot of work? Yes but it avoided much of what is the root of this topic.[/quote]I wish JD Edwards had done this. They sent the same SQL through to all platforms (Oracle/SQL Server/DB2) and performance was poor all around.I think it would be easy to have separate DALs. Developers might not like it, but that's why you hire a separate DBA to write the code for each platform.[/quote]Or in my case some developers only want to know SQL Server and I get charged with translating everything to Oracle. It isn't all bad though because due to this I can port code very quickly and do it either way.Thu, 31 May 2012 10:03:17 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Your Name Here (5/31/2012)[/b][hr]. . . I haven't had a ton of experience with multi-platform apps ("Oracle, SQL, MySQL, our app will work on any platform") but the few I have worked with were pretty much epic fail. Yeah, "it worked" sort of like using a hammer "works" to put in screws.[/quote]It may (and should) work if you design a well thought-through abstract facade and each implementation inherits it, with code specific to the respective database.Grant, I read this editorial before my second coffee and I could not belived my eyes: "Grant? Saying this?", until I finally got it.Thanks for making my morning! :-)Thu, 31 May 2012 10:01:08 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxI guess I might take this issue a bit to heart. I've had what could amount to arguments with people who claim no app can support this or that DB and still be efficient. Even after explaining that the app reads parameters to know the back end and act accordingly and they still refuse to see the positives.Thu, 31 May 2012 09:59:07 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]jfogel (5/31/2012)[/b][hr] Our app did not try to use say ANSI only SQL, the "mode" is detected and either Oracle or SQL Server specific code is executed within executables. For reports we had two sets of procedures, one with Oracle syntax and one with SQL Server so we did not rely on one set of code to be all things to all people. Was that a lot of work? Yes but it avoided much of what is the root of this topic.[/quote]I wish JD Edwards had done this. They sent the same SQL through to all platforms (Oracle/SQL Server/DB2) and performance was poor all around.I think it would be easy to have separate DALs. Developers might not like it, but that's why you hire a separate DBA to write the code for each platform.Thu, 31 May 2012 09:58:32 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]jfogel (5/31/2012)[/b][hr][quote][b]patrickmcginnis59 (5/31/2012)[/b][hr][quote][b]Grant Fritchey (5/31/2012)[/b][hr][quote][b]TravisDBA (5/31/2012)[/b][hr][quote]Sorry about that. I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.[/quote]I'm a little confused too Grant . Who really uses "generic' database designs nowadays anyway? Particularly, when the underlying database structure is constantly changing in a fast paced environment. At least mine anyway. This seems to me like something that is already very apparent to most DBA's IMHO anyway. So, Oracle and SQL Server are fundamentally different, Uhhhhh... yes, they are....no kidding! I'm with Chris, I'm not sure what point the message was trying to convey, that we didn't already know.:-D[/quote]The message may have been unclear, again apologies, but you're wrong that this is common knowledge. Tons and tons of developers and plenty of DBAs still believe in not hooking yourself to a single vendor. I interviewed someone just a little over a year ago who lectured me on my technical questions because they were too "Microsoft & SQL Server specific." His argument was that only lowest common denominator SQL should be used so that it's easier to port between database systems. People really do still believe this.[/quote]I'm one of those I guess, I spend a bit of effort comparing different server features just to stay in tune with how things are handled between different systems. I've dealt with a few systems that also try for a measure of independence from database vendors even to the extent of having in house programming languages, so theres clearly some pretty smart folks out there who believe there are reasons for doing so which you dismissed without comment. Its an interesting topic that maybe deserves a better writeup then you are able to deliver. Oh well, this is a pretty vendor specific forum so I guess it is to be expected.[/quote]True. We sell our software and not a particular vendors database. Our app had to play nice with Oracle or SQL to give the buyer a choice. At the time Oracle was the major player and SQL Server was at 6.5 and not really trusted. Then things changed and the majority of clients chose the SQL Server back end mainly because of price. Have a clustered or heap index, our app doesn't care as long as the index exists and if it does not it will create it using vendor specific syntax. Our app did not try to use say ANSI only SQL, the "mode" is detected and either Oracle or SQL Server specific code is executed within executables. For reports we had two sets of procedures, one with Oracle syntax and one with SQL Server so we did not rely on one set of code to be all things to all people. Was that a lot of work? Yes but it avoided much of what is the root of this topic.[/quote]But that would be the correct approach. Your code does different things in different environments rather than trying to do one thing in all environments. It makes perfect sense. I'm not anti-PLATFORMX. After years & years in this industry, I've just found that programming to the platform, whatever platform, works and programming to some generic & somewhat amorphous standard doesn't.Heck, it's even the issue I've seen with ORM tools. Many of the failed implementations of ORM have attempted to force objects into the database rather than focus on that "M" part of ORM. It's programming to a standard, not to the platform. And the funny thing is, the ORM tools I've worked with are perfectly capable of working with whatever platform we're talking about. But the problem is knowledge and approach, not software, tools or platform.Thu, 31 May 2012 09:55:44 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]Chris.C-977504 (5/31/2012)[/b][hr][quote][b]Grant Fritchey (5/31/2012)[/b][hr]...I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.[/quote]Well you didn't [i]completely[/i] fail because that was my best guess. (I reread the article but still wasn't sure - hence my post)It is a good point about the foundation of each.I hadn't thought about the foundation of SQL Server being clustered vs. the foundation of Oracle being the heap. This is an interesting discussion (though the topic has digressed). I learned on Oracle in `92 while still in college, and I have used it often since then, but I haven't done a db design in Oracle... uh, this century?So, my follow up question is:Can't you still write specific optimized code for both Oracle and SSvr in one program if you have two different DALs with different schemas underneath?Yes, you'd need a forked code base for the DALs, but if you're dealing with 'big data' ("big enough data"?) and tight performance constraints then it could be worth the headaches and (huge) costs.Maybe that's a dumb question, forgive me as I'm a applications developer (by job category) not a a DBA - though I do all the SSvr db design and query optimizing for my area :-)I do now agree with your premise which I think I can over simplify as "Don't gold plate your code" (the gold plating in this case being the fiction of the generic backend database)Thanks, HTH, -Chris C.edited to add "in one program" to the DAL question[/quote]No such thing as a dumb question. Yeah, if you need performance, you're going to have to fork the code base in some manner. You can get acceptable performance using lowest common denominator approaches, absolutely. But to make code hum, in any platform, you'll need to design for that platform.Thu, 31 May 2012 09:48:51 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxHey, Grant!Excellent topic. I get the jab at "one size fits all just doesn't work". I haven't had a ton of experience with multi-platform apps ("Oracle, SQL, MySQL, our app will work on any platform") but the few I have worked with were pretty much epic fail. Yeah, "it worked" sort of like using a hammer "works" to put in screws.Thu, 31 May 2012 09:45:09 GMTYour Name Herehttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]LadyRuna (5/31/2012)[/b][hr]I believe the rule regarding indexes (clustered or not) is the infamous "It Depends".... because your choice of indexing style really does depend upon how you're planning to access your data and what you're going to do with it. It always boggles my mind when someone calls a meeting and starts by saying, "I think we should set up a rule that we always use ___________ in all of our stored procedures/table designs/ etc". Usually this is because the guy just read an article that made some sweeping generalization and he decided that that should be enforced throughout our code. As soon as I hear that I think "DANGER!!" because I know that there are exceptions where that rule should [b]not [/b]be applied. [/quote]I absolutely agree, people have to be careful using wide sweeping generalizing terms like "widespread". Even me.:-DThu, 31 May 2012 09:43:27 GMTTravisDBAhttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspxI believe the rule regarding indexes (clustered or not) is the infamous "It Depends".... because your choice of indexing style really does depend upon how you're planning to access your data and what you're going to do with it. It always boggles my mind when someone calls a meeting and starts by saying, "I think we should set up a rule that we always use ___________ in all of our stored procedures/table designs/ etc". Usually this is because the guy just read an article that made some sweeping generalization and he decided that that should be enforced throughout our code. As soon as I hear that I think "DANGER!!" because I know that there are exceptions where that rule should [b]not [/b]be applied. Thu, 31 May 2012 09:28:24 GMTLadyRunahttp://www.sqlservercentral.com/Forums/Topic1308736-263-1.aspx[quote][b]patrickmcginnis59 (5/31/2012)[/b][hr][quote][b]Grant Fritchey (5/31/2012)[/b][hr][quote][b]TravisDBA (5/31/2012)[/b][hr][quote]Sorry about that. I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.[/quote]I'm a little confused too Grant . Who really uses "generic' database designs nowadays anyway? Particularly, when the underlying database structure is constantly changing in a fast paced environment. At least mine anyway. This seems to me like something that is already very apparent to most DBA's IMHO anyway. So, Oracle and SQL Server are fundamentally different, Uhhhhh... yes, they are....no kidding! I'm with Chris, I'm not sure what point the message was trying to convey, that we didn't already know.:-D[/quote]The message may have been unclear, again apologies, but you're wrong that this is common knowledge. Tons and tons of developers and plenty of DBAs still believe in not hooking yourself to a single vendor. I interviewed someone just a little over a year ago who lectured me on my technical questions because they were too "Microsoft & SQL Server specific." His argument was that only lowest common denominator SQL should be used so that it's easier to port between database systems. People really do still believe this.[/quote]I'm one of those I guess, I spend a bit of effort comparing different server features just to stay in tune with how things are handled between different systems. I've dealt with a few systems that also try for a measure of independence from database vendors even to the extent of having in house programming languages, so theres clearly some pretty smart folks out there who believe there are reasons for doing so which you dismissed without comment. Its an interesting topic that maybe deserves a better writeup then you are able to deliver. Oh well, this is a pretty vendor specific forum so I guess it is to be expected.[/quote]True. We sell our software and not a particular vendors database. Our app had to play nice with Oracle or SQL to give the buyer a choice. At the time Oracle was the major player and SQL Server was at 6.5 and not really trusted. Then things changed and the majority of clients chose the SQL Server back end mainly because of price. Have a clustered or heap index, our app doesn't care as long as the index exists and if it does not it will create it using vendor specific syntax. Our app did not try to use say ANSI only SQL, the "mode" is detected and either Oracle or SQL Server specific code is executed within executables. For reports we had two sets of procedures, one with Oracle syntax and one with SQL Server so we did not rely on one set of code to be all things to all people. Was that a lot of work? Yes but it avoided much of what is the root of this topic.Thu, 31 May 2012 09:15:39 GMTjfogel