[thesite] friday is a good day..

jeff jeff at members.evolt.org
Fri May 18 14:49:44 CDT 2001


rudy,

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: rudy
:
: i don't know what the page is doing, whether it
: is caching the results and/or how it is stepping
: through the "next 10 pages" so it might be
: legitimate to go get all the articles -- but if
: the "next" type links do *not* use cache and
: instead go and query the database again, then
: the above is truly wasteful, and we should consider
: putting a date check on it to get only the most
: recent articles or something
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

here's what the actual query looks like:

<cfquery
 name="articles"
 datasource="#data#"
 blockfactor="100"
 cachedwithin="#CreateTimeSpan(0,0,10,0)#"
>
  SELECT content.contentid,
         content.keyphrase,
         content.contentname,
         content.synopsis,
         content.datemod,
         content.replies,
         users.userid,
         users.who,
         categorys.category,
         categorys.categoryid,
         (SELECT Count(*)
            FROM replies
           WHERE contentid = content.contentid
             AND datemod > '#DateFormat(session.user.lastlogin)#'
         ) AS newcomments
    FROM content,
         users,
         categorys
   WHERE
  <cfif Len(url.sid) AND Val(session.user.priv) AND Val(session.user.priv)
GTE 3>
         content.signoff = #Val(url.sid)#
  <cfelse>
         content.signoff = 1
  </cfif>
     AND content.userid = users.userid
  <cfif url.tid NEQ 23>
     AND content.categoryid <> 23
  </cfif>
  <cfif url.tid NEQ 26>
     AND content.categoryid <> 26
  </cfif>
  <cfif url.tid NEQ 9741>
     AND content.categoryid <> 9741
  </cfif>
     AND content.categoryid = categorys.categoryid
  <cfif Val(url.tid)>
     AND content.categoryid = #Val(url.tid)#
  </cfif>
   ORDER BY content.datemod DESC,
            content.contentid DESC
</cfquery>

this query is used to drill down into the total article listing.  that drill
down uses the cached query if it exists.  in most cases it will exist as it
is cached for 10 minutes.

my suspicion is that the speed-killing culprit is the subselect aliased as
"newcomments".    i think we can minimize the use of that subselect to only
those instances where the user is logged in.

<cfif Val(session.user.id)>
         (SELECT Count(*)
            FROM replies
           WHERE contentid = content.contentid
             AND datemod > '#DateFormat(session.user.lastlogin)#'
         ) AS newcomments
<cfelse>
         (SELECT '0'
            FROM replies
         ) AS newcomments
</cfif>

(aside: can i do a select without specifying a table?)

the benefit of altering the logic that generates the query is that we will
get faster results for those instances where we know what the answer is (if
you're not logged in we know there aren't any new comments for you).

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: however, my first attempt at query optimization
: would be to restrict the query to returning no
: more rows than the page will display, if indeed
: the "next 10 articles" feature does go back to
: the database...
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

yes, that would be my suggestion as well .... *if* it did go back to the
database (in an uncached/new query fashion).

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: does oracle support anything like microsoft's
: SELECT TOP or mysql's LIMIT?
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

good question, but that won't help us out in this situation as we need to
generate the entire resultset in order to page through since coldfusion
needs to have the entire resultset for startrow and maxrows attributes of
the cfoutput to work properly.  the only exception is if we can pass to the
query where in its internal resultset we want to start and how many rows.
even if this were possible, which i don't think it is, i don't see this
improving the time of the query as it now not only has to retrieve the
entire resultset internally, it then has to grab the portion of the data
that meets the startrow and maxrows criteria.

thanks,

.jeff

name://jeff.howden
game://web.development
http://www.evolt.org/
mailto:jeff at members.evolt.org





More information about the thesite mailing list