primary key index column sort order

[huber]

Hi

Servoy mandates in a SELECT to load a foundset using the pk columns in alphabetical order (of course relevant only for composite, i. e. multi column pk's).
Should the pk index for good performance have the same (alphabetical) order for the columns? What, if the most to least selective columns are not in this order, i. e. pk index has column order a,b,c,d but most to least selective columns are c,d,a,b?

Are there best practices for applying primary key indexes (and foreign key indexes) to work optimal with Servoy?

Best regards,
Robert

[omar]

Hi Robert,

I prefer never to use multi-column primary keys. Performance will never be better than with meaningless primary and foreign keys. PK's and FK's should always have an index and database performance will mostly be better with indexes on selective columns. Are there any particular reasons why you need to use multi-column PK's?

Kind regards,

[huber]

Hi Omar

Thanks for your reply. How to design a primary key in a database design process is quite a bit beyond the possibilities of a forum to discuss. But I would very much like to discuss it with you - hopefully there is once a chance at a Servoy World meeting.

Robert

Hi Robert,

I prefer never to use multi-column primary keys. Performance will never be better than with meaningless primary and foreign keys. PK's and FK's should always have an index and database performance will mostly be better with indexes on selective columns. Are there any particular reasons why you need to use multi-column PK's?

Kind regards,

[Bernd.N]

Hi Robert,

I agree to Omar, there are not much reasons where one needs to design primary keys, it is just much work, and you always have to take care about uniqueness. All that you can spare with UUIDs.

I normally take the UUID Servoy suggests, only a few tables like countries or languages have the ISO-codes as PK, which has advantages over a UUID, as then they are easily readable as FKs in other tables.

There are a lot of SQL books where the authors talk a lot about creating PKs from different candidate keys, but I think that is more or less outdated wisdom from the past. In most entities, nearly every "real-world-data-field" is subject to change over time, and therefore not suitable to serve as a PK that may never change.

So when someone suggests for example to take first name, last name and birthday as a multi-column PK in a persons table, that makes no sense at all, as both names could change over time and the birthday could be unknown for some persons.

Bernd

[sbutler]

Any good DBA will tell you to always use the "natural" key of a table. Creating another arbitrary key for the sake of simplicity is something lazy programmer do. However, you'll find that it's like religion, without a lot of consensus. DBA say us the natural key, and programmers make a key for every table because its easier. In my Computer Science database course years ago, my professor would fail you for making arbitrary keys, while the programmer professors couldn't care less. In reality 90% of the time it doesn't matter. However I have customers with huge terabytes of databases joining billion row tables...and yes, it does matter then, which is why any good DBA will tell you to use the natural key, which many times is a composite key (more than one column as the key). My rule of thumb is to use composite keys for many-to-many join tables (ie order_items joining the order id to the item id)

Onto the specifics on your question. The answer depends on the database you are using. Some examples below.

Generally universal principals:
- Always use composite keys if the foreign keys make up a natural key (like order_item example)
- The order of the items in the key should be in order of highest cardinality (most unique first)
- When your query involves both columns in the key, most all databases can use the pk index, regardless of the order of the columns the index

MySQL:
- Can only use a composite index if both columns that are part of the index are in the query, OR it can also use the index if your query includes the first column in the index. It can't however use the index if you are querying only the second column in the index. So you might have to add another index on the second column.

Postgres:
- Really no restriction here. Postgres will use the pk index for one or both columns being queries, regardless of order. However, using the second column in the index is slower, so you might still create another index.


A few other thoughts...
- Take the order_items example where the orderid and itemid would make up the composite primary key. It restricts you from
Having the same item on the order more than once. If you make an arbitrary order_item _id you don't get that extra enforced reliability of your data.
-Don't just randomly try to pick columns that together should be unique. Generally is only use composite keys that are made up of foreign keys
- Using composite keys in Servoy is a pain in the ass. Sorry...it just is. Nothing in life is free.

[Bernd.N]

I have problems to see natural keys in most tables that are dead certain and will never change.

For example, a business partner table: any attribute of a business partner can change over the time. The DUNS can not be used as some business partners do not have a DUNS, or you need to create two business partners with the same DUNS. And in case a DBA decides to use the companies own business partner number as a PK, that will also fail when in 10 years the numbering scheme shall be changed, and it would also force you to give every business partner a number, even prospects who do not need it yet.

Second example, a projects table: when we take a project number as a PK, we will have problems as soon as a project leader decides she needs to change a project number.

Third example, an invoice table, there might be reasons that an invoice number needs to change after the invoice was written.

All those problems can be spared with surrogate keys like UUIDs. One advantage I found in UUIDs is that you do not have any trouble to care for the sequencing, besides the other obvious advantages when you want to merge two tables.

There are some few exceptions like languages, currencies and countries base tables, where it is obvious that you can savely use the ISO codes as PKs as they will never change.

And I agree, Scott, even when you try to use composite keys in Servoy, you will get a lot of problems and work which you can completely spare when you do not use them.

[rieder]

Hello

I appreciate all your effort. Thank you. But over my years programming and using databases, I was discussing this topic over and over again. It is religious, as Scott mentioned.

I'm very interested in answers to the original question? Any hints?

Thanks and regards
Birgit Rieder

[Bernd.N]

Hi Birgit,

regarding the second question "Are there best practices for applying primary key indexes (and foreign key indexes) to work optimal with Servoy?", I think the answers were valid.

In case you will get no answers for the first question, you could just test the performance of different approaches with large test tables.

I could imagine there will not be many further answers, as not many developers work with composite keys, as it creates further work you can spare without them.

In case there are heavy performance problems due to the composite keys, and they can not be solved by optimisation of coding or database setup, I would at least think about the option to go over to surrogate keys like UUIDs, and test how the performance looks there in comparison.

Of course it would not be easy to convert a complete solution, but on the other hand it is not that much work either when done systematically.

Bernd

[ROCLASI]

I think this whole discussion hinges on 2 thoughts/perspectives. The relational database way of doing things and the way your query tool-stack prefers things to work.

From a relational design perspective using natural keys is the 'proper' way of modeling your data. The notion that PK's have to be static forever is not true. When you use a natural key like a name and someone changes this name you make sure you have a CASCADE UPDATE defined on the foreign key constraints so any change automatically propagates to the related tables. Sadly tools like Servoy have no notion of a CASCADE UPDATE and so it will never update it's cache(s) automatically when you touch such a PK.
So the tool you are using to query your data forces you to add extra code to facilitate this or, what most do, is to model the database to please the query tool.

As for surrogate keys (being it incremental keys or UUID keys) the argument for them are usually about smaller indexes (and thus faster search). This is true. But they also have no meaning.
Consider the following situation.You want to get the total hours done on a specific project within your company.
When you use surrogate keys which have no meaning whatsoever you query it like so:

Code: Select all

SELECT sum(t.hours)
FROM timesheets t
     JOIN departents d ON (d.dept_id = t.dept_id)
     JOIN timesheet_statuses s ON (s.status_id = t.status_id)
     JOIN projects p ON (p.project_id = t.project_id)
     JOIN tasks k ON (k.task_id = t.task_id)
WHERE d.dept_code = 'HR'
AND s.status = 'VALID'
AND p.project_code = 'MYPROJECT'
AND k.task_code = 'BUILD';

However if you used natural keys that DO have meaning you could make your query much simpler and one could argue it might be faster as well since there are no joins needed:

Code: Select all

SELECT sum(t.hours)
FROM timesheets t
WHERE t.dept_code = 'HR'
AND t.status = 'VALID'
AND t.project_code = 'MYPROJECT'
AND t.task = 'BUILD';

(example taken from http://stackoverflow.com/questions/6309 ... iness-keys)

So one could conclude that there is no universal 'right way'. Always using UUID's or incremental keys can proof to be less efficient in certain cases than using natural keys (and vice versa).
I think the following blog post gives a bunch of nice examples why to use natural keys but also what reasons could be to use surrogate keys.

As for using multi-column keys. I still tend to use them on things like join tables. But even then sometimes Servoy forces me to redesign it or add another incremental key to it.

[mboegem]

I think this whole discussion hinges on 2 thoughts/perspectives. The relational database way of doing things and the way your query tool-stack prefers things to work.

I couldn't agree more, reading this post

It almost seemed a Mac/Win discussion
(Must be a coincidence we're both Mac users)

[Bernd.N]

Interesting discussion. I add the foreign keys of our project table as an example, so it may be easier to see the background of my argumentation.

I would be worried about the resulting complexity in case I would exchange some or all of them by natural (composed) keys, not to mention how the needed relations in Servoy would have to look.

And that's just one table, what about the other 173 tables.

[mboegem]

Like Robert Ivens started his post:

I think this whole discussion hinges on 2 thoughts/perspectives. The relational database way of doing things and the way your query tool-stack prefers things to work.

As we're all using Servoy, we need to keep in mind that Servoy has no knowledge about direct changes on the DB (like cascade update)
Still there could be valid reasons to use natural keys, even in Servoy.
Think about retrieving and storing data from an 3rd party API which does not include a unique key, other than the combination of 2 fields.
In order to avoid storing duplicates and executing additional queries to find out whether the entry already exists, using the combination of these fields as a PK is a much smoother solution.
In this example these fields will never change.

Along the way this thread has gone mainly off-topic, answering all other related-issue questions but the original one of Robert Huber...

Rephrased:
What is the best way to name/setup my multi-column PK in Servoy, in order to have Servoy use the most selective column first in the where clause, as this will gain performance in your backend DB.

Personally I haven't tried this yet, but maybe someone else has?
Anyone from Servoy?

[Bernd.N]

I do not have an answer to that question due to lacking experience with multi-column-PK, but some ideas to think about. Maybe one could lead to a solution or initiate further helpful thoughts/ideas from your side.

- I guess Servoy produces a SELECT with four clauses, when you have a multi-column PK out of four columns a,b,c,d, to get the records it wants.
I would have a second look if really all four columns have a single index in the backend DB.

- Using a SQL profiler to gather data regarding the performance of different approaches
(the Servoy application has a build-in-profiler, that may be sufficient)

- Maybe just one index on a single field would be faster, so one could create an additional DB field that gets automatically all values from the a,b,c,d-fields in a concatenated way, when the record is created. In case not all fields are strings, one could convert other types to a string. That new field could then serve as the new PK.
Sure that's not very elegant, but disk space is not a bottleneck any more today.

[sbutler]

Rephrased:
What is the best way to name/setup my multi-column PK in Servoy, in order to have Servoy use the most selective column first in the where clause, as this will gain performance in your backend DB.
Anyone from Servoy?

I'm pretty sure when Servoy issues SELECT statements on tables with composite keys, it does so in alphabetical order. You'll also find that noted in the docs
https://wiki.servoy.com/display/DOCS/JSFoundSet

// loads a primary key dataset, will remove related sort!
//var dataset = databaseManager.getDataSetByQuery(...);
// dataset must match the table primary key columns (alphabetically ordered)
forms.customer.foundset.loadRecords(dataset);

The order of the columns in the SELECT doesn't matter. All the DB engines I've used (SQL Server, MySQL, Postgres) are smart enough to use your index regardless of order (as long as all the pk columns are part of the query), and regardless of how you created the index (like most selective column first). So Servoy issues the queries Alphabetical first, your database sees all the PK columns are in the SELECT, and will use your index, which hopefully you've created to be most optimal.
The only real performance issue would be if you also tried to use them in a different order in the ORDER BY portion of the query, then it wouldn't likely be able to use the index.

[jcompagner]

i see all this way more higher up.. so i don't agree with scott, Natural keys are bad.

The way i see it is the same as if i would see it in a in a programming languages..

There you have Objects that references each other.. That reference is done by the system (its a memory address) thats not something the developer should have any influence with or tamper with..

And i see database things the same, the are all objects referencing each other, and those references shouldn't really be "used" or "seen" those are just rowid that the database is using..

Having pk's really visible on a form is in my eyes a bad design.