September 19, 2011 at 5:04 am
ispooner,
Does anything from your post make the approach less wrong?
If not, then what was that about?
I'm aware of the common practices.
Cursors, loops, denormalized tables, etc. - used as common practices by 90% of "normal" developers.
Does it justify use of those approaches?
_____________
Code for TallyGenerator
September 19, 2011 at 5:26 am
Sergiy - What I'm trying to say is that the original idea of coming up with a more efficient globally unique identifier is good. However its usage in order to come up with a non-standard operational procedure for DR is deeply flawed.
September 19, 2011 at 8:26 am
Thought provoking.
Why have a service issue the GUIDs? A small bit of clr code could do what you need and would be fast. You could then let your insert stored procedures transparently create the "identity" of the record and return it to your middle tier layer.
September 19, 2011 at 8:34 am
Colin Barry (9/19/2011)
Thought provoking.Why have a service issue the GUIDs? A small bit of clr code could do what you need and would be fast. You could then let your insert stored procedures transparently create the "identity" of the record and return it to your middle tier layer.
Absolutely agree - although SOA may be a buzzword on many people's lips, just because you can do something doesn't mean that you should: Not only does the cross-network and cross-process intercommunication introduce additional latency, but the additional components involved in implementing an additional service reduce the MTBF of the entire system.
September 19, 2011 at 9:21 am
iposner (9/19/2011)
Colin Barry (9/19/2011)
Thought provoking.Why have a service issue the GUIDs? A small bit of clr code could do what you need and would be fast. You could then let your insert stored procedures transparently create the "identity" of the record and return it to your middle tier layer.
Absolutely agree - although SOA may be a buzzword on many people's lips, just because you can do something doesn't mean that you should: Not only does the cross-network and cross-process intercommunication introduce additional latency, but the additional components involved in implementing an additional service reduce the MTBF of the entire system.
Don't confuse SOA with web services. In this case - "service" is just as valid a name for the CLR code as if would be for the web service call previous advocated. SOA also has nothing to do with whether the functionality is "local" or "remote".
In short - both would be SOA if the purpose is reusability of the identifier (GUID) functionality.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 19, 2011 at 3:37 pm
iposner (9/19/2011)
Sergiy - What I'm trying to say is that the original idea of coming up with a more efficient globally unique identifier is good. However its usage in order to come up with a non-standard operational procedure for DR is deeply flawed.
I cannot see anything "more efficient" in that new identifier.
Same size of the value, not very efficient process of generation, additional checks required to prevent duplications...
And it's all in sake of succession.
As SQL Kiwi (cheers mate!) said - evil is not in GUID, evil is in clustered key on GUID column. It's another extremely bad practice, so common that you'll say I should not challenge it.
But I still do.
You'll never select a range of GUID's, so it should be never used for a clustered index.
Problem with GUID's is not in lack of succession.
It's in architectural flaw of using internally auto-generated identifiers for global identification of business objects. No matter how smart will be your auto-ID it will never be good for the purpose.
_____________
Code for TallyGenerator
September 19, 2011 at 4:03 pm
Well the size is half the size of the GUID.
I'm not sure how GUIDs are generated but I'm willing to be there is substantially more calculation involved albeit highly efficient and optimised calculation.
As to not being able to come up with something better than GUIDs that implies that GUIDs are at the pinacle of identity generation and can never be surpassed.
I have heard anecdotal evidence that GUIDS are only guaranteed unique on the machine on which they are issued. It is improbable that you will get two systems generating contradictory GUIDs but not impossible for it to occur.
Just as a matter of interest how do you handle multiple data centres, particularly where there is a large geographic distance between them?
September 19, 2011 at 9:17 pm
nick 2435 (9/15/2011)
We changed the SO numbering here to be a long set of letters and numbers that are part random and part sequence (based on time) which means the computer just “makes it up” rather than grabbing the next one.What we did was build a 15 digit order number in the following format, using essentially base 30 - built from 30 easily distinguisable characters (23456789ABCDEFGHJKLMNPRTUVWXYZ)
S123-TYP-QRT-6RE
Interesting. What are you using to prevent the spelling out of random swear words?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2011 at 1:11 am
Interesting. What are you using to prevent the spelling out of random swear words?
Not thought of that one - but no one has ever reported any! There's very few that can be spelt with those letters anyhow 🙂
September 24, 2011 at 6:49 pm
nick 2435 (9/20/2011)
Interesting. What are you using to prevent the spelling out of random swear words?
Not thought of that one - but no one has ever reported any! There's very few that can be spelt with those letters anyhow 🙂
You're absolutely correct! Only several 3, 4, 5, and 6 letter swear words/phrases and just a couple of combinations of double 3 letter swear words and only a few "U" swear words can be spelled out. You should be fine and I'm sure that the users that receive an SO with a swear word in it will marvel at the technical prowess of your company and are sure to create many additional orders with the company just to see which swear words will be generated. Of course, they'll also tell all of the people and maybe even some lawyer friends they know so they can all join in the fun. Yep... I'm sure it'll all be fine and your boss will actually congratulate you for providing so much fun for everyone especially those sour-puss lawyers. 😉
At least remove the vowels and the letter "K" from the mix.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2011 at 1:45 am
Loved the article and, even more so, the fact that your brainwave has provoked such excellent feedback (haven't read it all yet mind).
I'm on the programmer-side and, therefore, no DB expert but while I understand the issues associated isn't one of the key things about Identity columns that no matter what client (any number of bespoke clients or apps like Excel, etc.) attempts to insert data the uniqueness of the id field will be guaranteed by the database (single instance).
Set me straight here but isn't the alternative that all apps (app developers) must know more about the internal storage of data and isn't that 1. a bad thing 2. open to be abused.
Perhaps you suggest all identity/identifier fields should be calculated somehow using an appropriate guid-type creation function from within the database?
I have seen apps that use all sorts of unique record identifier functions that must be called by the client app before inserting data. I have seen many problems with all of these implementations but the consistent thing i noticed was that it was always difficult for other apps to use - most seems to decide that this database is only to be access from this application or instances of it. Regardless of database scalability and all that good stuff the idea that any significant database would be accessed by a single application (even a single language) over the course of the lifetime of the data is....limiting.
Again, very much enjoyed this lateral thinking article.
September 27, 2011 at 1:55 am
A common problem among developers is an unwillingness to master the entire software stack. If you want great software, you must develop expertise in all layers including DB and OS.
September 27, 2011 at 1:56 am
You're absolutely correct! Only several 3, 4, 5, and 6 letter swear words/phrases and just a couple of combinations of double 3 letter swear words and only a few "U" swear words can be spelled out.
To be honest though we have been running this scheme for a few years (around 300,000 orders since we moved to using it) and although a few have appeared we have never had any comments. I suppose it is quite childish I guess to "spot" swear words in order numbers 😉 - and especially with the hypens they become less obvious.
I am the business owner so no one is going to shout at me either!
September 27, 2011 at 4:55 am
jh-638585 (9/27/2011)
Loved the article and, even more so, the fact that your brainwave has provoked such excellent feedback (haven't read it all yet mind).I'm on the programmer-side and, therefore, no DB expert but while I understand the issues associated isn't one of the key things about Identity columns that no matter what client (any number of bespoke clients or apps like Excel, etc.) attempts to insert data the uniqueness of the id field will be guaranteed by the database (single instance).
Set me straight here but isn't the alternative that all apps (app developers) must know more about the internal storage of data and isn't that 1. a bad thing 2. open to be abused.
Perhaps you suggest all identity/identifier fields should be calculated somehow using an appropriate guid-type creation function from within the database?
I have seen apps that use all sorts of unique record identifier functions that must be called by the client app before inserting data. I have seen many problems with all of these implementations but the consistent thing i noticed was that it was always difficult for other apps to use - most seems to decide that this database is only to be access from this application or instances of it. Regardless of database scalability and all that good stuff the idea that any significant database would be accessed by a single application (even a single language) over the course of the lifetime of the data is....limiting.
Again, very much enjoyed this lateral thinking article.
Hey jh, you are correct in what you assert for a single server environment. The issue here is really what happens when a database table spans multiple SQL Servers, either for temporary capacity or as a part of a failover procedure.
It was only a few years ago that this sort of argument was not even contemplated due of cost except in the biggest systems but with the dropping price of hardware & hosting, along with the increasing abilities of SQL Azure, it is becoming more and more of a consideration.
Even then the partitioning of a table ("vertical" partitioning rather than "horizontal" I guess) is really only an issue when you need to bring some or all of the partitioned data back onto a single DB server. Where a system is partitioned across multiple SQL Servers for temporary capacity increase, or when a DR site temporarily receives live transactions are examples of this. With identity columns on each server this means you almost always have key collisions.
There are ways to get around this, and the guid, sequential guid and generated ID methods are just some of them.
Hope this helps!
September 27, 2011 at 4:58 am
iposner (9/27/2011)
A common problem among developers is an unwillingness to master the entire software stack. If you want great software, you must develop expertise in all layers including DB and OS.
Plus 1 to that. I am constantly amazed by otherwise smart people on both sides of one software divide or another that are unwilling to understand things that directly affect what they do, and in many cases software projects live or die by these decisions.
Viewing 15 posts - 46 through 60 (of 63 total)
You must be logged in to reply to this topic. Login to reply