Importtext statement takes a long time


#1

I have a database with 95 fields, 160 procedures and 21 forms but only 300 records. I use a Drag Receiver form object to import a new 300-record text file with this code:

importtext fileload(info("dropfiles")),"ExistingData","Replace"

It takes over 60 seconds to import. I assume that the number of fields is the key factor here but it still seems very slow.

Might there be a faster way?


#2

Try closing all of the forms, and hiding half or more of the fields in the data sheet.


#3

The only form open was the one containing the Drag Receiver form object.

This is my code:

message "starting"
hidethesefields arrayrange(info("fields"),1,-2,cr())
message "hidden"
importtext fileload(info("dropfiles")),"ExistingData","Replace"
showallfields

The “starting” message was instantaneous and then it took 61 seconds to get to the “hidden” message. What else is happening (other than hiding the fields)?


#4

I’m wondering if it would help to skip the field hiding step and instead just use a simple noshow scheme.

noshow
importtext fileload(info("dropfiles")),"ExistingData","Replace"
showpage
endnoshow

#5

When displaying any kind of table, Panorama X uses an Apple class called NSTableView. This is used by the data sheet, Text Lists, Matrixes, etc. NSTableView is also used by many other programs, for example the Finder uses it to display files. It’s a workhorse of the Cocoa API’s.

Apple has gone to great lengths to optimize the performance of NSTableView for large numbers of rows. If programmed correctly, it is amazingly fast even with hundreds of thousands of rows.

However, Apple apparently did not put much work into optimizing performance for large numbers of columns – especially when adding or removing columns. Once you get past 50 columns, it starts slowing down significantly. Apple apparently doesn’t care, I don’t think they have any applications where more than 8-10 columns appear in a table.

Perhaps at some point I’ll have to write my own version of NSTableView from scratch. However, there are significant drawbacks to that idea: 1) It would probably take six months or more, 2) it would be difficult to optimize the performance with large number of rows to the degree that Apple has done, 3) It would be difficult to get the UI to exactly match Apple’s, especially as new versions of macOS come out, 4) any such large project would undoubtably have a significant number of new bugs. Bottom line, this is not a project that will be done in the near future, if ever.

So for best performance now, I would recommend keeping the data sheet to no more than about 50 visible fields. If your database has significantly more fields than that, I would set up configurations of favorite fields with no more than 50 fields, and then switching to the configuration of fields you want to see at any particular time.

That said, the performance problems are mostly related to changing the field configuration, adding and removing fields. So I’m wondering if that is happening in your example. The importtext statement will automatically add new fields as necessary if the imported text contains more fields than the database already has. So perhaps this is happening and that is causing a big slowdown. You might try using the "addfieds","no" option and see if that makes it faster – if so, you might want to make sure to add the fields you need in advance, rather than letting the import do it.

Another option to try would be to simply close the data sheet, leaving just your form with one object.


#6

Wow. Great info.

One of the files in my one of my file sets that has 7 files, has 153 fields. Not all of the tables are this complex but certainly they are not small. Apparently this has significantly contributed to the slowdown that I was waiting to get back for with a more complete optimization of Panorama X. It seems that Panorama 6 did not suffer from the same issues with a great number of fields. Clearly it is time for some spring cleaning that will get me a better place. Several fields can be eliminated, and many can be hidden in ordinary situations. Onward to a more speedy Panorama X.

Thank you for the sharing.


#7

Forget that idea.

No - the incoming file has the correct number of fields, all of matching types. In fact, the database always contains only the incoming text (Stock Exchange data updated daily).

Good one Gary - that cuts the time to five seconds!


#8

That does not make ANY sense at all.

Using noshow is helpful when you are going to perform multiple operations that update the display. In that case, it is a waste of time to display intermediate results when you are just going to update the display again a moment later.

In this case, importtext already only updates the display once. So using noshow won’t make any difference at all.

There’s something else going on here if noshow is having this effect.


#9

That was my thought right from the beginning but only you can find out what it is.


#10

If you want to send me the database and a text file, I will investigate it.


#11

It was obviously the above line that was adding the overhead as your test showed. So according to Jim, you should get the same 5 sec. result just running the importtext line by itself.


#12

Yes, but in the first post on this thread he said that using just that line by itself took over a minute.


#13

The hidethesefields statement slows it down considerably.

If I use noshow and endnoshow, it runs through to the final statement (which tells me how long it took), takes zero or 1 second, and doesn’t import the text file.

If I use noshow and show, it imports the file almost instantaneously, even though show and endnoshow are supposed to be identical.

Without the show etc, it takes 63 seconds and, interestingly, 25 seconds into that time, an image of the text file jumps from the Text receiver box back to the Finder window that it came from.

I’ve sent the data to Jim to check out.


#14

No. They are not supposed to be identical. EndNoShow simply means that it will start showing the changes, beginning with the next change. It’s not a call to redraw the window.


#15

True - I read the first half of this sentence but missed the second half:

“Note: This statement is identical to the endnoshow statement followed by the showpage statement.”


#16

Today, I see no difference between show and endnoshow or even show followed by showpage. They are all very fast and I’m unable to recreate my earlier problem.