Re: query speed joining tables

16/01/2003 9:46:30 AM, "Josh Berkus" <josh(at)agliodbs(dot)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