[thelist] SQL query help

Joel Lieberman joel_lieberman at yahoo.com
Thu Jan 3 07:15:41 CST 2002


I hate to get drawn into debates, but the real myth is
that a fully normalized relational database is the
only proper construction.  If that were true, we
wouldn't have so many advanced topics courses run by
the major vendors to address just his issue.

I always design normalized structures initially, and I
am a very strong proponent of surrogate keys.  If
speed becomes a problem and there is no other
workaround, I will consider denormalization. 
Sometimes it is simply necessary, and if you work with
large enough databases and complex applications long
enough, you will find that it can be a lifesaver.

Obviously one uses caution before resorting to data
redundancy/

Cheers

J.E.L.
--- Erik Mattheis <gozz at gozz.com> wrote:
> I modified your SQL a bit and it does the job (the
> votes are recorded 
> as poll_answers.option_id - in yours it was
> poll_options.option_id):
> 
>      select top 1 poll_answers.option_id, count(*), 
> polls.poll_question, poll_options.option_text
>          from polls, poll_answers, poll_options
>          where poll_answers.poll_id =
> poll_options.poll_id
>              and poll_answers.poll_id =
>              (   select poll_id
>                     from polls
>                   where poll_active=0
>                       and poll_date =
>                        (  select max(poll_date)
>                                    from polls
>                                 where poll_active=0 
> )      )
>         group by poll_answers.option_id,
> poll_options.option_text, 
> polls.poll_question
>        order by 2 desc
> 
> Then I came came up with this, which runs 2x faster
> on the server and 
> 4-10x faster on my box here (1/3 the RAM and less
> that 1/2 the 
> processor speed) (here is the test page I used to
> test the speed:
> http://www.gozz.com/temporary/tick_count.cfm):
> 
> 	SELECT TOP 1 Count(poll_answers.option_id) AS
> votes, 
> polls.poll_active, polls.poll_question,
> poll_options.option_text, 
> polls.poll_date
> 	FROM polls, poll_options, poll_answers
> 	WHERE poll_options.poll_id = polls.poll_id AND
> 		poll_answers.option_id = poll_options.option_id
> AND
> 		polls.poll_active = 0
> 	GROUP BY polls.poll_date, polls.poll_question, 
> poll_options.option_id,
> poll_options.option_text,polls.poll_active
> 	ORDER BY  polls.poll_date DESC, votes DESC
> 
> It was because I read your explanation of your SQL I
> didn't 
> understand that I was able to come up with this, but
> I think mine's 
> better! Faster and easier to follow.
> 
> >  > It seems a better option for the purpose at
> >>  hand to duplicate stuff if it will speed up page
> processing.
> >
> >that's a myth, sir
> >
> >trading redundancy for processing speed is a
> >http://www.m-w.com/cgi-bin/dictionary?mug's+game
> 
> Wha? Like on general principles or it's impossible?
> 
> >there's nothing wrong with having blazing speed as
> a design objective, but
> >it is only one system factor and must be balanced
> against other worthwhile
> >qualities like understandability, portability, and
> maintainability
> 
> Well, like I want to put off getting a second server
> as long as 
> possible ... and she's my baby ... I want to go as
> easy as possible 
> on her. Quick queries == happy good.
> 
> And I think I write pretty understandable code ...
> heh, you were able 
> to come up with a query that did the trick just from
> my table and 
> column definitions and a description of what I
> wanted the query to 
> do! (wink)
> -- 
> 
> __________________________________________
> - Erik Mattheis
> 
> (612) 377 2272
> http://goZz.com/
> 
> __________________________________________
> 
> -- 
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt ! 


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com




More information about the thelist mailing list