Effect on Performance if adding new key in a table

Andwian

Dear Experts,

Is there any effect on performance when I add new key in a table?

Thank you.

kitik

Yes. Inserting a new record will take longer.

Salut!

rhpnt

New keys/indexes only affect reads/queries not inserts.

kitik

rhpnt wrote:

New keys/indexes only affect reads/queries not inserts.

New indexes affect inserts and modifys.

In the C/Side Introduction course you can read

A secondary key can be changed into an incative key. This means that the DBMS does not use time during updates to mantain its index. Furthermore, an inactive key doesn't occupy database space

In the help file you can also read

Adding a large number of keys to database tables decreases performance

Salut!

kriki

rhpnt wrote:

New keys/indexes only affect reads/queries not inserts.

And in overindexed tables (a lot of NAV tables are overindexed because of the way the native server works), they influence a lot the INSERT/MODIFY/DELETES!
Each extra index must be updated when an INSERT/MODIFY/DELETE happens and that costs resources and time.

rhpnt

Dear Laura,

don't mix inserts with updates - these two transactions are not the same. And instead on relying on NAV courseware documentation, please do read some SQL Server related books.

kitik wrote:

In the C/Side Introduction course you can read

A secondary key can be changed into an incative key. This means that the DBMS does not use time during updates to mantain its index. Furthermore, an inactive key doesn't occupy database space

It's all true but it says "updates" not inserts.

kitik wrote:

In the help file you can also read

Adding a large number of keys to database tables decreases performance

True again, but the "performance" term applies only to read transactions.

rhpnt

kriki wrote:

rhpnt wrote:

New keys/indexes only affect reads/queries not inserts.

And in overindexed tables (a lot of NAV tables are overindexed because of the way the native server works), they influence a lot the INSERT/MODIFY/DELETES!
Each extra index must be updated when an INSERT/MODIFY/DELETE happens and that costs resources and time.

Don't get me wrong I'm a strong supporter of "lesser" table indexation. BUT, we should not be to general when explaining the impacts of indexes on application performance.
It's true that a heavy indexed table has to update all indexes when a record gets inserted but this action has no impact on the application itself. The transaction is closed as soon as the record is inserted in the main table - what happens next (updating indexes/pages/partitioned tables) is purely server business and has no effect on the front end.
Sure if the db server and client are running on the same pc you'll get some performance issues, even more if you are using the NAV native db. But we sure are not working by those standards or are we?

kitik

rhpnt wrote:

Dear Laura,

don't mix inserts with updates - these two transactions are not the same.

In NAV you have 3 possible actions: INSERT / MODIFY / DELETE
All 3 actions are updates

rhpnt wrote:

and instead on relying on NAV courseware documentation, please do read some SQL Server related books.

Are you saying that NAV courseware are all wrong and should not be read?

kitik wrote:

Adding a large number of keys to database tables decreases performance

rhpnt wrote:

the "performance" term applies only to read transactions.

Why it only affects to read transaction?
Actually, to read shouldn't it be best to have as many keys as possible so you could always sort for each field to be filtered?
When using the right key the read transaction increases it performance, not decreases it.

rhpnt wrote:

Don't get me wrong I'm a strong supporter of "lesser" table indexation

If not because of performace decrease, what other reason makes you support lesser table indexation?

rhpnt wrote:

It's true that a heavy indexed table has to update all indexes when a record gets inserted but this action has no impact on the application itself. The transaction is closed as soon as the record is inserted in the main table - what happens next (updating indexes/pages/partitioned tables) is purely server business and has no effect on the front end.

Isn't the server also part of the application?


Salut!

einsTeIn.NET

Well, I guess what rhpnt is saying is that the wohle process of INSERT is affected by the number of keys / indexes. But the part that is responsible for the creation of the indexes is performed by the server. That means the client shouldn't notice any loss of performance.
I think only when the server resources are undersized you might notice some performance issues when the server is performing that part.

Andwian

Thank you for all your valuable comments.

I want to add a new key to make my sort more fast. So is it worth it to add new key so that my SETRANGE result faster?

einsTeIn.NET

Yes, that could be an option. But it depends on your current situation. You have to deliberate about whether it is worth or not.

Andwian

einsTeIn.NET wrote:

Yes, that could be an option. But it depends on your current situation. You have to deliberate about whether it is worth or not.

Thank you, Einstein.

stryk

Uhm ... I just briefly followed this thread, but if I don't err the question about the type of database server hasen't been answered so far ... are you running SQL Server or C/SIDE Server?

