pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Vernon Wu <vernonw@gatewaytech.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: query speed joining tables
Date: Thu, 16 Jan 2003 15:22:04 -0800
Message-ID: <AC93265Y52EDXCB3VJE2174A9321Z.3e273e9c@kimiko> (raw)
In-Reply-To: <web-2316225@davinci.ethosmedia.com>

16/01/2003 9:46:30 AM, "Josh Berkus" <josh@agliodbs.com> 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





view thread (12+ messages)  latest in thread

Message-ID: <AC93265Y52EDXCB3VJE2174A9321Z.3e273e9c@kimiko>
Permalink:  ../AC93265Y52EDXCB3VJE2174A9321Z.3e273e9c@kimiko/
Also on:    postgresql.org/message-id/AC93265Y52EDXCB3VJE2174A9321Z.3e273e9c@kimiko

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: vernonw@gatewaytech.com, josh@agliodbs.com
  Subject: Re: query speed joining tables
  In-Reply-To: <AC93265Y52EDXCB3VJE2174A9321Z.3e273e9c@kimiko>
  References: <web-2316225@davinci.ethosmedia.com> <AC93265Y52EDXCB3VJE2174A9321Z.3e273e9c@kimiko>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox