SQLServerCentral / Editorials / SQLServerCentral.com / Surely It Should Just Work? / Latest Posts

RE: Surely It Should Just Work?

TheSQLGuru — Thu, 19 Nov 2009 11:22:24 GMT

[quote][b]bpatin (11/19/2009)[/b][hr]I've only worked on my own functions, and have looked at execution plans to compare the with and without. I have created some queries, without using functions, that had to incorporate some very complex multiple CASE statements that made the query very hard to understand and maintain. A simple function greatly simplified the query, and the execution plans and actual use showed no difference in the performance.So, I assume performance is highly dependent on the function and how it is used.[/quote]Yes, performance it is very dependent on what the function does. The main disasterous-performance-causing issue (although there are others) is when a UDF does ancillary data access. Using such function in a query almost invariably leads to row-by-row processing of said query. Another is joining to UDFs which give the optimizer an estimated rowcount of 1, leading the optimizer to pick nested loop plans even though you might hit 100M rows in other tables - pack a lunch.

RE: Surely It Should Just Work?

bpatin — Thu, 19 Nov 2009 11:15:09 GMT

I've only worked on my own functions, and have looked at execution plans to compare the with and without. I have created some queries, without using functions, that had to incorporate some very complex multiple CASE statements that made the query very hard to understand and maintain. A simple function greatly simplified the query, and the execution plans and actual use showed no difference in the performance.So, I assume performance is highly dependent on the function and how it is used.

RE: Surely It Should Just Work?

TheSQLGuru — Thu, 19 Nov 2009 09:41:21 GMT

RE: Surely It Should Just Work?

GSquared — Thu, 19 Nov 2009 09:25:26 GMT

[quote][b]bpatin (11/19/2009)[/b][hr]I've seen the T-SQL for a maintenance plan (view only) with a disclaimer that it may not be exactly what the server will be executing, but see no way to use T-SQL to actually create a maintenance plan that can afterwards be edited with the GUI in SSMS. I could put the T-SQL in a file somewhere and schedule a job to execute it - is that what you mean?[/quote]I prefer to bypass the GUI version altogether. I have a database called DBA that has the tables and procs that I use for my maintenance.There's a daily proc that runs integrity checks, full backups, index maintenance (based on fragmentation and usage patterns), maintains a set of batch and proc statement traces, reports on databases that are getting close to having to grow, and on objects in databases that are growing at unexpected rates, and then sends me an e-mail summary of both what was done, and things that may need my intervention.I'm working on an hourly proc that will monitor for the need for log backups and a few things like that. It's not done yet though.

RE: Surely It Should Just Work?

bpatin — Thu, 19 Nov 2009 09:20:08 GMT

I've seen the T-SQL for a maintenance plan (view only) with a disclaimer that it may not be exactly what the server will be executing, but see no way to use T-SQL to actually create a maintenance plan that can afterwards be edited with the GUI in SSMS. I could put the T-SQL in a file somewhere and schedule a job to execute it - is that what you mean?

RE: Surely It Should Just Work?

stevet-1034619 — Wed, 18 Nov 2009 16:02:29 GMT

Thanks for the clarification Phil...

RE: Surely It Should Just Work?

Phil Factor — Wed, 18 Nov 2009 15:37:31 GMT

You are entitled to use PostgreSQL for free. It is released under a BSD-style license, complying with both the Free Software Definition and the Open Source Definition. Itconforms to both the Debian Free Software Guidelines and the Copyfree Standard. There are no restrictions on using it. Realistically the only problem with MySQL comes if you need to release software incorporating MySQL code without having to release the source code for the entire application.This would require paying MySQL AB for a MySQL Commercial License.PostgreSQL is a true open-source project, owned by its community, whereas MySQL is owned by a commercial company for profit. It is an Open source product, but its' development is dictated by the company's owners. This is a subtle difference, but it manifests itself in the way that the two database systems are developed and maintained, and the future direction of the two databases. Given the doubts over future strategy over the product now that Oracle has acquired MySQL, my advice would be to use PostgeSQL if you require this sort of relational database. Its future development is guaranteed because of the widespread community that is looking after it's development. However, given the liberal licencing with SQL Server Express, I'd have to have some pretty compelling reasons to use 'open source' instead. However, as a development resource, PostgreSQL is very handy; particularly as a 'poor man's Oracle'!

