Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Surely It Should Just Work? Expand / Collapse
Author
Message
Posted Tuesday, November 17, 2009 12:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 12:17 PM
Points: 10, Visits: 29
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.
Post #820294
Posted Tuesday, November 17, 2009 12:35 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 4,406, Visits: 6,270
bpatin (11/17/2009)
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.


I would like to hear you elaborate on why you are pushing those other RDBMS platforms...


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #820318
Posted Tuesday, November 17, 2009 12:41 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:24 PM
Points: 41, Visits: 164
bpatin (11/17/2009)
have been recommending PostgreSQL over MySQL


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
Post #820326
Posted Tuesday, November 17, 2009 12:48 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 588, Visits: 2,555
...I really want to know more details about such a shocking statement as you have made. Please elaborate.


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.



Best wishes,

Phil Factor
Simple Talk
Post #820333
Posted Wednesday, November 18, 2009 12:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:35 AM
Points: 166, Visits: 1,056
...but one can harbour a sneaking sympathy for the harassed application developer who has, perforce, become an 'Accidental DBA'...

Thank you for that sympathy! In a similar manner I would have sympathy on a DBA who had to start programming.
Post #820607
Posted Wednesday, November 18, 2009 7:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 12:17 PM
Points: 10, Visits: 29
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.
Post #820852
Posted Wednesday, November 18, 2009 8:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 12:17 PM
Points: 10, Visits: 29
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.

Post #820888
Posted Wednesday, November 18, 2009 8:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 12:17 PM
Points: 10, Visits: 29
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.
Post #820900
Posted Wednesday, November 18, 2009 8:49 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 588, Visits: 2,555
(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
Simple Talk
Post #820909
Posted Wednesday, November 18, 2009 9:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #820964
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse