[thesite] t.e.o. updates

.jeff jeff at members.evolt.org
Sat Nov 17 16:15:34 CST 2001


rudy,

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> From: rudy
>
> hey, i'm not going to argue with facts, but you are
> definitely comparing apples and oranges
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

yeah, i'm aware of that.  i'm really only interested in how much processing
time the different approaches requires to achieve the same end result.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> in the first of your two queries, you select all
> articles that are not in special categories and less
> than a year old, and in the second of the two queries,
> you have
>
>      WHERE categorys.categoryid = content.categoryid
>        AND content.userid = users.userid
>        AND content.signoff = 1
>        AND content.categoryid NOT IN (1, 23, 26, 9741)
>        AND content.contentid = #Val(getrevivedrange.contentid[RandRange(1,
getrevivedrange.recordcount)])#
>
> i'm guessing, but it looks like the last condition might
> be selecting one or some (five?) of the contentids from
> the first of the two queries
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

selecting one.  let's break it down.

the first query returns all the contentids that match the criteria you
noted.

RandRange(1, getrevivedrange.recordcount)

this gives me a single number from 1 to the recordcount of that first query.
let's call this randomindex

getrevivedrange.contentid[randomindex]

this is a reference to a particular row in the first query results where
randomindex is the row number.  let's call this randomid.

AND content.contentid = randomid

as you can see this becomes a query to return a single record.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> by the way, that last condition, content.contentid =
> [something], means you are going after specific articles,
> so you might as well remove the preceding 2
> qualifications, which, presumably, were already
> satisfied in the first query
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

yes, very good point and something i missed in converting matt's approach to
something more efficient.  i've removed the categoryid NOT IN () criteria as
well as the signoff = 1 criteria and the processing time has dropped to an
average of 15ms.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> the difference in timings is most likely due to the fact
> that in the one-query approach, you are joining all
> articles to their users and categories, but in the
> two-query approach, you are only joining them if they've
> met the selection criteria
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

yeah, a join on a few results is likely to be faster than a join on many
results.  you suppose with the two query approach that re-ordering the
criteria would make it even faster?

     WHERE content.contentid = #Val(getrevivedrange.contentid[RandRange(1,
getrevivedrange.recordcount)])#
       AND categorys.categoryid = content.categoryid
       AND content.userid = users.userid

i just tested it and it doesn't seem to yield any notice time differences.

perhaps the optimizer is already doing that for me internally.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> yeah, the optimizer should've picked up on that, and
> maybe there's a way to use HINT to get it to do the
> same, or perhaps we might try a different tack...
>
> could you run the following test for me?
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

sure

    SELECT content.contentid
         , content.keyphrase
         , content.contentname
         , content.synopsis
         , content.datemod
         , content.replies
         , content.rating
         , content.ratings
         , users.userid
         , users.who
         , categorys.category
         , categorys.categoryid
      FROM content
         , users
         , categorys
     WHERE categorys.categoryid = content.categoryid
       AND content.userid = users.userid
       AND contentid IN
         ( SELECT contentid
             FROM
            ( SELECT contentid, rand.random AS randum
              RANK() OVER
                (ORDER BY randum ASC NULLS LAST) AS contrank
                FROM content
               WHERE content.signoff = 1
                 AND content.categoryid
                     NOT IN (1, 23, 26, 9741)
            ORDER BY randum ASC NULLS LAST
            )
           WHERE contrank < 6
         )

Oracle Error Code = 923
ORA-00923: FROM keyword not found where expected

Cause: In a SELECT or REVOKE statement, the keyword FROM was either missing,
misplaced, or misspelled. The keyword FROM must follow the last selected
item in a SELECT statement or the privileges in a REVOKE statement.

Action: Correct the syntax. Insert the keyword FROM where appropriate. The
SELECT list itself also may be in error. If quotation marks were used in an
alias, check that double quotation marks enclose the alias. Also, check to
see if a reserved word was used as an alias.

http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90202/e9
00.htm#1000549

it looks like maybe it doesn't like the parenthetical right after the FROM
in the first nested SELECT.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> obviously, you may need to adjust WHERE contrank < 6
> (which returns the top five) to reflect however many
> random articles you want out of the subquery
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

WHERE contrank < 2 to return just one?

<tip type="oracle">

need to find out what a particular oracle error number means?  use this
handy bookmarklet and then it's only a click away.  you can either select
the error string in the browser window or click the bookmarklet and enter
the error string into a prompt().  works in ie5+ only.

javascript:function oracleError(){var
errNum=document.selection.createRange().text;if(!errNum||errNum=='')errNum=p
rompt('What\'s the error identifier?',
'');if(errNum)location.href='http://tahiti.oracle.com/pls/db901/db901.error_
search?search='+errNum;}oracleError();void(0);

</tip>

thanks,

.jeff

http://evolt.org/
jeff at members.evolt.org
http://members.evolt.org/jeff/






More information about the thesite mailing list