On 10/26/08, Julian Bui <[EMAIL PROTECTED]> wrote: > HI everyone, > > I have records in my db that consist of smallint, bigint, smallint, double, > char(8). By my calculation that comes to 2 + 8 + 2 + 8 + 8 = 28 Bytes per > record. I also have an index over the attribute that is a double.
Why not start with reading TFM <http://www.sqlite.org/datatype3.html> "2.1 Determination Of Column Affinity The type affinity of a column is determined by the declared type of the column, according to the following rules: If the datatype contains the string "INT" then it is assigned INTEGER affinity. If the datatype of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity. If the datatype for a column contains the string "BLOB" or if no datatype is specified then the column has affinity NONE. If the datatype for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity Otherwise, the affinity is NUMERIC." What you expected was not what you got. That was expected. > > I inserted 100,000 records into a clean database and the database grew to > 240.0MB. That comes out to 2519 Bytes per record (240 * 1024 * 1024 / > 100,000). How is that possible? The index can't add THAT MUCH extra space. > > Results were duplicated when I started with a clean database and inserted > other # of records. > > This is very surprising. I must be doing something wrong. > > I'm attaching some code. Also to note, I'm using the xerial JDBC driver to > do this in java. > > If anyone knows what may be wrong I'd love to hear feedback. > > Thanks, > Julian > > <code> > //ps is a prepared statement > ps = conn.prepareStatement("CREATE table IF NOT EXISTS MY_TABLE(aaa SMALLINT > NOT NULL, bbb BIGINT NOT NULL, ccc SMALLINT, ddd DOUBLE, eee CHAR(8));"); > ps.execute(); > > .... > > ps = conn.prepareStatement("CREATE INDEX IF NOT EXISTS IDX_TIME on > CAN_MESSAGES (timeStamp)"); > ps.execute(); > > ... > > dataInsertPs = conn.prepareStatement("INSERT into CAN_MESSAGES(lru, pgn, > bus, timeStamp, messageData) VALUES (?, ?, ?, ?, ? > //every dataInsertPs gets added to a batch and committed every 1000 records > > </code> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
