Recursive Selection process -- syntax?

Results 1 to 3 of 3

Thread: Recursive Selection process -- syntax?

  1. #1
    SPG Guest

    Default Recursive Selection process -- syntax?

    When running an additive thread -- like this message board, for example -- given that each item has a Unique ID and an Added-To ID where the Added-To can reference any unique ID...<BR><BR>How can I write a select that will yield a recordset sorted with Added-To values following immediately after their Unique ID values?<BR><BR>For example, we have A1. A2 comes along adding to A1 (results are now { A1, A2 }). A3 does likewise. Then A4 comes along and adds to A2, A5 adds to A3 and A6 adds to A4 resulting in -- ideally -- { A1, A2, A4, A6, A3, A5 }<BR><BR>It&#039s easy with a recursive set of queries, but that&#039s also undesireably slow... Additional values to work with -- if they help -- are the Thread ID, the Depth (integer), and a Timestamp.<BR><BR>Thanks for any advice.

  2. #2
    Join Date
    Dec 1969

    Default RE: Recursive Selection process -- syntax?

    The real answer: You can&#039t.<BR><BR>This is something that is better done in VBScript code after getting all the records you will be "sorting" into this unique order.<BR><BR>Once upon a time, I "invented" an alternative "id" scheme that would sort the records automatically, but I haven&#039t seen anybody use it yet. Everyone goes for the simpler-to-design scheme you mention, even though it puts more load on the server.<BR><BR>Incidentally, you probably *can* do this with an OLAP query, but (a) it&#039s complicated, (b) it&#039s likely not a *whole* lot faster than the VBS solution, and (c) you&#039ll have to get somebody else to help you (grin).<BR><BR>Anyway, writing the code in VBS isn&#039t too tough if you first convert the recordset to an array using RecordSet.GetRows. Just a little bit of recursive subroutine calls and it&#039s done. I wouldn&#039t recommend the technique for more than a couple of hundred records (which is possibly one reason this site is divided up the way it is), because it&#039s essentially an "n-squared" algorithm, but...<BR><BR>One thing you can do: If all your users will see the same "tree", it might not be a horrible thing to keep the tree in memory, as an application variable, once it is generated. Update the tree directly when postings occur (or, I suppose, regenerate it, if that is easier and postings aren&#039t too frequent) as well as storing them in the DB. The memory usage isn&#039t really that much--a *LOT* less than having 200 users each regenerating the stuff each time they hit the page!--and it is, after all, multi-user material.<BR><BR>Final possibility: Send the raw data to user&#039s browser and do the sort in client-side JavaScript. Essentially same code, but you offload the work from the server.<BR><BR>Bill<BR><BR>

  3. #3
    Scott S Guest

    Default RE: Recursive Selection process -- syntax?

    I think what Bill said was very true, just as an aside though, I was working on a system similar to this one at one point. what i found was that by displaying the messages in tables it substantially increased the time it took to load. I realize this has nothing to do with the server load or query syntax but for me the result was the same: the user sees what he wants to a lot faster. <BR><BR>instead of tables i used &#060;PRE&#062; & return characters. you could use <BR> as well i didnt try that though. using PRE & return characters was blazing compared to the tables. <BR><BR>thought you might be interested.<BR><BR>Scott S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts