On Thu, Jun 26, 2008 at 11:58:21AM -0700, Brooks, Phil scratched on the wall:
> Hi, I am new to the list and to using sqlite - I am looking at
> using sqlite for a 2 way lookup table for my application.
> I have a home grown file based solution, but was hoping to
> find better performance and more functionality by putting
> the lookup tables into sqlite.
Lots of little things:
For what you're working on, I think the pair of single indexes
(rather than the compound index) is what you want.
As others have said, don't create the indexes until you're done
importing the data.
Before you create the indexes, up the page cache as much as you can.
The default page size is 1K, but takes about 1.5K of RAM. The
default page cache is 2000, or 3MB. Set the cache size appropriately
for the amount of physical RAM available on your machine. This won't
have a huge impact on the import times, but it will make a big
difference for the index creation.
If you know the *EXACT* strings you will be looking up, create
additional hash value columns for the strings and index those.
Since indexes have a full copy of the data they index, this will
make your indexes much smaller.
During the creation process turn off syncing and journaling and all
the other safety nets. Especially syncing.
If you decide to do your own importer, wrap groups of INSERT statements
into transactions. Typical values are ~1000 statements or so per
transaction.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"'People who live in bamboo houses should not throw pandas.' Jesus said that."
- "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
