Timer Object and single thread

[dpayer]

I have an import mechanism built for a database. The backup is 8000 records so using the NB command to import from a CSV file takes 2-3 minutes to complete.

I tried to put an update on a custom window indicating number of records in the table at that moment using the special variable [ID.Table.$RecCount]. I included this var in a SimpleText object and I use a timer to refresh it (I have tried 1 second and 10 seconds). It should show how far along the import has gone. I can see similar results using a page from PHPMyAdmin and verify the process is a bit slow.

The timer seems to fire at best one time during the import. I am guessing it is because it can't do its count when the import command is working.

Is there a way to have two things happen at the same time in NB or is this single thread issue a real limit? Example: should a counter be able to set a variable WHILE another command is in process?

[Gaev]

David Payer:

This suggestion might actually take longer to complete ... but receiving constant feedback of progress might be preferable than what you describe.

1) FileRead or FileToVar to load the csv file contents to a NeoBook Variable

2) StrParse to separate each line (record) to an element of an arrayed variable

3) Inside a Loop/EndLoop block, do a dbpVarToRecord to add one record at a time; after each addition (or perhaps every 10 or 100), update a Text Box with the Loop Counter.

Should you choose to take on this assignment, please post the elapsed times experienced with each step.

[dpayer]

I tried your suggestion Gaev. The process of parsing 8000 records takes over 5 minutes itself (actually about 2-3 in a compiled app). THEN it adds in records one by one which take about 1/3 to 1/2 second per record.

I think it may be more feasible to rely on PHPMyAdmin to do my backups. They can export the database in 15 seconds total and import it in 30 seconds, that includes all the tables not just this big one. I'll put in a webbrowser object to facilitate this process.

This is a case of "using the right tool for the job".

Thanks for your suggestion.

[Gaev]

David payer:

Thanks for sharing the information regarding "elapsed times".

I think it may be more feasible to rely on PHPMyAdmin to do my backups. They can export the database in 15 seconds total and import it in 30 seconds, that includes all the tables not just this big one.

Would it be any faster if you used dbpExecSQL ? ... i.e.

1) SetVar "[mySQL Command]" "something like text below"

LOAD DATA INFILE "/full/path/data.csv"
INTO TABLE yourDataTable
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

2) dbpExecSQL "yourDB" "[mySQLCommand]" "Temp1"



Another possibility would be to "export sets of 500 or 1000 records" into separate csv files ... then, while doing the import, you could update the status after "each import of the smaller subset file".

[dpayer]

I may try those things later. The LOAD DATA INFILE process looked promising when I was running the MySQL on the localhost. On a network server, I would have to locate the file in a particular folder via FTP or some other process and that was a bit of a headache on the network server we're using.

I think if I *needed* to measure the process as it happened, I'd break up the file and import smaller bits. I did run into one small point. When adding new records from the backup the ID field could not be inserted as it was autoincrement. I'd have to rid the backup data of that first column OR make the column CHAR and then change it later to AUTOINC.

For now, Im satisifed with the PHPMyAdmin method to data backup/restore. Thanks for your input.

[Wrangler]

WAMP?

[dpayer]

Yes using WAMP, but you can easily backup from wamp to a LAMP system and it works fine.

D