InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples
FAQs

Software
Downloads
Documentation
CVS Repositories
Roadmap
License

About Enhydra.org
Who We Are
News, Articles & Events
Getting Involved
Contact Us

Community
Demos
Contributions
Resources
Case Studies
On The Edge! -NEW-
Commercial Vendors

I have a 4 tables, the first with a one-element key and each of the 
others having the key of the table before plus one element.

That is
table t1: primary key k1
table t2: primary key k1, k2
table t3: primary key k1, k2, k3
table t4: primary key k1, k2, k3, k4

I want to join  those four tables and get all the records for a 
particular k1 value.

Since InstantDB doesn't support indexed range matches (or partial key 
matches), I added an index to t2, t3, & t4 on the values to match from 
the higher table.

That is
table t2: non-unique index on k1
table t3: non-unique index on k1, k2
table t4: non-unique index on k1, k2, k3

Then I do a join with the following where clause:
  where t1.k1 = <some key value>
   and t2.k1 = t1.k1
   and t3.k1 = t2.k1
   and t3.k2 = t2.k2
   and t4.k1 = t3.k1
   and t4.k2 = t3.k2
   and t4.k3 = t3.k3

It took 11.109 seconds to insert 50 t1 records + associated t2, t3, & t4 
records.
It then took 162.328 seconds to select the 96 records associated with 
one k1 value.

By contrast, the same query against a similar but commercial pure-Java 
database took 0.335 seconds.
(It did take that database 18.382 seconds for the inserts, although when 
you go to 150 t1 records the lead drops idb=48sec, pb=53sec).

The same query in InstantDB with 150 t1 reords took over 32 minutes.

I've attached a short Java program that creates the tables, inserts the 
records, and does the select (it should only need a test.prp file in the 
same dir). Please let me know if this is due to a flaw in my code or if 
it's just a limitation of InstantDB.

Thanks,

Scott Plante

import java.sql.*;
import java.util.*;

public class X
{
  private static Driver d = new org.enhydra.instantdb.jdbc.idbDriver();

  public static int MAX_CONF = 50;
  public static int MAX_PART = 6;

