I first used the database identity as a unique identifier, after I found a bug in Servoy, I switched to Servoy sequence. I would like to know, if this can have any sideffects? Is the server generating the Servoy sequences for the connected clients?
Is the Servoy sequence unique, if for example 2 connected clients create new records at the same time?
Be aware, that using servoy-sequences, the databases can’t be altered outside Servoy, or else your sequence’s become out of sync!
UUID IDs is the only way to go! No reseting sequences, can modify tables outside of Servoy, export/import a breeze, can easily split/combine like tables (SaaS task), etc.
david:
UUID IDs is the only way to go!
In fact there is a strong opinion for and against using UUID as PK’s.
In the end it all comes down to what your use-case is and can you live with the downside of either option.
We too are still not convinced if using UUID as PK’s, is the road to go…
Still not clear, what the consequences are with a large SaaS solution, containing tables with thousands (millions) of records
ROCLASI:
david:
UUID IDs is the only way to go!In fact there is a strong opinion for and against using UUID as PK’s.
In the end it all comes down to what your use-case is and can you live with the downside of either option.
10 years ago there were two valid sides to this decision. Now UUID for PK is a best practice except for a few rare edge cases. If you’re in this territory then it is very unlikely you would be developing this app with Servoy.
UUIDs are sexy but are very difficult to handle during debugging (integer pks easily show records creation order and are easy to spot when debugging), another concern is about performance if you have very large tables.
David, are you experiencing any performance issue on queries? Especially joins?
ngervasi:
UUIDs are sexy but are very difficult to handle during debugging (integer pks easily show records creation order and are easy to spot when debugging), another concern is about performance if you have very large tables.
David, are you experiencing any performance issue on queries? Especially joins?
All total bunk In what cases is the PK type a query performance consideration? If you know this answer, why are you developing in Servoy? And I had a developer not long ago state that they were using integers instead of UUIDs because they were easier to remember and type when debugging. This is not a valid reason!
SaaS, sharing data, syncing offline data, distributed data, multiple applications on same data, security, no maintaining uniqueness overhead, no scaling issues, multi-threaded ok, mobile friendly, cloud friendly, database vendor agnostic, unique across tables…these are valid reasons.
Auto-increment integers didn’t have to deal with all these situations back in the day. And nowadays with compression implemented by many databases, UUID space cost is negligible.
Many of the new db’s in the past handful of years don’t even have auto-increment integer PKs as an option.
david:
UUID space cost is negligible.
Space is not the issue, performance is!!
Nobody (sofar) can show real figures what the impact or difference is in performance with large scaled databases!
Harjo:
david:
UUID space cost is negligible.Space is not the issue, performance is!!
Nobody (sofar) can show real figures what the impact or difference is in performance with large scaled databases!
Space cost historically is the classic main debate issue. Space can still be an issue in certain types of deployments—but none that a Servoy app would used for.
Performance: exactly. “Large scaled databases”…note what type of PKs all the new databases in this category generate by default out of the box. Apparently they aren’t worried about any performance issues with UUIDs.
Hi David,
david:
Now UUID for PK is a best practice except for a few rare edge cases.
Can you enlighten us with some citations of these best practices and use-cases?
Hi David,
david:
Many of the new db’s in the past handful of years don’t even have auto-increment integer PKs as an option.
Even MySQL (and yes, I see that as a lowest denominator in the RDBMS world) does support these auto-increment integer PK’s.
And many of the ‘new’ (as in Not-Only-SQL) db’s also don’t support ACID. You make it sound that is always a good thing.
I disagree.
Again, it all depends on your use-case and the cost of the downside.
Database identity should be your first option. Your database is responsible for data integrity, so let it make the PK’s
Servoy Sequences are a good choice if you are making a SaaS product to distribute on various database platforms for which you may not have full say in how the backend is managed. In other words, I you don’t control the DB, then let your app do the work.
UUID’s are good if you want some kind of offline syncing. This should be your last choice as it comes with high performance costs. Integer PK’s can fit into memory easier and the index cluster handles them better.
Back to your original reason for switching from Database sequences. Normally foundset.sort issues a db query, so unsaved records would get dropped. According to your example, you passing a custom function into foundset.sort, so I’d say you did find a bug. Usually Servoy is pretty good about fixing real bugs like that. So, if you can hold off a bit, I’d suggest continuing to use Database Identities, and wait until Servoy fixes the bug.
ROCLASI:
david:
Many of the new db’s in the past handful of years don’t even have auto-increment integer PKs as an option.Even MySQL (and yes, I see that as a lowest denominator in the RDBMS world) does support these auto-increment integer PK’s.
And many of the ‘new’ (as in Not-Only-SQL) db’s also don’t support ACID. You make it sound that is always a good thing.
I disagree.
Again, it all depends on your use-case and the cost of the downside.
I give a bunch of reasons for why I think it should be the default choice for business applications. Any reasons for integer PKs? Throw 'em out for debate. Another one for UUIDs: sharing data amongst multi-developer teams.
goldcougar:
UUID’s are good if you want some kind of offline syncing. This should be your last choice as it comes with high performance costs. Integer PK’s can fit into memory easier and the index cluster handles them better.
Now we’re into an area that requires some thought. But apparently not black and white: http://inessential.com/vespersyncdiary. He ended up using UUIDs.
A most interesting thread, and it reminds me on discussions between fellow students in a canteen if Atari is the best computer or not. Most believed that the computer they currently used was the best.
But back to topic. I just googled “uuid performance” and found an interesting article written 2007 from MySQL guru Peter Zaitsev, who made some performance testing where integers were much faster for a large table with 268 million rows (200 times performance difference):
http://www.mysqlperformanceblog.com/200 … t-to-uuid/
Actually, there are tons of articles for “uuid performance”, therefore I think we should not fighting tooth and nail about this theme that others discussed so much already.
Without being aware of this thread, I asked by chance today if I should use an integer at least for the most important tenant_id in each table, while using UUID as PK for all tables in general.
ngervasi:
UUIDs are sexy but are very difficult to handle during debugging (integer pks easily show records creation order and are easy to spot when debugging)
Patrick Ruhsert told me that the record creation order can easily be done with the standard field creation_date, which most use anyway.
I agree to the advantage of integer PKs for debugging. I had cases where I just asked customers to give me the ID of a record that has a problem on phone. So identifying a record is much simpler. Therefore I am currently planning to use both a UUID as PK and a sequenced integer as user-friendly ID for communication about a specific record. I do not want to horrify my users by showing them a UUID.
Bernd.N:
A most interesting thread, and it reminds me on discussions between fellow students in a canteen if Atari is the best computer or not. Most believed that the computer they currently used was the best.
But back to topic. I just googled “uuid performance” and found an interesting article written 2007 from MySQL guru Peter Zaitsev, who made some performance testing where integers were much faster for a large table with 268 million rows (200 times performance difference):
http://www.mysqlperformanceblog.com/200 … t-to-uuid/Actually, there are tons of articles for “uuid performance”, therefore I think we should not fighting tooth and nail about this theme that others discussed so much already.
As I said earlier, this was a much debated issue 5+ years ago and almost entirely in the context of RDBMs—performance and storage size were definite concerns. The database field since then has seen quite a shakeup. RDBMs are no longer the only game in town and have had to adapt significantly to keep up with solutions offered by other types of databases. For sure, optimizations around PK UUIDs have been improved dramatically.
Bernd.N:
Without being aware of this thread, I asked by chance today if I should use an integer at least for the most important tenant_id in each table, while using UUID as PK for all tables in general.I agree to the advantage of integer PKs for debugging. I had cases where I just asked customers to give me the ID of a record that has a problem on phone. So identifying a record is much simpler. Therefore I am currently planning to use both a UUID as PK and a sequenced integer as user-friendly ID for communication about a specific record. I do not want to horrify my users by showing them a UUID.
To me this sounds more like a case for a custom customer identifier that has a little more meaning than just a sequenced integer. Ex: customer “8472” vs “SVY-012”.
david:
To me this sounds more like a case for a custom customer identifier that has a little more meaning than just a sequenced integer. Ex: customer “8472” vs “SVY-012”.
Sure, in case there was a handy matchcode, we used that. But in some tables like time_postings or business_trips it was faster to just take the sequenced integer id to have a unique and handy identifier for each record.
In general, I think it is nice to have that sequenced int-ID in all main tables, especially as is does not affect table seize or performance much.
david:
As I said earlier, this was a much debated issue 5+ years ago and almost entirely in the context of RDBMs—performance and storage size were definite concerns. The database field since then has seen quite a shakeup. … For sure, optimizations around PK UUIDs have been improved dramatically.
I currently use a SaaS solution for project management. And it needs about 5 seconds to just load one single task.
For me, such delay reduces the ability to really work fast and efficient, and I would not want my own users to wait so long until a record appears.
Therefore, I appreciate any method to improve response time performance for users.
And we have to keep in mind that not only RDBMs-performance improved much, but also data size and traffic.
Hi Bernd,
Did you already do some performance tuning? Like adding (correct) indexes and looking into database server settings? Also keep in mind even the OS and hardware it runs on can have an impact on performance. But to start with I would look at indexes and db server settings.
Hi Robert,
the 5 seconds stem from a third party solution we currently use, as long as our own Servoy solution is not ready. So that is not related to Servoy.
Maybe they put just too many tenants on one single physical database, or it maybe that its all hosted in the USA and therefore response time is quite long.
My remark was more a general one to underline that performance is still an issue in today’s world, and even gets more important, the more one goes towards SaaS solutions.
Adding indexes we will do for the most important fields.