[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