Legacy code for grid column sorting checks for recc() > 10000 5

[Patricia Cu]

thread184-1776680

Hello,
I am in charge of doing maintenance for a VFP 9 application that has been used since the 2000s at least. It was developed by multiple people. The infrastructure is quite big, with a lot of custom classes that 'wrap' and extend the base functionality of native controls.
For the grid, a class exists which allows for sorting on columns when clicking on the column header.
The code that executes the sorting, follows the recommended/standard INDEX ON TAG logic. But in order to issue the INDEX ON command, it first does the following:

sele(lcalias)
if recc() > 10000
nchoice = ogx.gmessagebox("Create New Index", 4+32+256)
endif
I am not sure why it needs to perform this check, if it was needed in previous versions of vfp, and no longer required, or if there is another way to sort grid columns that does not have to perform this check? What does it prevent?

Thank you for your time.
Patricia

PS: Apologies if this is not related to the thread mentioned above.

 

[GriffMG]

Hi

It's just a message that the system puts up if there are more than 10,000 records to reorder - it might take a few moments to do - so your system prompts first.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

There's several things wrong with this. The main one is that you should not be creating a new index every time the grid is sorted. Assuming we are dealing with a physical table (that is, a DBF rather than a cursor), you should be creating the indexes when you first create the table. After that, you can forget about them. VFP will automatically keep them up to date as the table itself is updated.

If, at run time, you don't know if an index exists for a particular column (perhaps because this is a generic class that is designed to work with any arbitrary table), then you can use functions such as ATAGINFO() to check that.

Next point: Issuing this kind of message serves no good purpose and is likely to confuse the user. Users cannot be expected to know about indexes. If you do need to create the index on the fly like this, you should do so "behind the scenes", without any user interaction. If you are worried about the user seeing a delay while the index is being created, a simple message saying "Working ...." would be better. And it should appear as a wait window, not as a messagebox that the user has to explicitly dismiss.

And finally, the messagebox should not be asking the user to agree to the creating of the index. This is for the same reason as above. The user does not know what an index is, and so is in no position to judge whether it is needed. But if you really want to ask the user, you messagebox should contain enough information to enable the user to make the choice.

In summary: create all your indexes in advance; and don't worry the user with any messages.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Mike,

If the OP was writing something new, your advice would be spot on, but this is a 20 year old application - presumably with users that are used to it's behaviour and, perhaps (stretching it a bit) documentation that actually explains this approach.

I think the OP is just wondering why the user is being given a chance (from the code sample) to decide if they feel like waiting for a list to be sorted... wouldn't it be nice if Windows did this sometimes when you open a folder with a gazillion files in it....

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

I take your point, Griff. So, on that basis, the simple answer to Patricia' question would be: No, you don't need to perform the check. But, if she removes the check, she should perhaps take the opportunity to review the indexing at the same time.

Also, 10,000 records isn't a particularly high number. Would it normally take more than a few seconds to index that number? - depending on the length of the key and other factors, of course.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I would also argue this: If this sorting was about tables, the backend DBFs, you wouldn't INDEX ON shared data, because that creates tags in shared CDXes and that'd regularly cause trouble. Instead, you'd index your database to start with once and make use of these indexes. Regularly creating indexes wouldn't have survived for long.

As Mike said you'd rather use an existing index tag, it's there for ordering besides seeking/locating/Rushmore and any new data automatically gets indexed.

So like Griff I'd assume this is about queried data, a cursor, initially with no index tag. And we don't really know how complex the routine is and whether it only indexes when a certain tag name isn't already existing.

You can at least be sure that indexing data on the fly, like anything, got faster with more RAM and faster hard drives and new drive types like SSD. So you can likely at least raise the limit.

There's always the subject of whether such a question makes sense at all about something that even in the worst-case doesn't take forever. Well, if you compare it to the time reading the question the first time, perhaps, but if you know this behavior and you're sure you want to sort and know it may take 2-3 seconds you'll get used to header click + key Y or ENTER to sort, not reading the question anymore and if you click on the wrong header you might realize it at the moment before you already press Y or ENTER and then are happy you can cancel out with N and then click on the column you really want to sort.

So from that perspective, even a shorter indexing time is already a reason to ask about whether the user really wants to do that. Especially since there is no way of doing this as a background task and let the user do something else, while that column is indexed. There is, but I don't want to open a can of worms about that, as it has no big benefit. If users don't want to wait >3 seconds, that's enough time to switch to Outlook just for checking whether new mail arrived, for example. Plenty of other tasks not related to your own software people can do. But usually, this is already over when you realize it takes a bit, especially with cursors.

It might even have been chosen not to care whether a tag already exists and simply index, as it usually is fast enough and you can SET SAFETY OFF to avoid the error about an already existing tag.

But also this limit is a sign this may have been a complaint about the software leading to it or the programmer was just very aware of the general rule to not do anything taking more than 3 seconds without at least signaling progress to the user and even better perhaps ask/warn in advance. Like Griff asked about Windows Explorer sort behavior. Even with my SSD some folders take "ages" to sort files by date or name or size. You can opt-out of the sorting progress, at least. But once you do an INDEX ON there is no secondary thread and no DOVENTS will help you to cancel out of this command by reacting to ESC presses, for example. As VFP isn't multithreaded in itself, while the indexing is done, nothing else happens, not even keypress events or timers, anything like events only fires in between executing code lines. If you put DOEVENTS in a loop processing something that can make a VFP application responsive, you can focus another control and type in something, for example, but INDEX ON isn't going to the next line before it finishes. So a DOEVENTS will not kick in this way.

