Quite wrong. Searching a B-Tree is relatively inexpensive but node splits are expensive.
Inserting a non-terminal key in a part filled leaf node is cheap, inserting a terminal key is more expensive and a split is more expensive again The reason we spend the extra resources maintaining B-tree indices is because they maintain the keys in sorted sequence. If maintaining keys in order is not required a hashing method can be faster. Our fastest B-Tree indices use the virtual memory capability of the OS as cache and perform very well.by avoiding buffer shadowing and maximizing utilization of physical memory.. Kosenko Max wrote: > Expenses in B-Tree not in the node splitting (that is really not that often > and takes small amount of time). As I've said - it's in finding right place > to insert. > > Root level which takes 1 page will do the same as your hash index. And will > use much less space in cache. This root page in such DB will always be in > cache. So you won't gain any advantage at all. And multi-threading also > won't use the benefit of multiply tables. At least in SQLite. > > That method called partitioning. It gives advantages when partitions divided > by some logic and there is a high chance to hit fewer partitions in average. > It also can benefit a bit in case RDBMS supports real parallel execution and > you have a lot of hard drives. That is not the case with SQLite (well you > can compile without thread safety and try to do own locks). > > I have actually posted a real proposal to make DB much faster. That will > work. > Proposal with 100 tables as a hash buckets doesn't works and I've checked > that a lot of time ago. > You have a sample where it works and gives any visible benefit? I'd like to > see that. > > My another addition to proposal is to use SSD with as small as possible > average access time. Some of them can easily do 50-100x faster. And that > will give 20-50x times faster inserts. > > Thank you. > Max. > > > John Stanton-3 wrote: > >> This technique is used extensively in disk cacheing and in maintaining >> file directories with huge numbers of files.. >> >> I would expect it toincrease key insertion speed because it removes a >> level of index in the B-tree of each index. The expensive activity in a >> B-tree index insertion is a node split which requires that key >> information be updated in each internal node level and possibly a new >> level added. Fewer levels mean faster performance. >> >> This method could also be used to add parallelism by having multiple >> threads or processes perform insertions concurrently. Having each >> database in a separate databases would help this approach. >> It would also help with concurrent read accesses. >> >> If this application only has one table and does not need SQL then there >> are better solutions than using Sqlite and paying the price for its many >> features but not using them. >> > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
