Optimising saving to an sqlite DB?

Question

I have an as3 app which utilises an sqlite db to save metrics data. It is well known that as3 is single threaded, so when I save to this db, the Stage Video I am playing becomes jumpy! I have heard that saving db data in batches can speed up this process so I save in a loop:

for (var i:int=0; i < metricsObject.metricsComponentData.length; i++){
    switch (metricsObject.metricsComponentData[i].mouseType) {
        case MetricsCollator.MOUSE_OVER:
            this._query_txt = "INSERT INTO " + this._tablePath[0] + " VALUES (null, " + this._sessionID + 
                                                                    ", " + metricsObject.metricsComponentData[i].timeStamp + 
                                                                    ", '" + metricsObject.metricsComponentData[i].component + "'" + ")";
                    break;
        case MetricsCollator.MOUSE_OUT:
            this._query_txt = "INSERT INTO " + this._tablePath[1] + " VALUES (null, " + this._sessionID + 
                                                                    ", " + metricsObject.metricsComponentData[i].timeStamp + 
                                                                    ", '" + metricsObject.metricsComponentData[i].component + "'" + ")";
                    break;
       } // end switch
    executeQuery(this._query_txt);
} // end loop

I have looked into pseudo threading examples but they all require a sprite or stage instance, the class I am executing this code in, is not a display class, I don't really want to pass an instance of the stage into this class, seems dirty! ;-)

Anyone have any ideas?

Answer 1

Most examples of pseudo threading use a display object and an enter frame listener, but you can just as well use a Timer, or if your application has some kind of update loop that works too.

Here's a simple example using a Timer:

package {
    import flash.display.Sprite;
    import flash.events.TimerEvent;
    import flash.utils.Timer;

    public class PseudoThreadExample extends Sprite {

        private var _timer:Timer;

        public function PseudoThreadExample(){
            // create a timer that runs with a low interval
            // tweak this to fit your needs!
            _timer = new Timer(30, 0);
            _timer.addEventListener(TimerEvent.TIMER, handleTick);
        }

        public function startWorking():void {
            // put work to be done in list here
            // in your case that will probably mean making an array of SQL-
            // statements to run later
            _timer.start();
        }

        public function handleTick(event:TimerEvent):void {
            // pop a thing off the list and do it.
            // if list is empty, stop the timer
            _timer.stop();
        }
    }

}