On Fri, Jan 23, 2009 at 12:36 PM, Chris Wareham <cware...@visitlondon.com> wrote: > Leon Rosenberg wrote: >> >> On Fri, Jan 23, 2009 at 11:45 AM, Chris Wareham >> <cware...@visitlondon.com> wrote: >> >>> By it's very definition (see Codd or Date), an RDBMS should be capable >>> of performing joins with good performance. MySQL often struggles to do >>> so thanks to the poor optimiser, so you had to implement what should be >>> core functionality of an RDBMS in your application layer. >> >> Sorry, but by the very definition JOINs are slow, and no database in >> the world will ever be able to make them fast :-) >> Avoiding JOINs is often a good architectural advice (taking in account >> a previous post of the contrary). >> >> For example if you need all orders by user with name Chris, you will >> ALWAYS be faster if you first retrieve the userid, and than the orders >> of the userid. >> > > So you perform two queries from the application layer? You are basically > doing a join by hand - the cost of those two round trips to the database > will lose to a single query with a join, unless you've not setup > adequate indexes and your tables have a huge number of rows in them.
two round trips to the database yes, in theory. The other side of the medal is that i can separate both data entities in different services (where they belong to), i can even hold them in different type of media, i can cache username to userid extremely efficient, which the db never could, and I don't depend on how this db implementation is tuned, and not bound to it. > >> No query optimizer in the world can perform better than the develop, >> simply because it lacks the knowledge a good developer should have >> about the semantic of his application. >> > > Yes, and occasionally I can produce "better" assembler code than my C > compiler. However, in your case you're ignoring the fact that any RDBMS > worth its salt is going to have cached a lot of data in memory, in a way > that is likely to be as fast to access if not faster than the same data > cached and accessed at the application layer. i highly doubt is, cause a local access to memory in the vm is 1.000.000 faster than an access over the net, even in the GBit network. > > In the system I currently work on, there was a gem of a code comment by > a previous developer that said something like "perform these queries > separately as the database cannot optimise them". Normalising the tables > meant the database could. If your joins are costing you so much > performance, then I'd humbly suggest your database design is screwed. > >> my 2 cent as an addition to Jonathan's 2, make it 4 against the joins :-) >> > > Sadly your four cents aren't even legal tender here ;-) My 4 cent may be placed in the wrong place, but basically, from 10 years online portals building (and i mean high-performance, 24/7, with millions of users) if your design relies on a join in the database in the production system, you've done something wrong. --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org