Loading ...
Sorry, an error occurred while loading the content.

Should Indexes be added to Taffy DB?

Expand Messages
  • tacoman_cool
    Hi everyone, I ve spent some time over the last couple of weeks trying to test and add indexes into Taffy DB. JavaScript is not designed for this type of
    Message 1 of 6 , Jun 2, 2008
    • 0 Attachment
      Hi everyone,

      I've spent some time over the last couple of weeks trying to test and
      add indexes into Taffy DB. JavaScript is not designed for this type of
      optimization so it has required a lot of brute force experimentation.
      The results have been mixed.

      Under a normal TaffyDB database it takes about 1500 milliseconds to
      load 10,000 records via JavaScript. With a unique ID column it takes
      around 200 milliseconds to retrieve a single record with a known ID.
      If you add an index you can find that single record in 8-20
      milliseconds. That is a huge improvement.

      The index works by creating blocks of records and assembling meta data
      about the column you want to search on. It knows the highest and
      lowest value within that block of records (256 by default) and so it
      can check each block to see if the record you are searching for may be
      contained within it. So instead of looking at 10,000 records Taffy DB
      only has to look at 256 to find the record in question.

      So what's the problem?

      The problem is speed of creation and maintenance of the index over
      time. Creating the index takes about a 1000 milliseconds on 10,000
      records. Given the improved speed of record lookup it is easy to
      recoup that speed in a faster application.

      Inserts also take virtually no time to handle.

      Updates? Well they aren't bad but it will double or triple the amount
      of time it will take to run updates.

      Removes? Horrible. Because the block records have to change it is more
      or less faster to rebuild the index from scratch than update it. Which
      means removing a single record could take a second.

      OrderBy? Also requires a complete rebuild of the index.

      So the basic question I have, is this worth the file size? If you only
      have 1000 records in your database the index really what improve your
      speed noticeably. If you had one or two very large collections that
      you do ID style lookups on then I think we could see an improvement.
      But that isn't a typically use case of Taffy DB.

      Thoughts? How would you want to use indexes? I hate to roll back all
      the work but after a lot of testing it becomes virtually impossible to
      maintain the index on a often updated collection, at which point it is
      faster to just do full table scans.

      Ian
    • madcitymm
      ... Hi Ian, I m new to Taffy, but what attracted me to it was that is a small but powerful application and it is not intended to be a real SQL type
      Message 2 of 6 , Jun 2, 2008
      • 0 Attachment
        --- In taffydb@yahoogroups.com, "tacoman_cool" <ian@...> wrote:
        >
        > Hi everyone,
        >
        > I've spent some time over the last couple of weeks trying to test and
        > add indexes into Taffy DB. ...........

        Hi Ian,
        I'm new to Taffy, but what attracted me to it was that is a small but
        powerful application and it is not intended to be a real SQL type
        replacement, but for use in web pages, which by definition(at least
        mine) should be keep small. So, my vote on indexes is no.
        Thanks
        Marty
      • berg.matt
        I would also vote for NO on indexes. I think keeping it lightweight is one of the real nice features of Taffy. Though you could possibly add extensions to
        Message 3 of 6 , Jun 10, 2008
        • 0 Attachment
          I would also vote for NO on indexes. I think keeping it lightweight
          is one of the real nice features of Taffy. Though you could possibly
          add extensions to TaffyDb, for users who need the extra processing.

          --- In taffydb@yahoogroups.com, "madcitymm" <marty@...> wrote:
          >
          > --- In taffydb@yahoogroups.com, "tacoman_cool" <ian@> wrote:
          > >
          > > Hi everyone,
          > >
          > > I've spent some time over the last couple of weeks trying to test and
          > > add indexes into Taffy DB. ...........
          >
          > Hi Ian,
          > I'm new to Taffy, but what attracted me to it was that is a small but
          > powerful application and it is not intended to be a real SQL type
          > replacement, but for use in web pages, which by definition(at least
          > mine) should be keep small. So, my vote on indexes is no.
          > Thanks
          > Marty
          >
        • frank_berger_software@ymail.com
          Hello Ian, well, I have not yet really any experience with Taffy and only four months in JS (after 20 yrs in pure o-o worlds). I think that any professional
          Message 4 of 6 , Dec 2, 2009
          • 0 Attachment
            Hello Ian,

            well, I have not yet really any experience with Taffy and only four months in JS (after >20 yrs in pure o-o worlds).

            I think that any professional data administration must be able to apply at least one unique ID key to a data record and that this should also be the fastest way to retrieve the related data. This ID also corresponds to the physical database behind on the server.

            Anything else, i.e. working without unique keys, is really toy playing.

            I don't think any other keys are needed for Taffy taking its form of usage into account.

            Regards
          • tacoman_cool
            That s actually how I use it when working with database. I download the relevant part of the table and import it into TaffyDB with the ID column. I think build
            Message 5 of 6 , Dec 2, 2009
            • 0 Attachment
              That's actually how I use it when working with database. I download the relevant part of the table and import it into TaffyDB with the ID column. I think build Ajax services that will let me modify those records by ID (assuming the user has permissions). But I can easily look up and link Taffy collections on the page using those database IDs.

              --- In taffydb@yahoogroups.com, "frank_berger_software@..." <frank_berger_software@...> wrote:
              >
              > Hello Ian,
              >
              > well, I have not yet really any experience with Taffy and only four months in JS (after >20 yrs in pure o-o worlds).
              >
              > I think that any professional data administration must be able to apply at least one unique ID key to a data record and that this should also be the fastest way to retrieve the related data. This ID also corresponds to the physical database behind on the server.
              >
              > Anything else, i.e. working without unique keys, is really toy playing.
              >
              > I don't think any other keys are needed for Taffy taking its form of usage into account.
              >
              > Regards
              >
            • yevgenie
              Ian- Would you be open to posting the code with the indexes? I for one would find it very useful. Thanks.
              Message 6 of 6 , Sep 8 1:47 PM
              • 0 Attachment
                Ian-
                Would you be open to posting the code with the indexes? I for one would find it very useful.
                Thanks.
              Your message has been successfully submitted and would be delivered to recipients shortly.