Hi All

I converted my RLIST combinations to REDUCE combinations

for RLIST like

Sel1=SELECT MY_TABLE WITH TRANS_DATE < "6/6/2000"'

call RLIST(Sel1, 5, ,,) Sel2=SELECT MY_TABLE WITH MY_CODE=APPLES" "ORANGES"' call RLIST(Sel2, 5, ,,)

done=0

Loop

readnext my_key else done=1

until done

gosub do_stuff

repeat

translates roughly into

declare subroutine reduce

OPEN "MY_TABLE" to my_table_F else err=broken"

for csr_ptr=1 to 8

clearselect csr_ptr

next csr_ptr

filter=WITH {MY_DATE} LE "28/06/2000"'

reduce(filter, , 1, 'MY_TABLE', fruit_csr, reduce_ok) if reduce_ok then filter= 'WITH {MY_CODE} EQ "APPLES" "ORANGES"' reduce(filter, , 0, 'CHARGES', fruit_csr, reduce_ok)

end

select "MY_TABLE" by "" using fruit_csr then

end_of_list=0

num_selected=0

loop 

  readnext fruit_key using fruit_csr by AT else end_of_list=1

  num_selected += 1

until end_of_list

  gosub do_stuff

repeat

mesg=num selected " : num_selected

end else

status=SET_FSError

end

Note it didn't work if I left out the AT or I hadn't opened MY_TABLE etc.

I got this much to work except from debugger stepping through, it did the first readnext but hung if I tried to F7 past the end of the loop. I know if I do the select on MY_CODE first then the one on the dates second it does work.

REDUCE looks like it would solve a lot of my problems, except each readnext was taking 20 seconds. I was expecting 250 records back which would mean an hour and a half of waiting. The actual REDUCE and SELECT statements were nearly instant.

The RLIST statements, to compare were taking around 10 minutes for the one that returns lots of data and 4 minutes for the one that returns fewer records. so in combination this was around 20 minutes tops if I ran them in reverse order to what I had originally. Which sure beats 80 minutes. Then the readnext that goes with the RLIST was almost instant. So I think I will be reordering my reduction RLISTS to put the ones that produce the shortest result list first. as opposed to putting the non optional lists first.

Can anyone tell me what the difference is between a READNEXT .. BY AT to BY AN ? ie a terminating list vs a non terminating list?

Is there a way to get the reduce to become a save select list that can be "activated"? Can you get something like the @RECCOUNT set for Reduce or Select by…?

I tried setting the fruit_csr to 0 to force overlap between RLIST and Reduce but it didn't work.

Scott, LMS

Scott,

My earlier remark about RList being a shell round Reduce means that Reduce will always be faster than RList.

Now to answer specific question -

@RecCount this becomes available when the list is resolved from a latent to an active list. This can be done with a Select … as illustrated below.

Order of reduction filter can make big difference to speed as with RList - so first criteria should arrive at the smallest list, also indexed values first

Comments on your example

- need to use the '2' parameter when adding a reduction filter to an existing filter.

- dont understand the change in Table name in the second reduction.

- not sure that you are assigning a cursor (but I could be wrong / frequently are)

Below is some sample code you should be able to do your own translate to your code…

Equ New_Exist$ to 0

Equ Next_Cur$ to 1

Equ Add_Exist$ to 2

* Search_Table is previosly Opened ….

flag =" ;* Reduce Flag variable

* ensure that all Cursors are available

For i=1 To 8

  ClearSelect i

Next i

If Lookup Else

• No value to search - send message

 Return

End

 StartTime=GetTickCount()   

• First Reduction set

 reduce_Script =WITH {SERIES_TITLE}  '":LOOKUP:"' OR WITH {MAIN_ENTRY}  '":LOOKUP:"'  "

 reduce_Script := " OR WITH {GROUP_TITLE}  '":LOOKUP:"'"

 sort_List=@ID'

 Cursorvar=0

 mode     =NEW_EXIST$

 Reduce(reduce_script, sort_list, mode, Search_Table, cursorvar, flag)

 If flag Else

    void=Set_Property(Parent:'.STATUS_LINE','TEXT','Search cancelled. Error in searching ': targetfile )

• Set_FSError()

    Return

 End