RE: Surely It Should Just Work?

stevet-1034619 — Wed, 18 Nov 2009 14:49:32 GMT

[quote][b]bpatin (11/18/2009)[/b][hr]I meant PostgreSQL over MySQL, not SQL Server (which is really just an old fork of Sybase).MySQL is not totally free for commercial purposes, and the recent purchase by Oracle has further complicated matters. PostgreSQL does not have these problems.[/quote]I don't really agree...how do you mean MySQL is not free for commercial purposes? If I use the community version on a production ecommerce site, what limits are there?Sorry, I know i'm kinda swerving off topic, but I'd like to get clarification on the argument that bpatin is putting forth.

RE: Surely It Should Just Work?

GSquared — Wed, 18 Nov 2009 12:30:40 GMT

[quote][b]TheSQLGuru (11/18/2009)[/b][hr][quote]I especially dislike having to use a GUI to tediously redo by hand things such as maintenance plans that I might quickly do in an open-source platform with a text editor when migrating from one version or manufacturer to another.[/quote]This also shows decided lack of knowledge about the product. EVERYTHING that is done via a GUI in SSMS (or other product) ultimately hits the server as a collection of tsql calls. You absolutely can (and SHOULD) roll your own maintenance activities.Best of luck with your life in the open source world![/quote]I was thinking the same thing. You can build maintenance plans (or most anything else) in SQL Server in a text editor. I do it all the time.

RE: Surely It Should Just Work?

TheSQLGuru — Wed, 18 Nov 2009 12:29:09 GMT

[quote]I especially dislike having to use a GUI to tediously redo by hand things such as maintenance plans that I might quickly do in an open-source platform with a text editor when migrating from one version or manufacturer to another.[/quote]This also shows decided lack of knowledge about the product. EVERYTHING that is done via a GUI in SSMS (or other product) ultimately hits the server as a collection of tsql calls. You absolutely can (and SHOULD) roll your own maintenance activities.Best of luck with your life in the open source world!

RE: Surely It Should Just Work?

TheSQLGuru — Wed, 18 Nov 2009 12:17:34 GMT

[quote]One thing I have discovered is that functions can be very useful in simplifying queries, and equivalent functions can be written, with some accounting for language differences, for both SQL Server and PostgreSQL. I've noticed that a query written with a simplifying function executes the same way in SQL Server as a query without the simplifying function, but have not tested that behavior in PostgreSQL.[/quote]Dude (or Dudette), that is SOOOO far from the reality, at least from what I have been paid to do for a good bit of the last 4 years or so. I have had to clean up significant UDF-related performance problems at at least a half-dozen clients. They can produce unbelievably horrid performance!!!!

RE: Surely It Should Just Work?

Elliott Whitlow — Wed, 18 Nov 2009 09:48:39 GMT

[quote][b]bpatin (11/18/2009)[/b][hr]I meant PostgreSQL over MySQL, not SQL Server (which is really just an old fork of Sybase).[/quote]I agree this WAS true, up to SQL 6.5, but at 7 and 2000 I don't think much Sybase code survived..CEWII

RE: Surely It Should Just Work?

Phil Factor — Wed, 18 Nov 2009 08:49:35 GMT

[quote](which is really just an old fork of Sybase).[/quote]Ouch. I suspect that Microsoft would wince at that. People have, in the past, called me an 'old fork': once.:-)

RE: Surely It Should Just Work?

bpatin — Wed, 18 Nov 2009 08:42:45 GMT

I meant PostgreSQL over MySQL, not SQL Server (which is really just an old fork of Sybase).MySQL is not totally free for commercial purposes, and the recent purchase by Oracle has further complicated matters. PostgreSQL does not have these problems.

RE: Surely It Should Just Work?

bpatin — Wed, 18 Nov 2009 08:30:52 GMT