"Key" and "Index" are actually different things on each server ...

But the short and common answer (applicable to both servers) could be:
- If you need a specific sorting order you must add a new "Key"
- Depending on the size of a table (e.g. number of records) and on the kind of fields you want to filter on READ performance could be dramatically improved by adding the right (!) Key/Index
- Technically every new Key/Index consumes disk and memory space and may affect WRITE performance. If this is indeed a problem or not depends on the table size and the number of already existing Keys/Indexes

I dare say that in 90% of all cases READ performance is way more important than WRITE performance (assuming the Server is properly sized and configured, running on appropriate hardware etc.), thus adding a new Key/Index usually has more benefit than harm.

If you are running SQL Server you could find out which Indexes are really used & required and which indexes you may dispose, thus, optimizing the balance between READ & WRITE perfromance; e.g. see this article.

HAPPY NEW YEAR!
Jörg

Andwian

Thank you Jörg for your comments.

stryk wrote:

are you running SQL Server or C/SIDE Server?

SQL Server.

stryk wrote:

"Key" and "Index" are actually different things on each server ...

Is Key = Index? :?

Happy New Year!

stryk

Andwian wrote:

stryk wrote:

"Key" and "Index" are actually different things on each server ...

Is Key = Index? :?

Technically a "Key" is a somewhat different thing than an "Index" (at least on SQL Server); but with NAV we have a defined out-of-the-box "translation" from Key to Index:

- The Primary Key in NAV is also the PK on SQL Server (= defines the uiqueness of each record)
- The Primary Key in NAV (usually) defines the Clustered Index on SQL (= physical sorting of records); could be changed in NAV using the property "Clustered"
- All other NAV "Keys" are represented by Non-Clustered Indexes in SQL Server (= Balanced Tree structures helping to retrieve records quick); you could influence this by using the "SQLIndex" property (beware!)
- If the NAV DB property "Maintain Relations" is flagged (not recommended!) the all logical NAV "Table Relations" are transformed into "Foreign Keys" on SQL Server

With SQL Server the NAV "Key" only defines the sorting order of records, while SQL Server uses the related "Indexes" to retrieve the result sets. Hence, "Keys" and "Indexes" could be handeled seperately!
Means, to optimize performance, you could also create indexes on SQL Server site, without (!) fiddling with "Keys" in NAV! This offers great possibilities to improve a system beyond NAV's limitations.
But: you really have to know what you are doing when tuning on SQL Server site, else you might screw up your system!!!
Since NAV is using "Dynamic Cursors" there should be a pretty good match between sorting order (Key) and filter (Index), else performance could be degraded (this also depends on the NAV version/build and the C/AL programming).

If you like you could check out my BLOG about further details

Andwian

What an explanation!

Danke, Stryk, und Glückliches neues Jahr!

David_Singleton

stryk wrote:

Andwian wrote:

stryk wrote:

"Key" and "Index" are actually different things on each server ...

Is Key = Index? :?

Technically a "Key" is a somewhat different thing than an "Index" (at least on SQL Server); but with NAV we have a defined out-of-the-box "translation" from Key to Index:

- The Primary Key in NAV is also the PK on SQL Server (= defines the uiqueness of each record)
- The Primary Key in NAV (usually) defines the Clustered Index on SQL (= physical sorting of records); could be changed in NAV using the property "Clustered"
- All other NAV "Keys" are represented by Non-Clustered Indexes in SQL Server (= Balanced Tree structures helping to retrieve records quick); you could influence this by using the "SQLIndex" property (beware!)
- If the NAV DB property "Maintain Relations" is flagged (not recommended!) the all logical NAV "Table Relations" are transformed into "Foreign Keys" on SQL Server

With SQL Server the NAV "Key" only defines the sorting order of records, while SQL Server uses the related "Indexes" to retrieve the result sets. Hence, "Keys" and "Indexes" could be handeled seperately!
Means, to optimize performance, you could also create indexes on SQL Server site, without (!) fiddling with "Keys" in NAV! This offers great possibilities to improve a system beyond NAV's limitations.
But: you really have to know what you are doing when tuning on SQL Server site, else you might screw up your system!!!
Since NAV is using "Dynamic Cursors" there should be a pretty good match between sorting order (Key) and filter (Index), else performance could be degraded (this also depends on the NAV version/build and the C/AL programming).

If you like you could check out my BLOG about further details

=D> =D> =D>

Great post Joerg,

Simple and concise.

stryk

Thanks a lot, David

Happy New Year!