Re: need help on a design point

It's hard to tell which way is better. There might be hardly any difference at all since tables are small. A bunch of smaller queries means a bunch of smaller transactions which means less Oracle time per transaction which in turn means less chance for user lockups in the database. A bigger query may cause someone to wait for resources especially if there are many users. Traditionally, DB app designers tend to minimize network traffic by breaking transactions down into smaller ones and by moving less data back and forth.

Personally, I like to try and use stored procs whenever possible instead of SQL directly from the app. It allows the DBA greater control over DB performance.

In article <0ffbbd9d.3c10ffe7_at_usw-ex0102-015.remarq.com>,   gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:
> This is just something I thought I'd put out here as a topic for
> discussion...
>
> I've reached a disagreement on a proposed schema design point
> with our lead developer.
>
> It comes down to whether a "wider" query (more columns in the
> select statement) is more efficient/performant than N thinner
> queries.
>
> What we're creating here is a "metadata" layer in the database
> to assist with user interface navigation. (Based on the data
> that comes back, alter the UI to assist the user in determining
> what they should look at next) By default these tables will be
> small and will grow very slowly, unlike OLTP tables.
>
> One proposed design has most of this metadata stored in "lookup"
> tables. This architecture requires multiple queries to be sent
> to the server in order for all required metadata to be
> extracted. The processing is quite iterative against oracle.
> However, each query is very small, not server
> intensive...essentially very lightweight.
>
> The other proposed design (this is the one I'm in favor of) will
> allow all of the appropriate metadata to be returned in one
> query along with the actual data. The benefit of this is that
> all the information is retrieved in one shot, no roundtripping
> back and forth with oracle, less taxing on oracle resources (cpu
> cycles, memory, shared sql area etc...) But the downside (if
> this is a downside), is that this one query is now much "wider."
>
> (A further point but it is not something we are debating... The
> lookup table approach is highly normalized, whereas the 2nd
> approach is not, so there is redundancy. But I honestly believe
> that there are cases where de-normalizing can provide
> performance improvements, and this is one of them...but this is
> just a side note...The only issue we are debating is the impact
> of "wide queries")
>
> We are essentially talking about increasing the number of
> columns in the select clause by a factor of 2. If we made this
> change the max number of columns would never be more than 20.
> (so it's not outrageous). These are all varchar2 columns <= 100
> characters in length.
>
> Are these valid concerns? Does he have a point? Are there any
> monitors or statistics I can use to help determine the best
> approach to take?
>
> My initial logic is that a wider query is more taxing on the
> network, but less taxing on the application and on oracle,
> whereas the other approach is the opposite. Since, I'm the
> Oracle guy here...I'm always biased in assuming that the scarce
> resource here is the database (as it always seems to be the
> bottleneck)...I think he feels that network bandwidth is the
> scarcer resource.
>
> Any comments are welcome.
> Thanks,
> Gavin
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Aug 03 2000 - 00:00:00 CDT