Best Way - Sql, Lists or Map (or other)?

[Harris]

I have 1000 (or more) records in an SQL table for the last 60 days.

I fetch the last 30 days worth and put them in a temp SQL table.

I then traverse the file and insert midnite time stamps for each day, since every day must begin and end with a midnite record.

I then traverse the same file and update each record - setting a duration field (time between each record).

It all works fine using SQL as a table, but is there a faster way to perform this? It does seem slow on my 1 Ghz processor (10 to 12 seconds).

I then use this temp table to present a tableview (rows and columns) showing each days restricted data.

Thanks

MHW

 

[Erel]

Are you explicitly using a single transaction for all the inserts and updates statements?

 

[Harris]

No I am not and remember your recommendation to use transactions for many writes... I will try this and see if it speeds it up.

Thanks for the reminder.

 

[Erel]

The writing operations should be about 100 times faster.

 

[Harris]

Not only did it speed it up, I removed the ProgressShow and the DoEvents to make The Progess Show!! It takes longer to load the layout than it does to process these records. Unbelievable..

Thanks Erel. It hard to remember these fine point until reminded..

 

[Erel]

It hard to remember these fine point until reminded..

For that reason it is recommended to use DBUtils. DBUtils makes common SQL tasks simpler.