On 23 Oct 2011, at 3:41pm, Fabian wrote: > 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 [snip]
My immediate question is why this is two rows in two separate tables rather than one row in one table. After all, if tables always have the same rows in, they might as well be the same row in one table. > 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. > > [snip] > > Are there any significant performances differences to be expected when > choosing the first method vs the second? The answer changes surprisingly much depending on what OS you're using and what format the disk is in. Windows, for example, suffers very badly when files are fragmented, and it does a great deal of pre-fetching, on the assumption that if you just asked for the sector S of the disk you are shortly going to want sector (S+1) of the disk. Unix speeds do not degrade as much when files are fragmented, and disk drivers generally don't do pre-fetching. So we could ask you for your OS and disk format. But even then the answer will be useful only for your exact current setup. The next time you get an OS update things might change. Manufacturers tweak this stuff all the time. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
