November 18, 2009 at 7:58 am
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.
November 18, 2009 at 8:30 am
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.
November 18, 2009 at 8:42 am
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.
November 18, 2009 at 8:49 am
(which is really just an old fork of Sybase).
Ouch. I suspect that Microsoft would wince at that. People have, in the past, called me an 'old fork': once.:-)
Best wishes,
Phil Factor
November 18, 2009 at 9:48 am
bpatin (11/18/2009)
I meant PostgreSQL over MySQL, not SQL Server (which is really just an old fork of Sybase).
I agree this WAS true, up to SQL 6.5, but at 7 and 2000 I don't think much Sybase code survived..
CEWII
November 18, 2009 at 12:17 pm
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.
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!!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 18, 2009 at 12:29 pm
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.
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!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 18, 2009 at 12:30 pm
TheSQLGuru (11/18/2009)
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.
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!
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 18, 2009 at 2:49 pm
bpatin (11/18/2009)
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.
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.
November 18, 2009 at 3:37 pm
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'!
Best wishes,
Phil Factor
November 18, 2009 at 4:02 pm
Thanks for the clarification Phil...
November 19, 2009 at 9:20 am
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?
November 19, 2009 at 9:25 am
bpatin (11/19/2009)
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?
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2009 at 9:41 am
bpatin (11/19/2009)
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?
To my knowledge this isn't a bi-directional GUI/script thing. Just avoid maintenance plans altogether . . . there are some AMAZING scripts online. Check out the one by Ola Hallengren.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 19, 2009 at 11:15 am
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.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply