On 1/19/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
This is probably a locality of reference problem. Try keeping an indexed version of the database on disk, but when you do your batch insert, do it in sorted order. CREATE TEMP TABLE toadd(str TEXT); -- insert 300-400K rows into toadd. INSERT INTO maintable SELECT str FROM toadd ORDER BY str; Selecting a larger page size such as 16K or 32K might also help.
I've recreated db to use 32k page size instead of 4k that it used previously. I've then inserted all the data in sorted order into the newly created db. And then I created a unique index on that data and was immediately stunned how fast index was created compared to my past experiences with indexing unsorted data of this size. Adding to that, batch inserting 400k sorted rows took almost no time, a minute maybe. Fantastic!
Running the queries in sorted order will also possibly help. I think it is at least worth a try.
But then came the time to do a lot of lookups. Millions. And that was running for a long time before I stopped it. I didn't have a chance to run SELECTs in sorted order yet since it will require some modifications to be made in my code, but I intend to try it eventually. Satisfying solution here seems to be to copy disk-db to memory in sorted order, index it and run a lot of lookups there. This simple change resulted in bottom line time dropping to almost 1/3 of what it used to be. Excellent gain for very little code change. Richard, thank you very much. Regards -- Nemanja Corlija <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
