extending my own reply... On 10/26/08, P Kishor <[EMAIL PROTECTED]> wrote: > 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>
The table MY_TABLE seems to have nothing to do with the table CAN_MESSAGES. What is your CAN_MESSAGES table storing? Maybe that explains the big db file size. In any case, note that all integer fields get stored as, well, integers "stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value" and the double field gets stored as real which is 8 bytes. Of course, the char(8) gets stored as a text field, and will happily take whatever you put into it without complaining. -- 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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
