clustered index on Ledger tables

NavStudent

Hello

I was reading a sql optimation book, and in the book it talked about clustered index on sequentially increasing tables. Ledger tables in navision.

Here is the quote.

Beware, though, if the possible cluster key is a monotonically sequential, or serial key—for example, an integer whose values always go up and always go up by the same amount, when you INSERT. The problem with monotonics (which are sometimes hidden behind names like "identity" or "timestamp" columns—see Chapter 7, "Columns") is that the nth row and the (n + 1)th row will both fit in the same page—so there might be contention for the same resource. Usually, in multiuser systems, you want rows to be dispersed.

This above all—The benefits of clustered indexing depend on a monster assumption; namely, that you want to retrieve and sort by the cluster key far, far more often than with any other key.

So does this mean Clustered index shouldn't be the entry no. but
for example Item No., Entry No. and have 99 % fill rate so that new entries get filled inbetween?

thanks.

NavStudent

one more quote about the order of fields added to a table.

Recommendation: Put columns in the order that users will expect to see when they execute SELECT *: generally, related columns grouped together, and the primary key on the left, followed by columns that are frequently used for searches or modified by the users.

NavStudent

and another question about ROWID and is Nav using this? or could use it?

Most DBMSs have a row identifier that you can use in SQL statements. While using ROWID allows you to write the fastest possible SELECT, ROWID also poses two dangers. The first is that another user could DELETE the row, which makes the row identifier invalid. The second, and greater, danger is that another user could DELETE the row and then INSERT a new row at the same location, which makes the row identifier a valid, but false, pointer.

The only completely safe use of ROWID is inside a serialized transaction.

Could somebody explain this?

Also navision uses server sided cursors, and reading about cursors,

To avoid WHERE CURRENT OF and cursor trouble, use a ROWID or serial column.

NavStudent

one more quote. This one is a bit extream.

If all rows in the table are to be deleted, it's a better idea to execute DROP TABLE and then re-create the table or to use TRUNCATE.

NavStudent

another suggestion for inserts

If a particular, lengthy, column value appears frequently in INSERT, save network transmission by making the value the default value for that column.

I know navision has default values, but does copy them to sql default values?

bbrown

NavStudent wrote:

one more quote about the order of fields added to a table.

Recommendation: Put columns in the order that users will expect to see when they execute SELECT *: generally, related columns grouped together, and the primary key on the left, followed by columns that are frequently used for searches or modified by the users.

The developer should control how information is presented in the application. The order of fields in the table becomes irrelevant to the application user. Also avoid the use of select *.

kriki

NavStudent wrote:

and another question about ROWID and is Nav using this? or could use it?

Most DBMSs have a row identifier that you can use in SQL statements. While using ROWID allows you to write the fastest possible SELECT, ROWID also poses two dangers. The first is that another user could DELETE the row, which makes the row identifier invalid. The second, and greater, danger is that another user could DELETE the row and then INSERT a new row at the same location, which makes the row identifier a valid, but false, pointer.

The only completely safe use of ROWID is inside a serialized transaction.

1) Don't use it, not even in pure SQL. It may be fast, but the dangers are too high.
2) Some comparison : Let's take some street address. At a certain moment of time a certain person lives there. And you can use it to point to that person. If that person leaves the house (=DELETE) and another person moves into the house (=INSERT), the address is still the same, BUT the person has changed and it points to the wrong person (=record). In this case you should have used the persons name (for this example I am considering the persons name is unique!).

cnicola

[/quote]

So does this mean Clustered index shouldn't be the entry no. but
for example Item No., Entry No. and have 99 % fill rate so that new entries get filled inbetween?

thanks.[/quote]

Hi NavStudent,

The answer to your questions is: "Depends"
Like always you have to balance between transaction speed and reporting speed.
On Item Ledger Entry let's say would seem to make logical sense to have say Item No., Posting Date as your clustered index. Reads would be so much faster.
However since clustered index is how the data is actually stored on the disk when you do an insert the system will have to insert somewhere in the middle of the table which will lead to much higher insert time.
If your primary key is Entry No. the inserts always happen at the end of the table so they will be very fast.
So depending which one you do a lot more often it may make sense to change it or not.
I actually changed it for Item Ledger Entry and Value Entry in an attempt to speed up Adjust Cost routine but since I have a lot of inserts happening I had to revert back to Entry No. (I think it was something like 5 ms for Entry No. and 110 ms + for Item No., Valuation Date for Value Entry).
Hope that helps a little.

cnicola

[/quote]

The developer should control how information is presented in the application. The order of fields in the table becomes irrelevant to the application user. Also avoid the use of select *.[/quote]

I believe the suggestion to "avoid use of Select *" should be directed to MS

NavStudent

kriki wrote:

1) Don't use it, not even in pure SQL. It may be fast, but the dangers are too high.
2) Some comparison : Let's take some street address. At a certain moment of time a certain person lives there. And you can use it to point to that person. If that person leaves the house (=DELETE) and another person moves into the house (=INSERT), the address is still the same, BUT the person has changed and it points to the wrong person (=record). In this case you should have used the persons name (for this example I am considering the persons name is unique!).

Isn't that what time stamp is for?

kriki

NavStudent wrote:

kriki wrote:

1) Don't use it, not even in pure SQL. It may be fast, but the dangers are too high.
2) Some comparison : Let's take some street address. At a certain moment of time a certain person lives there. And you can use it to point to that person. If that person leaves the house (=DELETE) and another person moves into the house (=INSERT), the address is still the same, BUT the person has changed and it points to the wrong person (=record). In this case you should have used the persons name (for this example I am considering the persons name is unique!).

Isn't that what time stamp is for?

Not in this case. Because first you delete a record and then (also maybe after a few days) you insert another record that can have the same ROWID.

colin_leversuch-roberts

sql server does not provide rowid's in the sense of this discussion.