Database identity VS. Servoy sequence

Questions and Answers on installation, deployment, management, locking, tranasactions of Servoy Application Server

Re: Database identity VS. Servoy sequence

Postby david » Thu Jun 19, 2014 8:26 pm

Bernd.N wrote: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 wrote: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 Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Re: Database identity VS. Servoy sequence

Postby Bernd.N » Fri Jun 20, 2014 9:53 am

david wrote: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.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Database identity VS. Servoy sequence

Postby Bernd.N » Mon Jun 30, 2014 8:39 am

david wrote: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.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Database identity VS. Servoy sequence

Postby ROCLASI » Mon Jun 30, 2014 1:02 pm

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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Database identity VS. Servoy sequence

Postby Bernd.N » Mon Jun 30, 2014 1:20 pm

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.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Database identity VS. Servoy sequence

Postby david » Mon Jun 30, 2014 5:01 pm

Bernd.N wrote:
david wrote: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.


Right. But, 5 seconds to load one single task has nothing to do with UUIDs vs auto-incrementing integer PKs. Crap programming/multiple round trips, slow connection, throttled server, missing indexes, Servoy web client, etc. This supports my point: UUIDs are the least of your performance worries in today's typical business apps. And if you don't use them, you are missing out on a ton of programmer ease-of-use and a major feature of the current web landscape: data ubiquity across services.

Whenever we run across auto-incrementing integer PKs, we literally cringe. Automatically puts your data in a restrictive box.
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Previous

Return to Servoy Server

Who is online

Users browsing this forum: No registered users and 9 guests