[thesite] CSS related bug?

[Daniel J. Cody]

rudy wrote:
> 
> >http://test.evolt.org/article/Top_Ten_SQL/18/131/evolt.borg
> >
> >too bad, cus i wanted to read it :(
> 
> daniel, if the reason you wanted to read it has anything to do with writing
> a query that returns the top X rows, please consider that you will get poor
> performance using the correlated subquery technique outlined in my article

i was thinking along that line, yes.. thanks for the warning, i wont
read it now :)

> remember a thread we had on thelist a long time ago about how to use
> MAXROWS in CFQUERY to limit the number of rows in cold fusion?

yup.

> therefore, if there's some way to limit the query *before* the sort, that
> might improve response time

exactly.
 
> for instance, if you were interested in returning the last 5 articles, we
> have about a thousand articles in the CONTENT table, so it might help if
> you put a condition into the query that returns only those articles posted
> in the last thirty days, use ORDER BY datemod DESC, and then have MAXROWS
> in the query cut it off

i tested a ton of shit out durning codefest 2.0 on this rudy.. doing a:
<cfquery name=lastfive datasouce=yadda maxrows=5>
select id, subject from posts 
</cfquery>

was taking about 15 seconds to run.(there are 50,000 rows in that table)
obviously my query was shit though so i tried:

<cfquery name=lastfive datasouce=yadda maxrows=5>
select id, subject from posts where id >= 45000
</cfquery>

still took 14 seconds, even though i was only asking the DB to sort 5000
records. i then asked it to:
<cfquery name=lastfive datasouce=yadda maxrows=5>
select id, subject from posts where id >= 49950
</cfquery>

which still took around 12 seconds, even though it was only getting 50
rows. interestingly, when i used the 'blockfactor' attribute in cfquery
with blockfactor=75, it knocked it down to 9 seconds. using
blockfactor=100 was 12 seconds and blockfactor=50 was about 11
seconds(which on a tangent is very interesting) so i had something like
this:

<cfquery name=lastfive datasouce=yadda maxrows=5 blockfactor=75>
select id, subject from posts where id >= 49950
</cfquery>

but it was still taking about 9.5 seconds.. what i ended up doing is
limiting the query before the sort, as you mentioned above. the maxrows
tag really has shit to do with performance, although i never really
realized it. what i came up with in the end is:

<cfquery name="lastfive" datasource="#data#" maxrows="5"
blockfactor="75">
		select id, subject, author from posts 
			where id >= (select count(*) -5 from posts) 
			order by id desc
</cfquery>

this appeared to me to be the same sort of query(get id where its > X)
as above, but it runs at right around 5 secs. i then threw a
cachedwithin attribute in there and its actually pretty quick now. 

so it used to take about 15 secs for that 'last 5 msgs on thelist' thing
to run, now its at about 5. i'm still going to have adam look over it
and see if theres any other way i can optimize it, but i'm not unhappy
with what we have now.

anyways.. i need to get started packing my stuff to move :(

.djc.