3 Replies Latest reply on Feb 8, 2008 11:15 AM by Newsgroup_User

    Slow handler

    Level 7
      I am experimenting with Valentin Schmidt's free SQLite xtra
      http://staff.dasdeck.de/valentin/xtras/sqlite/ (Kudos to him for making
      this BTW)!

      I have a table in my DB with a column named displayOrder, which holds
      integers. Basically if there are 20 rows, then one row's displayOrder
      will be 1, another will be 2, another 3,...through 20 (in no particular
      order). The displayOrder determines the order that the rows of data will
      be displayed in my Director program.

      So I wrote a handler that changes a row's displayOrder. With it, I can
      change the displayOrder of a row from say, 1 to 20. The problem is that
      it is slow. A table with just 20 rows takes 3 or 4 seconds to update if
      I change the row who's displayOrder is 1 to 20. I realize it is issuing
      21 SQL Update queries in this example, but I wouldn't expect them to
      take hardly any time at all.

      Am I being unrealistic about the time? Is there a flaw in my handler? s
      there a better way to do this?


      on changeDisplayOrder dbFile, tableName, oldDisplayOrder, newDisplayOrder
      if oldDisplayOrder <> newDisplayOrder then
      sx = xtra("SQLite").new()
      sx.sqlite_open(dbFile)
      err = []
      sql = "SELECT MAX(displayOrder) from " & tableName
      nextDisplayOrder = value(sx.sqlite_fetch(sql, 2, err)[1][1]) + 1
      sql = "UPDATE " & tableName & " SET displayOrder = " &
      nextDisplayOrder & " WHERE displayOrder = " & oldDisplayOrder
      sx.sqlite_exec(sql, 0, err)
      if oldDisplayOrder > newDisplayOrder then
      repeat with j = oldDisplayOrder - 1 down to newDisplayOrder
      sql = "UPDATE " & tableName & " SET displayOrder = " & j + 1 &
      " WHERE displayOrder = " & j
      sx.sqlite_exec(sql, 0, err)
      end repeat
      else
      repeat with j = oldDisplayOrder + 1 to newDisplayOrder
      sql = "UPDATE " & tableName & " SET displayOrder = " & j - 1 &
      " WHERE displayOrder = " & j
      sx.sqlite_exec(sql, 0, err)
      end repeat
      end if
      sql = "UPDATE " & tableName & " SET displayOrder = " &
      newDisplayOrder & " WHERE displayOrder = " & nextDisplayOrder
      sx.sqlite_exec(sql, 0, err)
      sx.sqlite_close()
      sx = void
      else
      -- oldDisplayOrder = newDisplayOrder, display order isn't really
      changing, so do nothing
      end if
      end
        • 1. Re: Slow handler
          Level 7
          How large is your DB (size on disk)? Does it help if you compact it? Did
          you try adding some timing info to help benchmark the handler and
          identify the slow spot?
          --
          on changeDisplayOrder dbFile, tableName, oldDisplayOrder, newDisplayOrder
          ms = the milliseconds
          if oldDisplayOrder <> newDisplayOrder then
          ms = the milliseconds
          sx = xtra("SQLite").new()
          sx.sqlite_open(dbFile)
          t1 = the milliseconds - ms
          ms = the milliseconds
          err = []
          sql = "SELECT MAX(displayOrder) from " & tableName
          t2 = the milliseconds - ms
          ms = the milliseconds
          nextDisplayOrder = value(sx.sqlite_fetch(sql, 2, err)[1][1]) + 1
          sql = "UPDATE " & tableName & " SET displayOrder = " &
          nextDisplayOrder & " WHERE displayOrder = " & oldDisplayOrder
          t3 = the milliseconds - ms
          ms = the milliseconds
          sx.sqlite_exec(sql, 0, err)
          if oldDisplayOrder > newDisplayOrder then
          nChanged = oldDisplayOrder - newDisplayOrder
          repeat with j = oldDisplayOrder - 1 down to newDisplayOrder
          sql = "UPDATE " & tableName & " SET displayOrder = " & j + 1 &
          " WHERE displayOrder = " & j
          sx.sqlite_exec(sql, 0, err)
          end repeat
          else
          nChanged = newDisplayOrder - oldDisplayOrder
          repeat with j = oldDisplayOrder + 1 to newDisplayOrder
          sql = "UPDATE " & tableName & " SET displayOrder = " & j - 1 &
          " WHERE displayOrder = " & j
          sx.sqlite_exec(sql, 0, err)
          end repeat
          end if
          t4 = the milliseconds - ms
          ms = the milliseconds
          sql = "UPDATE " & tableName & " SET displayOrder = " &
          newDisplayOrder & " WHERE displayOrder = " & nextDisplayOrder
          sx.sqlite_exec(sql, 0, err)
          t5 = the milliseconds - ms
          ms = the milliseconds
          sx.sqlite_close()
          sx = void
          t6 = the milliseconds - ms

          put "initialisation:", t1
          put " MAX:", t2
          put " first UPDATE:", t3
          put "UPDATE changed:", t4, nChanged, float(t4)/nChanged
          put " final UPDATE:", t5
          put " shutdown:", t6
          else
          -- oldDisplayOrder = newDisplayOrder, display order isn't really
          changing, so do nothing
          end if
          end
          • 2. Re: Slow handler
            Level 7
            Problem solved!

            I first rewrote my algorithm, trying to make it as efficient as
            possible. I didn't help. Even though my test table is small (20 rows) it
            was taking about 4 seconds to execute. So then I started researching
            about databases and learned about something called transactions.

            If I understand it correctly, each transaction introduces a certain
            amount of 'overhead'. Since each UPDATE was it's own transaction, this
            overhead was being multiplied.

            Then I learned that by first issuing a BEGIN TRANSACTION statement, all
            of the UPDATE commands get issued as a single transaction, thus
            significantly reducing the overhead. After all of the UPDATES are
            issued, you issue an END TRANSACTION command.

            Once I added this the time went from 4 seconds to 2/10 of a second, big
            difference! I can see where this wouldn't always be a good way to go,
            but in this case where each UPDATE was guaranteed to be affecting a
            different row, it is perfect. SQLite has limited support for
            transactions (such as no support for nested transactions), unlike more
            advanced databases. Still, so far I am finding SQLlite (and specifically
            the SQLite Xtra) to be great, especially considering it is free!

            My new handler:

            on changeDisplayOrder dbFile, tableName, oldDisplayOrder, newDisplayOrder
            if oldDisplayOrder <> newDisplayOrder then
            sx = xtra("SQLite").new()
            sx.sqlite_open(dbFile)
            err = []
            sql = "SELECT id,displayOrder from " & tableName & " WHERE
            displayOrder >= " & min(oldDisplayOrder, newDisplayOrder) & " AND
            displayOrder <= " & max(oldDisplayOrder, newDisplayOrder)
            data = sx.sqlite_fetch(sql, 1, err)
            repeat with j in data
            if j.displayOrder = oldDisplayOrder then
            j.displayOrder = "x"
            else
            if oldDisplayOrder > newDisplayOrder then
            j.displayOrder = string(value(j.displayOrder) + 1)
            else
            j.displayOrder = string(value(j.displayOrder) - 1)
            end if
            end if
            end repeat
            repeat with j in data
            if j.displayOrder = "x" then
            j.displayOrder = string(newDisplayOrder)
            exit repeat
            end if
            end repeat
            sql = "BEGIN TRANSACTION"
            sx.sqlite_exec(sql, 0, err)
            put err
            repeat with j in data
            sql = "UPDATE " & tableName & " SET displayOrder = " &
            j.displayOrder & " WHERE id = " & j.id
            sx.sqlite_exec(sql, 0, err)
            end repeat
            sql = "END TRANSACTION"
            sx.sqlite_exec(sql, 0, err)
            else
            -- oldDisplayOrder = newDisplayOrder, display order isn't really
            changing, so do nothing
            end if
            end
            • 3. Re: Slow handler
              Level 7
              Opps, forgot to close the DB and dispose of the xtra instance

              on changeDisplayOrder dbFile, tableName, oldDisplayOrder, newDisplayOrder
              if oldDisplayOrder <> newDisplayOrder then
              sx = xtra("SQLite").new()
              sx.sqlite_open(dbFile)
              err = []
              sql = "SELECT id,displayOrder from " & tableName & " WHERE
              displayOrder >= " & min(oldDisplayOrder, newDisplayOrder) & " AND
              displayOrder <= " & max(oldDisplayOrder, newDisplayOrder)
              data = sx.sqlite_fetch(sql, 1, err)
              repeat with j in data
              if j.displayOrder = oldDisplayOrder then
              j.displayOrder = "x"
              else
              if oldDisplayOrder > newDisplayOrder then
              j.displayOrder = string(value(j.displayOrder) + 1)
              else
              j.displayOrder = string(value(j.displayOrder) - 1)
              end if
              end if
              end repeat
              repeat with j in data
              if j.displayOrder = "x" then
              j.displayOrder = string(newDisplayOrder)
              exit repeat
              end if
              end repeat
              sql = "BEGIN TRANSACTION"
              sx.sqlite_exec(sql, 0, err)
              repeat with j in data
              sql = "UPDATE " & tableName & " SET displayOrder = " &
              j.displayOrder & " WHERE id = " & j.id
              sx.sqlite_exec(sql, 0, err)
              end repeat
              sql = "END TRANSACTION"
              sx.sqlite_exec(sql, 0, err)
              sx.sqlite_close()
              sx = void
              else
              -- oldDisplayOrder = newDisplayOrder, display order isn't really
              changing, so do nothing
              end if
              end