If Level then ;* Apply further reduction

   sort_List=@ID'

 If mode=New_Exist$ then mode=add_exist$ else mode=New_Exist$

 void=Set_Property(Parent:'.STATUS_LINE','TEXT','Now searching Level=: Level )

 reduce_Script=WITH {LEVEL} EQ '":Level:"' "

 Reduce(reduce_script, sort_list, mode, Search_Table, cursorvar, flag)

 If flag Else

     void=Set_Property(Parent:'.STATUS_LINE','TEXT','Search cancelled. Error in searching ': Material )

• Set_FSError()

     Return

 End 

End

* Can repeat with further reduction(s)

* Turn this into a resolved list

Select Search_Table by sort_list Using Cursorvar Else 

  void=Set_Property(Parent:'.STATUS_LINE','TEXT','Search cancelled. Error in resolving list')

end

EndTime=GetTickCount()

QueryTime=EndTime - StartTime

* — Now @Reduction_Done=1 so

* — can get @RecCount

* Follows is something else - just to check everything working — *

Save_Select(, "MYLIST", ,'Comment2')

if Get_Status() then

• Gosub error_processing

end

Hits=Xlate("SYSLISTS","MYLIST",,'X') Hits_Info=Field(Hits,@fm,1) If Hits_Info then If Index(Hits_Info,@Tm,1) then ;* Take out header which contains number of hits Hits11,len(Hits_Info)+1=

  End

end

HIT_CNT=COUNT(HITS,@fM) + (HITS NE '')

* CALL MSG(parent,"Searched ":LOOKUP:" and items found")

*

Not sure why the readnext would hang. I can tell you that RLIST is just a shell around reduce. The basic difference is that RLIST does the readnext as it's processing and saves the keys in a temporary record in SYSLISTS. When the program comes back, you are simply readnexting through a list of pre-defined keys. When you do the reduce yourself, you have to find all the appropriate keys during each readnext.

Generally, the time of the delays you are seeing seem to indicate large files and no indexes.

We have a two part article on selection/reduction and other various system related functions in SENL. Part 1 is in volume 1, issue 10 and part 2 can be found in Volume 2, Issue 1

The Sprezzatura Group

[/i]World leaders in all things RevSoft[/i]

Hi Richard

Well I typed a long response, including the table name change was a cut paste error brought on by a injured finger, that is crippling my touch typing. The whole lot got eaten when I tried to cut and paste again and replaced my selection with c (as you do when you mis-time the ctrl key part of Ctrl+C)

I thought I checked the @RECCOUNT after the select by stuff and it was still not set. I will test this again. It would be nice if the reduce stuff worked but I was worried that the readnext were taking longer to resolve than the entire process took using RLIST. Twenty seconds per record is too long.

Do you know what happens if you use the logical "and" mode of the reduce statement to join up the filters? Does it bracket the lists correctly or does it just form up a long list and bugger it up when it resolves it eg the (a and b) or c when you want a and (b or c) problem. I wish I knew how some of you do bolding/italics in your posts.

Scott, LMS

(should be asleep now)

Scott,

Try translating your code into the form I provided. Simplify by say doing one Reduce statement then doing the Select.. and check for @Reduction_Done=1. (If that is not happening you have problem).

Once you have the first filter then concatenate or add filters as per the example.

If you use the Add_Filter (param=2) then you get your logical (a and/or b) AND (c and/or d).

The Readnext should take only a milisec per record because it is only reading the key from the cursor - suggests that the list is not resolved.

I will see if I can redo your own code and post - got to go and run 1/2 marathon before breakfast first. Back soon.

Richard

Scott,

I wish I knew how some of you do bolding/italics in your posts.

Use html codes. If you want your name in italics write Scott. Bold is Scott. And then the secret ingredient is to surround the html codes with square brackets! I.e, put a in front of each .

- Oystein -

I have been reading all this "select" talk… I sure cant even think of why you are having so much of a problem with the "readnext" hanging. My customers have files with hundreds of thousands of row (records). They have no problem with selects and readnexts hanging. And the select time is nowhere near your times.

I have this possible thought… Is your table (file) in good condition? Does a "VERIFYLH" show any problems, bad overflows, or sizelock? For a select and readnext to take so long… hmmmm… doesnt sound reasonable unless your system is old and slow or your network is slow or you have millions or billions of rows (records) in that table (file).

My personal experience is when a readnext takes long or seems to hang, it is because the table (file) overflow is so large that the system seems to get lost trying to allocate the memory to handle the extremely and unreasonable large overflow part of the table (file). And it only has to be a few groups that are overflowed so badly that the system chokes at that point.

There is another possibility… see when you do a select using an index, normally the index will be updated before the select can begin. if that is happening to you that would possibly look like the system is hanging. although the index update would go first and complete before any part of the select starts.

Somehow i sure do think there is something wrong with the table (file) and not the select.

Hello all,

Well, since I recently had 2 problematic experiences with RList, I thought I would throw them out.

The first one I encountered had to do with an OI version 3.6 issue. OI would do the select and @Reccount showed a couple thousand items found, however it would abort out of the Readnext section immediately. If the Select statement was shortened, it worked okay. When OI was upgraded to 3.7.2, this problem disappeared.

The other problem was caused by a GFE in the index table. This caused OI to freeze up during the routine (I'm not sure if it was during the Readnext or the Select). Once the indexes were rebuilt, it ran fine.

FWIW,

psimonsen@srpcs.com

SRP Computer Solutions

Hi RH

I am not sure if the table is the problem. If it is the table then it is probably all of their big tables ie this problem happens in a few places.

We have OI v3.7 (I thought it was 3.7.2 but that might be the upgrade we couldn't use because of too much copy_table type code). It does make things really difficult if the client wants new function and we can't apply upgrades with new tables or dictionary items and for report processing to get around the 64K data limit problem, we need to create tables, well that was our solution because as soon as the tables got big, everything goes to *&%$.

The network is slow but there is not much we can do about it unless I start writing letters alternately to our state govt and our newspapers. I would want to be right about the network being the cause of the problem though.

The servers and clients are state of the art for us, even some pentium III 500hz - wow. I don't know why they can get new pc & servers but not new routers, or hubs etc. Probably because the elected govt rep doesn't know what a router is or what problems (costs) a dodgy one causes.

If it is table overflow, how do you fix it? I don't know much about verify so you could be right about that being a problem, I do know the verifys take years to run and then tell me nothing is wrong.

Scott

Richard,

Table overflow: Has to do with Sizelock. Check Sizelock with Database Manager - Utilities | OpenInsight Tables | Table Info. Doubleclick the tables you want to inspect. If Sizelock ]= 2 the table cannot grow properly, and the OV file eventually gets much bigger than the LK part. You can spot that symptom in Explorer btw.

- Oystein -

Øystein Reigem,

Humanities Information Technologies,

Allégt 27,

N-5007 Bergen,

Norway.

Tel: +47 55 58 32 42.

Fax: +47 55 58 94 70.

oystein.reigem@hit.uib.no

Home tel/fax: +47 56 14 06 11.

View this thread on the forum...