I have two tables, both containing 1 million rows, which frequently need to
be joined by rowid. Right now, the insert loop is like this:
For I = 1 to 10000000
INSERT INTO TABLE1 ...
INSERT INTO TABLE2 ...
Next
When I look at the structure of the created database-file, the rows for the
two tables are in alternating pattern. At first I thought this was a good
sign, because when the two rows needs to be joined, they are very close to
eachother on disk.
An other way to do the inserts would be:
For I = 1 to 10000000
INSERT INTO TABLE1 ...
Next
For I = 1 to 10000000
INSERT INTO TABLE2 ...
Next
Now, the actual data of the two rows, are not close to eachother on disk,
but the structure looks very clean and not fragmented.
Are there any significant performances differences to be expected when
choosing the first method vs the second? I guess that using the first method
JOINS will be faster (because the data is close), but SELECTs on a single
table will be slower (because the rows are scattered around the file), but I
don't know enough about the internals of SQLite to know if that's true.
So what is generally more preferable, or doesnt it make any difference?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
