[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