FILTER command help

[Ricklionhart]

I've got the whole Bible in a text field, 31103 lines, in the format:
1•1•1•1•#In#the#beginning#God#created#the#heavens#and#
2•1•1•2•#And#the#earth#was#waste#and#void#and#darkness#
3•1•1•3•#And#God#said#Let#there#be#light#and#there#

I put the field into a variable (rawv) then FILTER it for the search term, eg:
FILTER rawv with ("*#" & cd fld "srchterm" & "#*)

But I don't get any results after about 2/3rds of the data - the just don't get put into the filtered result. I thought this might be a limit on the memory size of a variable, but this proved wrong as I can PUT RAWV INTO CD FLD "RESULT" and get an exact copy of the original field, which wouldn't be the case if the rawv variable had a size limit.

Finally, I split the routine into 3 parts, doing the FILTER for lines 1 to 10000, then 10001 to 20000, then 20001 to 31103. It works fine when I do that, so that leads me to believe that there is a limit on the number of lines that the FILTER command can handle.

Can anyone confirm that there is such a limit for the FILTER command?

Obviously with that many records, I should be using an SQL database and doing an SQL SELECT but Rev returns the results instantly using the FILTER command, with the above workaround. And I'm a bit of a newbie when it comes to using an SQL database with Rev, every time I try to use one locally I get a 'could not connect' error. Can someone post me a few lines showing how to create one, write data to one, etc? Much appreciated.

[kpeters]

Not so sure whether you *should* be using a SQL server for this. It's a historical text that's not changing anymore and you don't have to sort it either.

Revolution is pretty darn good and fast at working with text and SQL servers are not terribly good at performing full text searches - I think your approach makes a lot of sense.

You may want to direct your question directly to the Rev people as I believe it to be a real 'under the hood' question and I doubt anyone but them would have these implementation details.

HTH,
Kai

[Janschenkel]

Hi Rick,
If the 'filter' command is causing this type of problem, you should definitely report it in the Quality Control center: http://quality.runrev.com.
That being said, for large datasets it is sometimes faster to use the 'repeat for each' construct, especially if the filter is complex:

Code: Select all

-- put the two fields into variables
put field "ManyLines" into tData
put field "SearchTerm" into tSearchTerm
-- loop over the lines of data in the fastest way
repeat for each line tLine in tData
  -- check if this is a line that we are interested in [1]
  if tSearchTerm is in tLine then put tLine & return after tFilteredData
end repeat
-- remove the trailing return and display the results
delete char -1 of tFilteredData
put tFilteredData into field "FilteredLines"

As you can see, you can adapt the filtering method at [1] for more complex searching methods, but I'll leave that as an exercise to the reader

Hope this helped,

Jan Schenkel.

[oliverk]

Hi Rick,

Is there any chance you could send me your stack?

I have taken a quick look at the implementation of the filter command and can't see why it should be limited to such a relatively small number of lines.

If I can use your stack to reproduce the problem on my system then we may be able to confirm it as a bug and fix it.

The most sensible thing is probably to post a quality control report as Jan suggested and attach the stack to it.

Regards
Oliver