> > > No, a UNIQUE index and a regular index are implemented the exact same way. > It's just that, at INSERT and UPDATE time, after finding a proper place to > insert the new value, an additional check is made that the place isn't > already occupied. >
So the only overhead for UNIQUE is that extra check? > I suspect the way you are going to manually filter duplicates will involve > inserting them into a moral equivalent of a UNIQUE index. The performace is > probably going to be similar to that of just creating a UNIQUE index up > front. But only measurements with your particular data can tell for sure. > I was planning using the HashList from the .Net framework, which should be the fastest way to do it. If SQLite only has to check if a certain position is occupied, it should outperform creating a hash for each value. What got me worried was the post "Slow insertion for Unique Text Column" to this mailinglist, I quote: ----------- I was creating a new table and populating it with 100,000 rows of data (as a test case; I really wanted to populate it with over a million rows). [Insertion A] When a Text Column was NOT Unique it would take: 8875 ms = ~9 seconds [Insertion B] When a Text Column was Unique it would take: 155781 ms = ~156 seconds ------------- The difference between 9 and 156 seconds is too large for me. The original author solved this by pre-sorting the data for the indexed column, which made the difference 9 to 12 seconds (including the pre-sort), which is very acceptable. So why does SQLite not pre-sort the data itself, when doing such large batch inserts inside a transaction? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