OK, I admit that I'm not a fan of proprietary software that limits me, costs more than I could afford on my own, and for which I have trouble getting support when I need it. I especially dislike having to use a GUI to tediously redo by hand things such as maintenance plans that I might quickly do in an open-source platform with a text editor when migrating from one version or manufacturer to another.I can see you question the support issue. There are these problems:1. With open-source, I can often figure out myself what the problem is by going through their code.And, even if I then need assistance fixing the issue, I understand it better, can explain the problem better, and am less frustrated.2. With Microsoft and some other big companies, I usually have to go through first-level support persons who, once they have given up finding a previously canned solution, have to tediously reproduce the problem and submit it to the next level support who know enough to be able to submit it further to developers who can actually fix the problem. After hours, then weeks or even months of this nonsense in some cases, I usually give up and work around the problem with ugly bandaids, or tell my users to live with it, or convince management to drop the product entirely and go to a competitor.3. Microsoft licensing is a bear, especially when I have to purchase the product through a large organization using volume licensing and special deals to save money. I have an MSDN subscription with four support incidents, but have been unable to use them after spending hours on the phone with Microsoft support getting authorization codes that ultimately don't work.4. To circumvent the above problems, I have resorted to posting problems in forums, hopefully to get directly in touch with product developers who might actually work on it. This is closer to the way open-source works, except that with open-source I can actually submit a formal bug fix request for an open-source program and eventually expect a reply. There is no way to submit a bug to Microsoft, or even just inform the ones who need to know about it, without paying for a support incident and wasting a lot of my time. I remember one problem in which there were literally over a million posts in a forum on an issue I was having, and no Microsoft fix was forthcoming. I have had other issues that went on for ten years and several versions with no resolution, until the feature became obsolete.Need I say more?Open-source isn't always perfect or always as well integrated and polished as some proprietary products, but I can more easily live with the openness and hope it provides than the frustrating, dark hole that proprietary products often put me in.

RE: Surely It Should Just Work?

bpatin — Wed, 18 Nov 2009 07:58:47 GMT

Thanks for your reply. At least I now know what to keep in mind when I'm in similar situations.You mentioned arguments over normalization with your devs, but didn't say what side you were on. I develop both applications and the databases they use, and have had to get fellow developers to see the benefits of and understand how to work with joins and normalized data. Although I usually push normalization, sometimes, when practical performance issues arise, I have had to back off and deal with partially denormalized schemas. One thing I have discovered is that functions can be very useful in simplifying queries, and equivalent functions can be written, with some accounting for language differences, for both SQL Server and PostgreSQL. I've noticed that a query written with a simplifying function executes the same way in SQL Server as a query without the simplifying function, but have not tested that behavior in PostgreSQL.

RE: Surely It Should Just Work?

AndrewJacksonZA — Wed, 18 Nov 2009 00:29:44 GMT

[quote]...but one can harbour a sneaking sympathy for the harassed application developer who has, perforce, become an 'Accidental DBA'...[/quote]Thank you for that sympathy! :-) In a similar manner I would have sympathy on a DBA who had to start programming.

RE: Surely It Should Just Work?

Phil Factor — Tue, 17 Nov 2009 12:48:28 GMT

[quote]...I really want to know more details about such a shocking statement as you have made. Please elaborate.[/quote]I rate PostgreSQL quite highly, and my friends who use it say it is greatly improved now. I don't think that it is a shocking statement to say that a simple four-way join took ten times longer, though. It just underlines how careful one has to be with porting a database from one platform to another. Until recently I had the offending execution plan, but I can't put my hands on it. I was writing the database for a J2E/postreSQL application. Just because the development tools were better in SQL Server, I wrote the first cut in SQL Server, and stocked it with realistic data. I ran test timings on all the SQL Calls. I then ported the database to PostgreSQL (it was surprisingly simple) using the EMS PostgreSQL tools. I then ran the same timings. The PostgreSQL database was hosted on Linux (I also tried the Windows version with similar results) on hardware that was pretty-well identical to the SQL Server version. The purpose of the timings was to check that the indexing I'd used for SQL Server was appropriate for PostgreSQL, and to flag up any problems. The problem with the four-way joins was somewhat embarrassing as I'd had an argument with the Devs over denormalization. I'd demonstrated, using SQL Server, that there was no measurable difference on SQL Server between a fully-normalized NAD subsystem that involved a four-way join and a denormalized two-way join. I was not happy to find out that the same wasn't true with PostgreSQL. I put the offending query and plan on some PostgreSQL sites and asked PostgreSQL experts, and did everything they suggested, but I never got the speed up to SQL Server. If I remember right, the problem was due to the postgreSQL indexes not 'covering' the query in the same way as SQL Server.PostgreSQL deserves our support as being the only free open source relational database that is community-owned and published under a BSD Licence. I'm happy to recommend it where the use of SQL Server is not appropriate for some reason, but it is unfair on PostgreSQL to compare it directly with an industrial-strength commercial database such a SQL Server; it is hardly surprising to find that there are occasions where it doesn't match up. It has improved enormously in the time since I used it.

