Re-ordering Primary Key in SQL Server Option

PaddyMullaney

Hello

I am currently working through a heavily bespoked 3.7 SQL database. We are experiencing performance issues and I am going through ways of eliminatinating these.

looking at one of the primary keys on a bespoke table, I can increase performance by re-oredering the primary key.

It is similar to other tables in that initailly the primary key has been designed with document type at the front. I am moving this to the end as this is the least unique value in the key, and I know that in SQL any filter/search is best to go for the most unique value first.

As I am maintaing all the fields in the primary key, and only re-ordering them I do not foresee any problems with uniqueness, and as there are no SumIndexes or Sift levels maintained I do not in theory see any problems.

Does anyone know or can think of any problems I should be aware of.

Regards

Paddy

g_dreyer

I do hope that you are not attempting to do it on a modified navision base object like Sales Header or Sales Line.

You will experience the following problem:

Anywhere in the code where you do a table.get(primkey1,primkey2). You will have to fix all the code to read something like
table.get(primkey2,primkey1)

You could try File | Database | Information, click on the [Tables] button, then highlight the tables that you want to optimize, then click the [Optimize] button.

I also recall a post where the user had performance issues, and to resolve it they have unticked "Allow find as you type" (you can find this by going File | Database | Alter, then look at the Options tab).

Regards,
gus

kine

look there: http://www.mibuso.com/forum/viewtopic.php?t=5529 (my post about optimizing)...

Read the documentation I mentioned...

PaddyMullaney

g_dreyer

No the object is not a modified standard objects, just based on ths same Header/Line logic, but it is a point I will have to bare in mind as I had considered expanding the changes to other areas.

Thank you.

--

kine

Thanks for that the post you refereed to, it is very good, I certainly need to revisit the "apropriate switches for OS and MS SQL" isasue as this is something I have overlooked.

I have been on a course with the same person, and found it excellent and I am working through the other areas. My thought behind re-ordering the primary key comes more from his comparison of Navision Native using binary search trees, and so being efficent when selecting large sections of data such as "Document Type" first, and the fact that SQL server is more efficent when it uses the most unique value first, so it would be best with the choice of "Document No." as an example.

As SQL Server concatanates the secondary keys defined in Navision to the Primary key, I am inclined to think that this re-ordering could have some advantages.

Paddy

kine

Do not forget, that SQL server is selecting index according only the first field in the index. What is in the end of the index, is irrelevant... Changing this have only impact to selectivity of the clustered index, which is created for the primary key... Other indexes can be only one field length (+PK fields automatically added).

If there will be some index with better selectivity, it will be used instead the clustered index. I think you will gain performane mainly because the update of the clustered index will update smaller part of the index...

PaddyMullaney

Kine

yes, that is my idea, to get the clustered index sorted by the more unique value, which should speed tings up.


But can I just clarify with you that SQL server will select on the first field in the primary key? Do you mean that even if this key is a composite the fist field will only be used?

Incidentaly I am also updating the secondary keys, to achive other improvements.


Thank you.

kine

If you look, only statistic for first field in index are kept and calculated. You can setup MS SQL to have statistics for more field, but standard setup make it in this way - it means that execution plan is based on first field selectivity in indexes....