Thus, all you can offer is not to index instead of indexing.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TheProgrammerGuy]

Nevermind

Larry (TPG)

 

[Patricia Cu]

Thank you all for your feedback, it is all very enlightening.

As Mike Lewis and Olaf Doschke suggest, the grid that is used is a generic class that is designed to work with any arbitrary cursor, and when sorting,
it will first look for the key and if not found then it will create the index and tag. The message will only show when recc() > 10000.
I will look into implementing a more friendly message and also increasing the limit.

Thanks again for your time!

Regards,
Patricia

 

[Mike Lewis]

Glad to hear that this has been useful, Patricia.

I would just add that, if you decide to keep a message, that you make it a Wait window (with the Nowait option) rather than a message box. This would be less intrusive, and, more importantly, it won't require the user to dismiss it (which could take almost as long as the actual indexing).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TheProgrammerGuy]

I think that the fact that the answer is going to a variable indicates that the process can be stopped.

 

[Patricia Cu]

Yes, it would be up to the user to continue or not. There is no way of knowing what caused the implementation of the check for recc() > 10000. Olaf's theory makes a lot of sense. The message does not seem to happen very often (users had not reported it for a while), so I think raising the limit would be a good enhancement.

Thank you all for your feedback!

Regards,
Patricia

 

[Olaf Doschke]

I second that vote against the wait window.

Even if you'd remove the option to not sort and let this merely become a notification of the indexing, my experience with wait windows is, they are often not noticed at all. You can position them but in terms of their appearance... It's something I won't present to users anymore. Even though the help says since VFP7 Windows Control Panel settings determine some font characteristics. Who's telling the user or admins that? And you only influence the font a bit and influence it not only for VFP wait windows.

People sometimes even don't know the status bar is a source of notifications of desktop applications.

And, of course, when you still want to ask the user, stay with a message box.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

OK, forget the Wait window. My point was that, whatever method you use to notify the user, it should not require a user response. I still feel that you should not expect the user to make a choice in these circumstances.

Just put yourself in the user's shoes for a moment. You have in front of you a grid containing, say, customer data. The grid is in customer name order. You decide you want it in postcode order. So you click on the header of the Postcode column. You then see the message, "Create New Index" with Yes and No buttons.

What does it mean? What do I do? Is it a question or a command? What are the implications of hitting Yes vs. No?

At the very least, the message should say something like: "Sorting this column may take some time. Are you sure you want to do this?". But even that is fairly useless, because the user has no way of knowing how long "some time" is. Is it a couple of seconds? Or an hour?

In my view, it would be better to go ahead with the indexing without consulting the user, but to give them an indication that something is happening behind the scenes, so that they won't get anxious if clicking on the column appears to have no immediate effect. That's why I suggested a Wait window, but that's not the only option. Even an hourglass would be better than a messagebox.

And this all assumes that you actually need to create the index in the first place. If you followed the advice in my first post, all the required indexes would be in place at the outset. OK, I understand that this is a generic control designed to work with an arbitrary table and indexes. But that doesn't stop you creating the indexes at design time. At that point, you will know which table you will be using to populate the grid, and therefore which indexes you will need. You can create the indexes at the point (or add code to your install program to do that).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You can't plan all indexes for queries you do, unless you simply generically index all columns of a cursor you create to be prepared for whatever header click.

I would have guessed you never encounter this message, as a usual grid display would limit the rows anyway, you rarely have anything from listing a table fully, in case you do you'd also not want to create indexes on the fly in shared mode. It's an intensive operation on the tables CDX file, it can easily corrupt the file when several other users would query using some index tags, update records and update other tags concurrently, etc., so I assume the grid is only for local temp data aka cursors. It may work on DBFs, too, as cursors are nothing else, but it's not wise to use that mode for a shared dbf at all, no matter how long or shirt the indexing takes. Index creation on backends is a design-time task for the developer. Knowing what indexes are necessary for query optimizations and sorting.

But okay, then at least I got your idea of moving to notify only.

You could do a worst-case scenario on a typical or worst client of that company and create a test cursor with millions of rows, let this grid index it and measure the times, then you could warn about that. Or- when it turns out in the worst case that needs only 5 seconds - you can completely skip the notification and simply index.

If you stay with this, of course, the message shouldn't be that generic. That's easy to do by adding a message property to the grid class and be able to override the text with something individual.

The most ideal situation would be able to cancel out of index creation. You can't, really, even introducing multithreading the best you get to is put the index on in a background thread, but you can't stop that, you only will be able to do other things in the main thread.

The user then likely still would wait, as he sorts for the reason to look for something in that order, but you could also overall rely on the intelligence of a user looking for something in a very long list by filtering rather than by sorting.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Patricia Cu]

Thank you Mike and Olaf (and everyone else) for taking the time to share your knowledge and design ideas. I appreciate it, and will certainly keep them in mind in future development. For now, I'll try to disrupt as little as possible the current design, while trying to make it more efficient and friendly. Documentation has been updated to explain the current message, and I will suggest to implement a more friendly message or if they prefer, the hour glass while waiting. I will also look into creating a test cursor with millions of rows to measure performance, as Olaf suggested.

Best regards,
Patricia