Re: Making an application that uses identity keys occassionallyconnected
Kirsten, Our DBAs considered and rejected sequential GUIDs. My best advice is don't solve half the problem :-) If you are using the latest Visual Studio database tools then rewriting the solution may not be as onerous as you fear. Finding all references to the single key and replacing them with two key references would be well within the capabilities of the tooling. -- Regards, noonie On 4 February 2012 15:38, Kirsten Greed kirst...@jobtalk.com.au wrote: ** ** ** ** Noonie ** ** Were you using NEWSEQUENTIALID() in the app that had problems in production? David Amos also mentioned NEWSEQUENTIALID() to keep fragmentation down – but I missed it’s importance. ** ** Bill – thanks for the info and link. ** ** I am thinking that the problem with 2 part keys is that it’s a bigger re-write than changing primary keys. I am sure to forget to add the 2ndkey in places! ** ** Kirsten -- *From:* ozdotnet-boun...@ozdotnet.com [mailto: ozdotnet-boun...@ozdotnet.com] *On Behalf Of *Bill Chesnut *Sent:* Saturday, 4 February 2012 3:20 PM *To:* **ozDotNet ** *Subject:* RE: Making an application that uses identity keys occassionallyconnected ** ** Kirsten, One issue with GUID keys is that they are not one-up so with clustered indexes base on a GUID, inserting new records into a table typically can cause a page split, which is expensive, there is a different algorithm to generate a GUID that is always increasing so it acts more like an integer key. In SQL 2005 and above it is NEWSEQUENTIALID() and there is code to generate it in .net I think. Good article that compares all of these key types: http://www.codeproject.com/Articles/32597/Performance-Comparison-Identity-x-NewId-x-NewSeque Bill Chesnut BizTalk Server MVP Melbourne**, **Australia -- * *
RE: Making an application that uses identity keys occassionallyconnected
Hi Noonie Thanks for that info, I have hundreds of thousands of records in some of my tables and have been very happy with identity integers as primary keys so far. Kirsten _ From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of noonie Sent: Saturday, 4 February 2012 12:05 PM To: ozDotNet Subject: Re: Making an application that uses identity keys occassionallyconnected Hi Kirsten, From my experience, using GUIDs as surrogate primary keys works fine in development where sample data is only thousands of rows. It bites viciously in production where we have millions of rows and deep dependencies. Int keys are still surrogate keys but seem to offer better performance. How you do this will depend on what your occasionally connected applications are connecting to. If it is a reporting database that consolidates data from different sites then your solution would be simpler than where it shared the information across all the remote sites. All the suggestions mentioned, in this thread, are viable. I have used two-part keys (e.g site-id, item-id) in the past to good effect. More recently we have added non-key GUIDs for the central database to verify equality and, as we don't redistribute all the data to the remote databases, it's only used as a tie-breaker. The solution you choose will depend on the nature of the application and how much sync code you are willing to write. If you use a framework you are as much constrained by its limitations as you are freed from having to write code :-( -- Regards, noonie On 2 February 2012 07:51, Kirsten Greed kirst...@jobtalk.com.au wrote: Hi All I am interested in making my application occasionally connected. My app uses SQL Server and the tables have identity keys (auto incrementing numbers) I can see this will be a problem if I have 2 databases on separate computers because they will both want to assign a new record to the same key. Indeed when I experimented with Microsoft Sync Framework 2, this is what happened. Any advice on a strategy forward? Thanks Kirsten
RE: Making an application that uses identity keys occassionallyconnected
Kirsten, One issue with GUID keys is that they are not one-up so with clustered indexes base on a GUID, inserting new records into a table typically can cause a page split, which is expensive, there is a different algorithm to generate a GUID that is always increasing so it acts more like an integer key. In SQL 2005 and above it is NEWSEQUENTIALID() and there is code to generate it in .net I think. Good article that compares all of these key types: http://www.codeproject.com/Articles/32597/Performance-Comparison-Identity-x-NewId-x-NewSeque Bill Chesnut BizTalk Server MVP Melbourne, Australia _ From: Kirsten Greed [mailto:kirst...@jobtalk.com.au] To: 'ozDotNet' [mailto:ozdotnet@ozdotnet.com] Sent: Sat, 04 Feb 2012 12:40:51 +1100 Subject: RE: Making an application that uses identity keys occassionallyconnected Hi Noonie Thanks for that info, I have hundreds of thousands of records in some of my tables and have been very happy with identity integers as primary keys so far. Kirsten _ From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of noonie Sent: Saturday, 4 February 2012 12:05 PM To: ozDotNet Subject: Re: Making an application that uses identity keys occassionallyconnected Hi Kirsten, From my experience, using GUIDs as surrogate primary keys works fine in development where sample data is only thousands of rows. It bites viciously in production where we have millions of rows and deep dependencies. Int keys are still surrogate keys but seem to offer better performance. How you do this will depend on what your occasionally connected applications are connecting to. If it is a reporting database that consolidates data from different sites then your solution would be simpler than where it shared the information across all the remote sites. All the suggestions mentioned, in this thread, are viable. I have used two-part keys (e.g site-id, item-id) in the past to good effect. More recently we have added non-key GUIDs for the central database to verify equality and, as we don't redistribute all the data to the remote databases, it's only used as a tie-breaker. The solution you choose will depend on the nature of the application and how much sync code you are willing to write. If you use a framework you are as much constrained by its limitations as you are freed from having to write code :-( -- Regards, noonie On 2 February 2012 07:51, Kirsten Greed kirst...@jobtalk.com.au wrote: Hi All I am interested in making my application occasionally connected. My app uses SQL Server and the tables have identity keys (auto incrementing numbers) I can see this will be a problem if I have 2 databases on separate computers because they will both want to assign a new record to the same key. Indeed when I experimented with Microsoft Sync Framework 2, this is what happened. Any advice on a strategy forward? Thanks Kirsten
RE: Making an application that uses identity keys occassionallyconnected
Noonie Were you using NEWSEQUENTIALID() in the app that had problems in production? David Amos also mentioned NEWSEQUENTIALID() to keep fragmentation down - but I missed it's importance. Bill - thanks for the info and link. I am thinking that the problem with 2 part keys is that it's a bigger re-write than changing primary keys. I am sure to forget to add the 2nd key in places! Kirsten _ From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Bill Chesnut Sent: Saturday, 4 February 2012 3:20 PM To: ozDotNet Subject: RE: Making an application that uses identity keys occassionallyconnected Kirsten, One issue with GUID keys is that they are not one-up so with clustered indexes base on a GUID, inserting new records into a table typically can cause a page split, which is expensive, there is a different algorithm to generate a GUID that is always increasing so it acts more like an integer key. In SQL 2005 and above it is NEWSEQUENTIALID() and there is code to generate it in .net I think. Good article that compares all of these key types: http://www.codeproject.com/Articles/32597/Performance-Comparison-Identity-x- NewId-x-NewSeque Bill Chesnut BizTalk Server MVP Melbourne, Australia _ From: Kirsten Greed [mailto:kirst...@jobtalk.com.au] To: 'ozDotNet' [mailto:ozdotnet@ozdotnet.com] Sent: Sat, 04 Feb 2012 12:40:51 +1100 Subject: RE: Making an application that uses identity keys occassionallyconnected Hi Noonie Thanks for that info, I have hundreds of thousands of records in some of my tables and have been very happy with identity integers as primary keys so far. Kirsten _ From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of noonie Sent: Saturday, 4 February 2012 12:05 PM To: ozDotNet Subject: Re: Making an application that uses identity keys occassionallyconnected Hi Kirsten, From my experience, using GUIDs as surrogate primary keys works fine in development where sample data is only thousands of rows. It bites viciously in production where we have millions of rows and deep dependencies. Int keys are still surrogate keys but seem to offer better performance. How you do this will depend on what your occasionally connected applications are connecting to. If it is a reporting database that consolidates data from different sites then your solution would be simpler than where it shared the information across all the remote sites. All the suggestions mentioned, in this thread, are viable. I have used two-part keys (e.g site-id, item-id) in the past to good effect. More recently we have added non-key GUIDs for the central database to verify equality and, as we don't redistribute all the data to the remote databases, it's only used as a tie-breaker. The solution you choose will depend on the nature of the application and how much sync code you are willing to write. If you use a framework you are as much constrained by its limitations as you are freed from having to write code :-( -- Regards, noonie On 2 February 2012 07:51, Kirsten Greed kirst...@jobtalk.com.au wrote: Hi All I am interested in making my application occasionally connected. My app uses SQL Server and the tables have identity keys (auto incrementing numbers) I can see this will be a problem if I have 2 databases on separate computers because they will both want to assign a new record to the same key. Indeed when I experimented with Microsoft Sync Framework 2, this is what happened. Any advice on a strategy forward? Thanks Kirsten __ Information from ESET NOD32 Antivirus, version of virus signature database 6777 (20120108) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
RE: Making an application that uses identity keys occassionallyconnected
The problem with NEWSEQUENTIALID is that it fixes the wrong problem. One of the main reasons for using GUIDs in the first place is so they can be generated in other layers of code (and potentially other servers) and then just thrown into the database. If you are going to the database to get a GUID of any type, you might as well get an INT instead. Regards, Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: http://www.sqldownunder.com/ www.sqldownunder.com From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Kirsten Greed Sent: Saturday, 4 February 2012 3:39 PM To: 'ozDotNet' Subject: RE: Making an application that uses identity keys occassionallyconnected Noonie Were you using NEWSEQUENTIALID() in the app that had problems in production? David Amos also mentioned NEWSEQUENTIALID() to keep fragmentation down - but I missed it's importance. Bill - thanks for the info and link. I am thinking that the problem with 2 part keys is that it's a bigger re-write than changing primary keys. I am sure to forget to add the 2nd key in places! Kirsten _ From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Bill Chesnut Sent: Saturday, 4 February 2012 3:20 PM To: ozDotNet Subject: RE: Making an application that uses identity keys occassionallyconnected Kirsten, One issue with GUID keys is that they are not one-up so with clustered indexes base on a GUID, inserting new records into a table typically can cause a page split, which is expensive, there is a different algorithm to generate a GUID that is always increasing so it acts more like an integer key. In SQL 2005 and above it is NEWSEQUENTIALID() and there is code to generate it in .net I think. Good article that compares all of these key types: http://www.codeproject.com/Articles/32597/Performance-Comparison-Identity-x- NewId-x-NewSeque Bill Chesnut BizTalk Server MVP Melbourne, Australia _ From: Kirsten Greed [mailto:kirst...@jobtalk.com.au] To: 'ozDotNet' [mailto:ozdotnet@ozdotnet.com] Sent: Sat, 04 Feb 2012 12:40:51 +1100 Subject: RE: Making an application that uses identity keys occassionallyconnected Hi Noonie Thanks for that info, I have hundreds of thousands of records in some of my tables and have been very happy with identity integers as primary keys so far. Kirsten _ From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of noonie Sent: Saturday, 4 February 2012 12:05 PM To: ozDotNet Subject: Re: Making an application that uses identity keys occassionallyconnected Hi Kirsten, From my experience, using GUIDs as surrogate primary keys works fine in development where sample data is only thousands of rows. It bites viciously in production where we have millions of rows and deep dependencies. Int keys are still surrogate keys but seem to offer better performance. How you do this will depend on what your occasionally connected applications are connecting to. If it is a reporting database that consolidates data from different sites then your solution would be simpler than where it shared the information across all the remote sites. All the suggestions mentioned, in this thread, are viable. I have used two-part keys (e.g site-id, item-id) in the past to good effect. More recently we have added non-key GUIDs for the central database to verify equality and, as we don't redistribute all the data to the remote databases, it's only used as a tie-breaker. The solution you choose will depend on the nature of the application and how much sync code you are willing to write. If you use a framework you are as much constrained by its limitations as you are freed from having to write code :-( -- Regards, noonie On 2 February 2012 07:51, Kirsten Greed kirst...@jobtalk.com.au wrote: Hi All I am interested in making my application occasionally connected. My app uses SQL Server and the tables have identity keys (auto incrementing numbers) I can see this will be a problem if I have 2 databases on separate computers because they will both want to assign a new record to the same key. Indeed when I experimented with Microsoft Sync Framework 2, this is what happened. Any advice on a strategy forward? Thanks Kirsten __ Information from ESET NOD32 Antivirus, version of virus signature database 6777 (20120108) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
RE: Making an application that uses identity keys occassionallyconnected
I find that these sorts of discussions often come out of standardisation meetings where people say stuff like should we make all our primary keys guids or ints then make a broad ruling and dev teams have to live with the consequences. Remember that one size does not have to fit all. Of course, because you can synchronise with the single-source-of-truth in a batch anyway, both have strategies that will work. If you choose guids, I think you need to separate out the data that you're adding from the client vs reference data, so you don't make the mistake of implementing guids for all tables, or ints for all tables and allowing clients to add reference data using the same mechanism when it is unnecessary or even undesirable. I don't need to repeat performance issues info about guids, but I have one more comment to make that many people overlook, and that is: From a developer's point of view, GUIDs suck. With guids, if you have your query analyser window open and want to run a quick query on a table to get a particular row, you have to have a valid guid, which means you have to look up your guid then copy or rewrite whole guid string into your query. Far more prone to errors. Therefore, with Guids, cutting and pasting is necessary most of the time. With ints, it's really easy, because you can often remember the ids of reference data after a while. With ints, most of the time, no cutting and pasting is necessary. It is far easier to remember an int when crossing between development and debugging in the query analyser. It's far easier when you're profiling to see an int and type it straight into a query on another screen. Also think of keystroke analysis and how much extra you need to do to work with Guids, such as requerying the database because it's too hard to remember a guid, identifying the record you want, honing the mouse, copying the guid, selecting your destination window, honing the mouse over the destination query, pasting the guid in the correct location. All of that instead of using a simple int! So if you want your team to be more efficient in their development, work out a strategy using ints and enjoy your jobs a little better. Regards, Tony Wright Hazaa From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Greg Low (GregLow.com) Sent: Saturday, 4 February 2012 4:43 PM To: 'ozDotNet' Subject: RE: Making an application that uses identity keys occassionallyconnected The problem with NEWSEQUENTIALID is that it fixes the wrong problem. One of the main reasons for using GUIDs in the first place is so they can be generated in other layers of code (and potentially other servers) and then just thrown into the database. If you are going to the database to get a GUID of any type, you might as well get an INT instead. Regards, Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: http://www.sqldownunder.com/ www.sqldownunder.com From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Kirsten Greed Sent: Saturday, 4 February 2012 3:39 PM To: 'ozDotNet' Subject: RE: Making an application that uses identity keys occassionallyconnected Noonie Were you using NEWSEQUENTIALID() in the app that had problems in production? David Amos also mentioned NEWSEQUENTIALID() to keep fragmentation down - but I missed it's importance. Bill - thanks for the info and link. I am thinking that the problem with 2 part keys is that it's a bigger re-write than changing primary keys. I am sure to forget to add the 2nd key in places! Kirsten _ From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Bill Chesnut Sent: Saturday, 4 February 2012 3:20 PM To: ozDotNet Subject: RE: Making an application that uses identity keys occassionallyconnected Kirsten, One issue with GUID keys is that they are not one-up so with clustered indexes base on a GUID, inserting new records into a table typically can cause a page split, which is expensive, there is a different algorithm to generate a GUID that is always increasing so it acts more like an integer key. In SQL 2005 and above it is NEWSEQUENTIALID() and there is code to generate it in .net I think. Good article that compares all of these key types: http://www.codeproject.com/Articles/32597/Performance-Comparison-Identity-x- NewId-x-NewSeque Bill Chesnut BizTalk Server MVP Melbourne, Australia _ From: Kirsten Greed [mailto:kirst...@jobtalk.com.au] To: 'ozDotNet' [mailto:ozdotnet@ozdotnet.com] Sent: Sat, 04 Feb 2012 12:40:51 +1100 Subject: RE: Making an application that uses identity keys occassionallyconnected Hi Noonie Thanks for that info, I have hundreds of thousands of records in some of my tables and have been very happy with identity integers as primary keys so far. Kirsten _ From: ozdotnet
RE: Making an application that uses identity keys occassionallyconnected
I did find a mention on stack overflow that suggests using a separate sync key. It doesn't mention GUIDS http://stackoverflow.com/questions/7184372/how-do-you-sync-databases-using-m s-sync-framework-when-tables-are-using-identity What data type would you use to store guids in the database? _ From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Greg Kennedy Sent: Thursday, 2 February 2012 11:08 AM To: ozDotNet Subject: Re: Making an application that uses identity keys occassionallyconnected I think the sync framework will crack a fruity if the Guid is not the PK. On Thu, Feb 2, 2012 at 8:50 AM, Greg Keogh g...@mira.net wrote: Kirsten has lots of tables already created with traditional PK IDENTITY columns, so I'm guessing that converting the PKs would be a nightmare. Perhaps a compromise is the answer: add an indexed Guid column to those tables that might find it useful in sync processing. That Guid is effectively stamped onto the row and never changes even if the INT PK does. I did this years ago to some of my own tables and they're still in use and the Guids are used by external applications to reliably and unambiguously find rows. However, can Sync Framework make use of Guid columns that are not the actual PK? Greg
Re: Making an application that uses identity keys occassionallyconnected
The data type is called uniqueidentifier Greg On Thu, Feb 2, 2012 at 11:01 AM, Kirsten Greed kirst...@jobtalk.com.auwrote: ** ** I did find a mention on stack overflow that suggests using a separate sync key. It doesn’t mention GUIDS http://stackoverflow.com/questions/7184372/how-do-you-sync-databases-using-ms-sync-framework-when-tables-are-using-identity What data type would you use to store guids in the database? ** ** ** ** -- *From:* ozdotnet-boun...@ozdotnet.com [mailto: ozdotnet-boun...@ozdotnet.com] *On Behalf Of *Greg Kennedy *Sent:* Thursday, 2 February 2012 11:08 AM *To:* ozDotNet *Subject:* Re: Making an application that uses identity keys occassionallyconnected ** ** I think the sync framework will crack a fruity if the Guid is not the PK.* *** On Thu, Feb 2, 2012 at 8:50 AM, Greg Keogh g...@mira.net wrote: Kirsten has lots of tables already created with traditional PK IDENTITY columns, so I’m guessing that converting the PKs would be a nightmare. Perhaps a compromise is the answer: add an indexed Guid column to those tables that might find it useful in sync processing. That Guid is effectively “stamped” onto the row and never changes even if the INT PK does. I did this years ago to some of my own tables and they’re still in use and the Guids are used by external applications to reliably and unambiguously find rows. However, can Sync Framework make use of Guid columns that are not the actual PK? Greg ** **
