[thelist] SQL query help
Erik Mattheis
gozz at gozz.com
Thu Jan 3 02:26:22 CST 2002
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/
__________________________________________
More information about the thelist
mailing list