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.