SYSLISTS

Opto_Will · November 2023

I am going to see if I can cut a long story short here.

We had an issue where a client came into all sorts of grief because the SYSLISTS REV*.OV file was like 60GB+
Turns out some of the Devs havent been using RTI_LIST("DELETE", myID) when they were putting SELECT staments to a TARGET_SAVELIST$.

OK. We can go through and fix those up.

In the process of trying to clean up the existing data though we spent a bit of time working out what the SAVELIST does.

It seems that the SAVELIST will create 3 SYSLISTS records .

The 'GUID' record
a 'Q' record that gets appended to in subsequent creations (maxed at 10 maybe?). It looks like a 'Recent' list?
a 'W' record that looks suspiciously like the data in the GUID record!

If I do a RTI_LIST("DELETE") it seems to only delete the GUID record and not the matching W record (or adjusting the Q record)
That would indicate that, regardless of good practice, those W records will still eventually become quite large when they represent SELECTs with large returns.

So my questions:

What are the Q and W records used for?
What does the RTI_LIST("DELETE") not remove them as well?
How to W records get cleaned up then?
Is anything "important" permanently stored in SYSLISTS or can I just do a CLEAR_TABLE?

BarryStevens · November 2023

this might provide some insight for you

BarryStevens · November 2023

I knew I had this somewhere.

Subroutine BS_Clean_Lists(void)

pfxlist = "T,W,Q,B,TCL"
Open "SYSLISTS" To slFv Else Return
idate   = date() - 2
Select slFv
EOF     = 0; knt = 0

Loop
	Readnext @ID Else EOF = 1
Until EOF
	if index(@id,'.',1) then
		lock slFv,@id then
			unlock slFv,@id
			delete slFv,@id
		end
	end else
		part1 = @ID[1,"F*"]
		Locate part1 In pfxlist using "," setting F Then
			
				Delete slFv, @ID Then
					knt += 1
				End
			
		End Else
			part1 = @ID[1,"F_"]
			If part1 eq "SESS" Or part1 eq "SESS2" Then
				part2date = Field( @ID, "_", 2)
				If Num( part2date) Then
					If part2date lt idate Then
						
							Delete slFv, @ID Then
								knt += 1
							End
						
					End
				End
			End Else
				part1 = @ID[1,"F*"]
				If part1 eq "QUERY.TABLE" Then
					
						Delete slFv, @ID Then
							knt += 1
						End
					
				End Else
					part1 = Field( @ID[1,"F."], "*", 3, 3)
					part1date = iconv( part1, "DE")
					If part1date gt 0 and part1date lt idate Then
						
							Delete slFv, @ID Then
								knt += 1
							End
						
					End Else
						part1 = Field( trim(@ID), " ", 2,3)
						
						part1date = iconv( part1, "DE")
						If part1date gt 0 and part1date lt idate Then
							
								Delete slFv, @ID Then
									knt += 1
								End
							
						End
					End
				End
			End
		End
	end
Repeat

return

AusMarkB · November 2023

FWIW, at some bigger clients, we would keep an empty copy of the lk and ov files for syslists on hand. Whenever syslists became large enough to hinder performance, (and it inevitably would regardless of our attempts to clean it out on the fly), we'd simply do a doscopy of the files.
Obviously we'd choose an appropriate time to do that, but it was the simplest, quickest way to rectify the problem and never seemed to have any ill effects.

Oddly enough, when performance was dire enough to point us in that direction, users seemed more than willing to log off for a couple of minutes and appreciative of the almost instant noticeable resolution.

Opto_Will · December 2023

Thanks @AusMarkB. Thats what I ended up doing for this site in particular. I gave them the empty files that we deploy with a new site. No ill effects so far.

@BarrySteven, Thanks for the link. Interesting read. I only have gone through it 2 or 3 times so I still have a few more before I start to understand it ;-)

Also, I appreciate the code sample. I will run it against the backup copy of the problem site I kept and see what it does for us. I wasn't aware SYSLISTS had so many record types!

We have started using Saved Lists more and unfortunately not always deleting them after use. I have now gone through our entire source and corrected that oversight.

Opto_Will · February 9

Would this logic also apply to the systasks table?
We are also building up entries via immediate RTI_TASK_SUBMITS.
Obviously the completeion of a task doesnt clean that up, otherwise you couldn't retrieve the status.

Opto_Will · February 16

Does anyone else have an ever growing SysTasks table as well?
I was thinking maybe AusMarkB's solution for Syslists might also apply to Systasks?

DonBakke · February 16

I'm being mum on this because I don't have any apps that use the SYSTASKS table.

Opto_Will · February 16

Fair enough.
We have only just started using it ourselves (Immediate Task Submits) to get around a problem we encountered running some preocess via System Monitor. Thats a longer story.

Already though, I can see the residual entries stacking up so my question was pre-emptive rather than problematic.

AusMarkB · February 20

See @DonBakke

Opto_Will · February 20

@AusMarkB

You, sir, are a neverending source of Boomhauer memes! Always gets a chuckle :)

SYSLISTS

Comments