5 Replies Latest reply on Sep 14, 2008 9:32 PM by Oliver Goldman

    Why is asynchronous database access so much slower?

    wouter vdb
      Hi, I have an AIR app that loads and parses a data file (approx 170 MB) and stores the parsed data in a database using the built-in SQLite functionality. I have two implementations: one synchronous, another asynchronous, both use transactions. The synchronous one takes approx 5 minutes to complete. The asynchronous takes approx 2.5 hours. The choice thus seems to be between having an unresponsive program for 5 minutes, or having to wait too long.

      Did anyone else observer this significant difference? Is there a 'known' reason for it? Is there anything that could potentially be done to reduce the run time of the asynchronous approach?
        • 1. Re: Why is asynchronous database access so much slower?
          Oliver Goldman Adobe Employee
          Something is clearly wrong if there's that kind of differential. First up is, I think, to pin down where the extra time is being sent. Do you have an example demonstrating the issue?

          • 2. Re: Why is asynchronous database access so much slower?
            wouter vdb Level 1
            Hi,

            I made a test application that demonstrates (at least on my machines) the difference between the synchronous and asynchronous way of using the AIR database. The two approaches share the same code where possible. The interface provides two buttons to start the tests. Next to the buttons, the respective run time in ms is shown when completed.

            The AIR app can be downloaded here: http://www.addith.be/air/DBDiffDemo.air

            The FlexBuilder project here: http://www.addith.be/air/DBDiffDemo.zip

            I have tested this on two macs:

            - MacBook Pro, 2.6 Ghz Intel Core 2 Duo, 4GB 667 MHz DDR2 SDRAM, OS X 10.5.4):
            - sync: 5533ms
            - async: 237193ms

            - Mac Pro, 2 x 3 GHz Quad-Core Xeon, 16GB 800 MHz DDR2 FB-DIMM, OS X 10.5.4):
            - sync: 4044ms
            - async: 280756ms

            So, actually for some reason that's beyond me, the async mode is even slower on the desktop than on the laptop.

            I did some additional runs, and got comparable times.

            I would love to know what causes this huge difference.

            regards,

            Wouter Van den Broeck

            • 3. Re: Why is asynchronous database access so much slower?
              Oliver Goldman Adobe Employee
              Thanks for posting your code; that helps a lot. I think you'll find it instructive to watch CPU usage during the two demos. Here's what's going on.

              In the synchronous code, you are keeping the application busy 100% of the time because as soon as each insert is complete you immediately schedule the next one. That's efficient although, as you note, it makes the application unresponsive for too long a period of time.

              In the asynchronous code you are also waiting for each insert to complete before scheduling the next one. The work done behind the scenes for each insert is therefore greatly expanded:
              1) An event is posted from the UI thread to a background worker thread, scheduling the insert
              2) The background thread wakes up
              3) The insert executes on the background thread
              4) The background thread posts an event containing the results back to the UI thread
              5) The UI thread eventually processes that event, delivering it to your code

              As you can see, there's a tremendous amount of overhead to this. This is not really the way the async API is intended to be used. Instead, try one of the following strategies:

              A) Don't wait for each statement to complete before running the next one. They'll queue up and run in order, but because the background thread can process many at once, it runs much faster.

              B) Chunk your synchronous inserts, e.g., run 100 inserts every 100 msec or so. (You'll have to measure to find optimal numbers.)

              Hope this helps.

              • 4. Why is asynchronous database access so much slower?
                wouter vdb Level 1
                Thank you for your feedback. It was helpful.

                However, while working on my app I did encountered another situation. AIR systematically crashes while trying to retrieve a large amount of data from the database. It crashes regardless of the use of paging.

                I have made a demo app that systematically crashes on my laptop (MacBook Pro, 2.6 Ghz Intel Core 2 Duo, 4GB 667 MHz DDR2 SDRAM, OS X 10.5.4)

                The FlexBuilder project: http://www.addith.be/air/DBArrayDemo.zip

                What might be the cause of this problem?
                • 5. Re: Why is asynchronous database access so much slower?
                  Oliver Goldman Adobe Employee
                  It's most likely a bug in AIR. Can't tell you off-hand exactly what might be causing it, but you may be able to find a way around it if you can narrow down when the crash occurs.

                  In any case, please submit a bug report via www.adobe.com/go/wish.