Good URLs you presented. To me from a performance POV the INSERT of the GUID is the only downside with respect to the index. It has to identify the page and add itself to there. If need be it will tear the page and generate two pages with access holes to accept new index-data going forward. Next. you look at the type of data you are presenting via a velocity of inserts. Are your inserts per min to a table > 10,000? If so the GUID may be the wrong thing. Think of eBay in the closing seconds of an auction, or your stock trader in changes in the market generating A LOT of transactions. These situations are where next int is best because it always going to the last page of the index.
If you are not in that type of data environment you can do either with no problem. M$ loves using GUID in their internal systems like CRM or SharePoint. It is Massive GUID driven in all of the tables. On Mon, Oct 30, 2017 at 12:42 PM, < [email protected]> wrote: > On 2017-10-30 11:09, Stephen Russell wrote: > >> Less efficient indexes? Are you talking about space in a db compared to >> an >> int for a pointer or are you saying that the time to isolate the data on >> that row because of the data type of the pointer? The flip side is data >> insertion. >> >> Can you tell us why you use less efficient? >> >> > > Not sure of your wording, if you meant exactly that or not, so let me try > to respond: > > I like the guid v(40) indexes because if ever I needed to combine data, > I'm not running into duplicate keys. Plus, I like defining the key ahead > of time and having complete control so I can work with > parent/child/grandchild datasets easier (than if I had to contend with > auto-inc keys). The negative of this approach as I understood it is that > the since the index is 4x larger in size than a 4-byte integer key, it > would not be as efficient in memory, and the index tree needs reindexing > more often so as to be balanced. > > Plenty of good article on the interweb discussing both: > http://www.ovaistariq.net/733/understanding-btree-indexes-an > d-how-they-impact-performance/#.WfdQDHYpCJA > https://blog.codinghorror.com/primary-keys-ids-versus-guids/ > http://web.archive.org/web/20150511162734/http://databases. > aspfaq.com/database/what-should-i-choose-for-my-primary-key.html > > I think I'll stick with app-generated GUIDs though for the portability and > no-collision benefit if I merged/move data. I also want to do replication > where their database is stored locally but then replicates to a master > database outside their office. > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cajidmy+p0ubfm_tjyfsjm7c+z8xuz3prn-ibr5ngzdo2efz...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
