Hi Guys,
how can i use the newsequentialid() for my guid keys? Currently i have my own mssql Connection Provider, where i override
GetSqlCreateColumnFullAttributes
and add
" DEFAULT (newsequentialid())";
to the key column. works fine so far. but now i Need to tell XPO to remove the key property from the insert statements, and retrive the guid after the insert from the SQL Server?
how to solve this?
Hello Noxe,
I have discussed your task with our XPO developers and I am afraid there is currently no capability to achieve what you want. Moreover, our XPO architects think that the overall use of sequential guids does not give you any noticeable advantages in typical ORM scenarios.
UPDATE
With v18.2, you can provide a custom Guid primary key generation algorithm globally with the new XpoDefault.CustomGuidGenerationHandler (XpoDefault.GuidGenerationMode = GuidGenerationMode.Custom):
C#Func<Guid>
myCustomGuidGenerationAlgorithm = () => { return Guid.NewGuid(); };
XpoDefault.CustomGuidGenerationHandler = myCustomGuidGenerationAlgorithm;
See also: How I found out using Oids in clustered index in XAF is a very bad idea.
Hi Dennis,
i think you should then replace your architects. i did just some tests in an productive database. inserting an complex object which involves thousands of inserts took 5-6 minutes! before. i now switched to sequential guids from my second link avove. the whole commit now takes, sit down, 7 SECONDS!!
Thanks for your update, Noxe. I would not be so adamant about our XPO architects:-), because everything depends on a particular testing scenario, database server and other environmental settings.
That said, we would be more than happy to research your samples that show how it worked before and after your changes. Looking forward to hearing from you!
No need for server-side newsequentialid() - this has other disadvantages.
Instead, use a CombGuid, which is sequential, and you will get almost no fragmentation at all:
C#public static class CombGuid
{
public static Guid NewGuid()
{
DateTime timestamp = DateTime.UtcNow;
byte[] guidArray = Guid.NewGuid().ToByteArray();
byte[] timestampArray = BitConverter.GetBytes(timestamp.Ticks/10000L);
if(BitConverter.IsLittleEndian)
Array.Reverse(timestampArray);
Buffer.BlockCopy(timestampArray, 2, guidArray, 10, 6);
return new Guid(guidArray);
}
}
void Test()
{
Guid guid;
for(int i = 0; i < 100; i++)
{
guid = CombGuid.NewGuid();
guid.Dump();
}
}
Hi Rob,
yes, guid.newguid is worse. i really wonder why nobody else already come across this (with xaf). everywhere you read that guid as PK with the clustered index on it is absolute bad. as for the uuidcreatesequential, yes, there is an bug with winxp - if the mac adress starts with 8 or higher, this method Fails - you can fix it with changing the first letter of the mac address. (there is an Microsoft kb article about it)
for the test Scenario - i simply created an console application, an simple class (derived from xpcustomobject) - and and guid key. inserting 100.000 records. doing this on 2 different machines into the same database. i used both methods from xpodefault, yours, and 2 other methods (the seconds link, and the code Project solution)
they are all quite fast, but fragmentation and usage was best with the link above using uuidcreatesequential and shifting the Bytes.
we already rolled out the changes today - customers and internal tests showed inserts going from 10 minutes going down to 15-20 second!!
Hi,
I have hit performance issues with my client due to GUID.
Can you please suggest which would be the best Sequential GUID to use - XPO Default UUIDcreatesequential or CombGUID posted by Robert or something else?
Kindly suggest… Thanks in advance.
Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.
Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.
Hi Noxe,
what is your reason to use newsequentialid() for guid keys?
If the reason is that you want "sequential" guids to avoid key fragmentation, then look into CombGuid (see NHibernate).
These are sequential and have the benefit that no extra roundtrip is necessary to retrieve the guid from server.
JM2€C, Robert
Hi Rob,
1
there would be no roundtrip if you set it as Default for the key column. i want to use it because of Performance. we are getting more and more Performance Problems, and it seems that there is a huge Problem if the Primary key is an guid, and this column has also an clustered index on it. because guids are random, fragmentation is nearly 100%. i tested a lot today, and it seems XpoDefault.NewGuid() is the badest Performance. i then tried XpoDefault.GuidGenerationMode = GuidGenerationMode.UuidCreateSequential - but here fragmentaion is also nearly 100%, Performance is slightly better - but still bad if you add many records. for test, i added 100.000 records, 50 times in a row - a simple class containing only the PK. after adding many rows, both methods will drop on Performance, sometimes (because of SQL Server?) there are gaps of 1-5 minutes where SQL servere is shifting? rows… normal times is with Default guid 10-12 seconds for 100.000 records, 6-7 with uuidcreatesequential.
now i found some other methods:
http://www.codeproject.com/Articles/388157/GUIDs-as-fast-primary-keys-under-multiple-database
http://www.jorriss.net/blog/archive/2008/04/24/unraveling-the-mysteries-of-newsequentialid
where the second one is the fastest, and index fragmentation is on 0,5 - 1% with this method, and insert performace is constant on 5-6 seconds with no gaps!
also interessting link: http://blogs.msdn.com/b/dbrowne/archive/2012/06/26/good-page-splits-and-sequential-guid-key-generation.aspx
So after Research, it seem it is too hard to use newsequentialid() because of the insertstatement Generator which would Need to exclude the OID column, and get the new inserted value back to the Client, so i think i will go with the second link i posted above!
Hi Noxe,
no, do NOT use the Guid from the second link!
This Guid has the MAC address encoded and will not be sequential if generated from more than one machine, so you will still get a lot of fragmentation.
What you need is a Guid generated with a TimeStamp - a so called Comb(ined) Guid aka CombGuid, like I already posted above.
I posted the code that I use in the solution below.
Thanks for posting about Comb GUIDs Robert - been doing some research on this and it looks very interesting.