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
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