RE: Surely It Should Just Work?

stevet-1034619 — Tue, 17 Nov 2009 12:41:50 GMT

[quote][b]bpatin (11/17/2009)[/b][hr]have been recommending PostgreSQL over MySQL[/quote]bpatin: did you mean PostgreSQL over SQL? I agree PostgreSQL has more mature capbilities than MySQL, but licensing issues?As far as this topic goes, I think the onus is on the developer to understand the difference in database applications and platforms to achieve the best performance in his/her application. It is the developer's responsibility to understand the technology they are manipulating. Luckily the basic principles of databases apply to most and they are not going to have to start from scratch, but they will need to find out the differences and learn how to best utilize the technology at hand.just my .02

RE: Surely It Should Just Work?

TheSQLGuru — Tue, 17 Nov 2009 12:35:15 GMT

[quote][b]bpatin (11/17/2009)[/b][hr]Whatever the main premise of your article, you mentioned a "very simple four-way join" that "took ten times as long" in PostgreSQL as it did in SQL Server. I have been trying to get my Microsoft-worshipping supervisor to more seriously consider open source, and have been recommending PostgreSQL over MySQL based on its more mature capabilities and fewer potential licensing problems. So you can understand that I really want to know more details about such a shocking statement as you have made. Please elaborate.[/quote]I would like to hear you elaborate on why you are pushing those other RDBMS platforms...

RE: Surely It Should Just Work?

bpatin — Tue, 17 Nov 2009 12:01:25 GMT

Whatever the main premise of your article, you mentioned a "very simple four-way join" that "took ten times as long" in PostgreSQL as it did in SQL Server. I have been trying to get my Microsoft-worshipping supervisor to more seriously consider open source, and have been recommending PostgreSQL over MySQL based on its more mature capabilities and fewer potential licensing problems. So you can understand that I really want to know more details about such a shocking statement as you have made. Please elaborate.

RE: Surely It Should Just Work?

PocketJoshua — Tue, 17 Nov 2009 08:05:32 GMT

It is fair to say who wouldn’t want it “To Just Work”. But at what cost? If all products just worked what would be the differentiators between one product and another. There are good reasons to work towards standards so there is a degree of interoperability but in reality how good is that interoperability. What standards and commonality does do is gives the developers a common ground to communicate and learn. Products should continue to evolve and “Just work” better. But not at the cost of providing real improvements and benefits to functionality and performance. Many years ago I worked on a project that used the Java Message Queue stuff. Functionally it just worked. But the reference implementation was not robust enough for an enterprise environment. In comes a 3rd party product that cost $6K per processor but had a ridiculous performance and scalability compared to the reference implementation. Yes to Phil’s point it did just work, which is good, but the different implementations had different optimizations. If they were exactly the same where is the differentiator that make one more valuable than the other. If they are different then they will have different ways to use them that can impact the developer.

RE: Surely It Should Just Work?

TheSQLGuru — Tue, 17 Nov 2009 08:00:51 GMT

[quote][b]Steve Jones - Editor (11/17/2009)[/b][hr][quote][b]GSquared (11/16/2009)[/b][hr]She has a Swiss Army knife, I have a locking-blade pocket knife. [/quote]I think it's cool both you and your wife have knives :)[/quote]And clearly his wife has the smarts in the family! Swiss Army Knives can be AMAZINGLY useful tools! I have been carrying one (except on commercial flights - dammit!!) for almost 30 years. Being able to travel with my knife is one of the hidden benefits to having my own plane! :cool:As for the article and thought that databases should just work, I say poppy-cock, and not just because the original premise comes from Joe Celko. ANY reasonably complex system where you want/need optimal performance or to be able to capture every little nook-and-cranny feature REQUIRES one (or at least part of the group) to be WELL VERSED in the internals of said system.

RE: Surely It Should Just Work?

GSquared — Tue, 17 Nov 2009 06:53:19 GMT

[quote][b]Steve Jones - Editor (11/17/2009)[/b][hr][quote][b]GSquared (11/16/2009)[/b][hr]She has a Swiss Army knife, I have a locking-blade pocket knife. [/quote]I think it's cool both you and your wife have knives :)[/quote]It's mankind's oldest invention (as opposed to the rock or club, which is the oldest discovery), and still useful on a day-to-day basis. I don't understand why anyone would [i]not[/i] carry one.

RE: Surely It Should Just Work?

Steve Jones - SSC Editor — Tue, 17 Nov 2009 06:50:21 GMT

[quote][b]GSquared (11/16/2009)[/b][hr]She has a Swiss Army knife, I have a locking-blade pocket knife. [/quote]I think it's cool both you and your wife have knives :)

RE: Surely It Should Just Work?

jay-h — Tue, 17 Nov 2009 06:43:36 GMT

I don't know how many times I've seen people use functions (especially user-defined functions) as if they were overhead free. After all, the form "function(parameter)" looks so simple, but could easily be hiding enormous amounts of machine time (and calling other UDFs that hide more machine time). Unless users really think about what they are doing in terms of cpu and disk access, it's easy to be lulled into poor design.Even with sophisticated optimizers, user constructs can (if the user does not understand them) prevent the use of optimization.[at the extreme, we might consider the mathematician's "function" which is often an abstract to clean up a proof. The fact that such a 'function' could take longer than the age of the universe to execute is immaterial the mathematicians.]

RE: Surely It Should Just Work?

Elliott Whitlow — Mon, 16 Nov 2009 14:02:56 GMT

The part I like the best is that it paints a VERY clear picture and the comparison is VERY apples to apples.CEWII

RE: Surely It Should Just Work?

GSquared — Mon, 16 Nov 2009 13:59:06 GMT

[quote][b]Elliott W (11/16/2009)[/b][hr]That was an amazing analogy G.. It is just perfect..CEWII[/quote]Thanks.I view things from an evolutionary viewpoint, and that includes tools and ideas.

RE: Surely It Should Just Work?

Elliott Whitlow — Mon, 16 Nov 2009 13:51:02 GMT

That was an amazing analogy G.. It is just perfect..CEWII

RE: Surely It Should Just Work?

GSquared — Mon, 16 Nov 2009 12:56:32 GMT

Of course it "should just work", but so should everything else.My car has an automatic transmission. My wife's has a manual. If I try to drive hers the way I drive mine, I'll destroy the engine by staying in first gear the whole trip. If I try to drive mine the way I drive hers, I'll be pounding on the left side of the brake pedal and messing stuff up every time I try to change gears and there isn't a clutch pedal there.She has a Swiss Army knife, I have a locking-blade pocket knife. Again, try to use them the same way, and I'm liable to lose fingers (hers doesn't lock), or break the knife (mine doesn't have a bottle opener or screwdriver on it).Shouldn't those things "just work"? Yep. But they don't. You have to use a tool the way it's designed. Some are going to be optimized for one thing, some for others.I can do things with my knife, because of the locking blade, that wouldn't be safe with hers. I can do things with hers that wouldn't be possible with mine.Surely pocket knives, perhaps the most ancient piece of technology on the planet, should be similar enough that specialized expertise with one would translate into specialized expertise with the other. Or they should "just work" well enough that one doesn't need specialized expertise with any knife. If you think that, then try doing with a fileting knife what you would do with a chisel, or vice versa.So, why should software, which is just another tool, have some special "just workness" to it, that no other technology on this planet has? Specialization by tools and knowledge has always been humanity's greatest evolutionary advantage. Why would that have changed in the last 50 years? Different tools should have different strengths and weaknesses. Over time, these will evolve into the kind of true specializations that differentiate a jewler's mallet from a hydraulic press (both are hammers).Sure, the differences now aren't that well-defined and don't have that kind of special utility, in some cases. But we are seeing some of it, a la, SQL Mobile vs SQL Enterprise. It just takes some time to really get there. Wheelbarrows vs 18-wheelers didn't happen in a decade or five.

Surely It Should Just Work?

Phil Factor — Mon, 16 Nov 2009 12:20:05 GMT

Comments posted to this topic are about the item [B]Surely It Should Just Work?[/B]