[thelist] CFQuery sub search...

[.jeff]

joshua,

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> From: Joshua Olson
>
> : the only limitation i can think of is that there may
> : be a character string limit on the number of values
> : in the IN list
>
> Rudy, you read my mind!
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

mine too and for the same reason.

you beat me to the response though.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> We use Sybase ASE extensively (which is a derivative of
> MS SQL) [...]
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

it's the other way around actually.  ms sql server licenses (at least it did
as of 6.5 and maybe 7.0) the database engine from sybase.

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> If your like me, then you may not want to pass the
> entire list of PK's via the form.  So, perhaps set
> up a session var to hold the list. [...]
>
> Hence plan B.  Create a cache file on the server that
> contains nothing but the PK's of the query. [...]
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

all good ideas, but i doubt it's less processor intensive than simply
parsing the list of pks into 255 unit chunks on the fly.  besides, it'd have
to be a pretty hefty database of material if you're going to encounter a
chunk of records over 255 that meet the initial search criteria.

i don't like passing the pks in the form either.  it leaves it open to too
much meddling.  to combat it i might choose to perform a simple encryption
on the list before stuffing the value in the hidden input and then decrypt
on the server only if the user checks the "search within..." checkbox.
otherwise i'd just generate a new list of pks for the new search.  i think
in most cases this will probably suffice.

<tip type="ColdFusion" author=".jeff">

have a list of items that you want to break up into chunks?  convert your
list to an array (chunkArray in this example).  then, adjust the "chunkSize"
variable so it reflects the number of elements you want in each chunk (if it
doesn't divide evenly or if your chunkSize is bigger than the number of
elements in your list it's no big deal this can handle it).  change the
value of the "chunkDelimiter" variable to whatever you want to be the string
of text in between your chunks (mine will be calculated chunks of IN
statements).

<cfscript>
  chunkSize = 255;
  chunkDelimiter = ')' & Chr(10)& '  AND id IN (';

  for(i = chunkSize; i LT ArrayLen(chunkArray); i = i + chunkSize)
    chunkArray[i] = chunkArray[i] & '^';
  chunkList = ArrayToList(chunkArray);
  chunkList = Replace(chunkList, '^,', chunkDelimiter, 'ALL');
</cfscript>

then, use the chunked up list in your cfml:

WHERE id IN (#chunkList#)

the execution time for a 1000 element list chunked into 255 element chunks
is usually around the 10ms range on my taxed laptop running cf5, win2k,
650mhz, and 256megs of ram.  the execution time is directly proportional to
the number of elements in the list  and the number of elements per chunk --
the more elements or chunks there are the longer it will take.

</tip>

just my 2¢,

.jeff

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