  public static void main(String args[])
      throws Exception
  {
    long startTime = 0;
    long insertTime = 0;
    long endTime = 0;
    
    Connection con = null;
    Statement stmt = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try
    {
      con = DriverManager.getConnection("jdbc:idb=test.prp","","");
      stmt = con.createStatement();

      startTime = System.currentTimeMillis();

      stmt.execute("DROP TABLE t1");
      stmt.execute("DROP TABLE t1");
      stmt.execute("DROP TABLE t2");
      stmt.execute("DROP TABLE t3");
      stmt.execute("DROP TABLE t4");
      stmt.execute("CREATE TABLE t1 ("
          + " k1 long,"
          + " data1 char(128) )"
          + " UNIQUE (k1)"
          + " PRIMARY KEY (k1)");

    
      stmt.execute("CREATE TABLE t2 ("
          + " k1 long,"
          + " k2 char(10),"
          + " data2 char(128) )"
          + " UNIQUE (k1,k2)"
          + " PRIMARY KEY (k1,k2)");

      stmt.execute(" CREATE TABLE t3 ("
          + " k1 long,"
          + " k2 char(10),"
          + " k3 char(10),"
          + " data3 char(128) )"
          + " UNIQUE (k1,k2,k3)"
          + " PRIMARY KEY (k1,k2,k3)");

      stmt.execute(" CREATE TABLE t4 ("
          + " k1 long,"
          + " k2 char(10),"
          + " k3 char(10),"
          + " k4 char(10),"
          + " data4 char(128) )");

      stmt.execute(" CREATE INDEX t2idx1 on t2"
          + " (k1)");
      stmt.execute(" CREATE INDEX t3idx1 on t3"
          + " (k1,k2)");
      stmt.execute(" CREATE INDEX t4idx1 on t4"
          + " (k1,k2,k3)");


      ps = con.prepareStatement("insert into t1 values ( ?, ? )");
      for (int i=0;i<MAX_CONF;i++)
      {
        if (i%10==0)
          System.out.println("t1: i="+i);
        ps.setLong(1,i);
        ps.setString(2,"CONFERENCE NUMBER " + i 
            + "  Abcdefghijk lmno pqrst uvwxyz 12 345 678 90");
        ps.execute();
      }

      ps = con.prepareStatement("insert into t2 values ( ?, ?, ? )");
      for (int i=0;i<MAX_CONF;i++)
      {
        if (i%10==0)
          System.out.println("t2: i="+i);
        for (int j=0;j<MAX_PART;j++)
        {
          ps.setLong(1,i);
          ps.setString(2,"J"+j);
          ps.setString(3,"CONFERENCE NUMBER " + i 
              + " PARTICIPANT NUMBER " + j
              + "  Abcdefghijk lmno pqrst uvwxyz 12 345 678 90");
          ps.execute();
        }
      }

      ps = con.prepareStatement("insert into t3 values ( ?, ?, ?, ? )");
      for (int i=0;i<MAX_CONF;i++)
      {
        if (i%10==0)
          System.out.println("t3: i="+i);
        for (int j=0;j<MAX_PART;j++)
        {
          for (int k=0;k<cts(k);k++)
          {
            ps.setLong(1,i);
            ps.setString(2,"J"+j);
            ps.setString(3,"K"+k);
            ps.setString(4,"CONFERENCE NUMBER " + i 
                + " PARTICIPANT NUMBER " + j
                + " CT NUMBER " + k
                + "  Abcdefghijk lmno pqrst uvwxyz 12 345 678 90");
            ps.execute();
          }
        }
      }

      ps = con.prepareStatement("insert into t4 values ( ?, ?, ?, ?, ? )");
      for (int i=0;i<MAX_CONF;i++)
      {
        if (i%10==0)
          System.out.println("t4: i="+i);
        for (int j=0;j<MAX_PART;j++)
        {
          for (int k=0;k<cts(k);k++)
          {
            for (int l=0;l<cts(l);l++)
            {
              ps.setLong(1,i);
              ps.setString(2,"J"+j);
              ps.setString(3,"K"+k);
              ps.setString(4,"L"+l);
              ps.setString(5,"CONFERENCE NUMBER " + i 
                  + " PARTICIPANT NUMBER " + j
                  + " CT NUMBER " + k
                  + " CON NUMBER " + l
                  + "  Abcdefghijk lmno pqrst uvwxyz 12 345 678 90");
              ps.execute();
            }
          }
        }
      }

      insertTime = System.currentTimeMillis();
      System.out.println("Insert done, ms="
          + (insertTime - startTime));

      rs = stmt.executeQuery("select "
          + " t1.k1,"
          + " t2.k2,"
          + " t3.k3,"
          + " t4.k4,"
          + " data1,"
          + " data2,"
          + " data3,"
          + " data4"
        + " from t1, t2, t3, t4"
        + " where t1.k1 = " + (MAX_CONF / 2)
          + " and t2.k1 = t1.k1"
          + " and t3.k1 = t2.k1"
          + " and t3.k2 = t2.k2"
          + " and t4.k1 = t3.k1"
          + " and t4.k2 = t3.k2"
          + " and t4.k3 = t3.k3"
        + " order by t1.k1, t2.k2, t3.k3, t4.k4");

      int recCtr = 0;
      while (rs.next())
      {
        recCtr++;
        System.out.println(
            "CONF " + rs.getLong(1)
            + " PT " + rs.getString(2)
            + " CT " + rs.getString(3)
            + " Cn " + rs.getString(4)
            + " d4 " + rs.getString(8));
      }

      endTime = System.currentTimeMillis();
      System.out.println("Insert time, ms="
          + (insertTime - startTime));
      System.out.println("Select time, ms="
          + (endTime - insertTime)
          + ", record count="
          + recCtr);
    }
    finally
    {
      try
      { if (rs!=null) rs.close(); }
      catch (SQLException ex)
      { ex.printStackTrace(); } 

      try
      { if (ps!=null) ps.close(); }
      catch (SQLException ex)
      { ex.printStackTrace(); } 

      try
      { if (stmt!=null) stmt.close(); }
      catch (SQLException ex)
      { ex.printStackTrace(); } 

      try
      { if (con!=null) con.close(); }
      catch (SQLException ex)
      { throw ex; } 
    }
  }

  public static int cts(int i)
  {
    int j = (i % 10);
    if (j > 3)
      return 1;
    return j + 2;
  }
  public static int cons(int i)
  {
    int j = (i % 10);
    if (j > 2)
      return 1;
    return j + 2;
  }
}