Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EB0BD477264 for ; Thu, 16 Jan 2003 18:30:54 -0500 (EST) Received: from pd3mo3so.prod.shaw.ca (shawidc-mo1.cg.shawcable.net [24.71.223.10]) by postgresql.org (Postfix) with ESMTP id EC43D477086 for ; Thu, 16 Jan 2003 18:24:05 -0500 (EST) Received: from pd6mr3so.prod.shaw.ca (pd6mr3so-qfe3.prod.shaw.ca [10.0.141.218]) by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12 2002)) with ESMTP id <0H8T00G2TYC8YA@l-daemon> for pgsql-sql@postgresql.org; Thu, 16 Jan 2003 16:24:08 -0700 (MST) Received: from pn2ml5so.prod.shaw.ca (pn2ml5so-qfe0.prod.shaw.ca [10.0.121.149]) by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12 2002)) with ESMTP id <0H8T008M7YC8TJ@l-daemon> for pgsql-sql@postgresql.org; Thu, 16 Jan 2003 16:24:08 -0700 (MST) Received: from kimiko (h24-78-132-76.vc.shawcable.net [24.78.132.76]) by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12 2002)) with SMTP id <0H8T00MD5YC7AP@l-daemon> for pgsql-sql@postgresql.org; Thu, 16 Jan 2003 16:24:08 -0700 (MST) Date: Thu, 16 Jan 2003 15:22:04 -0800 From: Vernon Wu Subject: Re: query speed joining tables In-reply-to: To: Josh Berkus Cc: pgsql-sql@postgresql.org Reply-To: vernonw@gatewaytech.com Message-id: MIME-version: 1.0 X-Mailer: Opera 6.05 build 1140 Content-type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: 7BIT X-Priority: 3 (Normal) X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/269 X-Sequence-Number: 11134 16/01/2003 9:46:30 AM, "Josh Berkus" wrote: >Vernon, > >> What I stated is my observation on my project with over twenty >> multivalued detail tables. I have a selection query >> contained 200 characters, involving 10 tables, and using subquery. >> The performance is not bad after properly indexing, >> least than 3 second (what the planner says). I will have longer >> queries later and hope they won't have any performance >> problem. > >Keep in mind that the complexity is all on your end, not the users'. > You can construct VIEWs, FUNCTIONs, TRIGGERs and RULEs which will make >the actual sophistication (i.e., 20 "detail tables") appear to the user >exactly as if there was only one flatfile table. > Well, my current position is a DB design as well as a DB user. I'm doing J2EE development without EJB. I currently have two ways of building a query. One is to set up a query string as a static string. This method is similar with the View in DB, but in application layer (Date Access Object). I apply this type of query strings on insertion, selection, updating, and deletion operations of a DB table. The other way to build a query string is used on selection operation for multiple table joined and/or involved. A query string is built dynmically due to whether or not any fields are examined. The characteristic of the application is that among of many fields a user may only want search on a few selected fields. I think this approach is better than to have all fields listed and fill in "%" for fields the user doesn't want to search on. (Please correct me if I'm wrong). But the building query function is as long as more than one hundred lines. >Frequently in database design, the design which is good for efficiency >and data integrity ... the "nromalized" design ... is baffling to >users. Fortunately, both SQL92-99 and PostgreSQL give us a whole >toolkit to let us "abstract" the normalized design into something the >users can handle. In fact, this is job #2 for the DBA in an >applications-development team (#1 is making sure all data is stored and >protected from corruption). > Please elaborate the above statement. I don't know any 'toolkit to let us "abstract" the normalized design into something the users can handle', other than something like View. >> Thank you for recommending another DB book after the "Database Design >> For Mere Mortals". I will read the book. > >That's a great book, too. Don't start on Pascal until *after* you >have finished "database design". I waited for the book from the local library for more than a month, but only took me less than a hour to scan over the whole book and grip the multivalued table design idea. > >-Josh Berkus > Vernon