Non-clustered index on Sals Shipment Line table

chrisgrill3

Is it safe to create a non-clustered index on the Sales Shipment Line table in SQl? Need to optimize a query used in an external reporting application and it would really increase the performance of the query. Thanks.

kriki

It is safe if done using NAV. It is better not to do it directly in SQL.

But remember that too many indexes slow down all the writing to the table.

BTW: Is your report so important (=run a lot or when run it must give the results very fast for some valid reason), that adding an index is opportune?

matttrax

Yeah, do it in NAV if you are going to do it. View the properties of the key (View --> Keys, Shift+F4), and set the Clustered property here.

chrisgrill3

In this case it is the Sales Shipment Line table which gets written to when shipping. There is enough time between shipping packages that an affect on write speed would be probably go unnoticed. The report is run multiple times throughout the day by accounting personnel and could potentially make them more efficient. I guess if it did not help the report and/or slowed down shipping I could remove the index with no ill effects, correct? Thanks.

David_Singleton

The question is why can't you just make the index withing Navision. In general it is possible to add indexes in SQL, but there are issues you need to b aware of, and if you are not aware of them its safer to just do it the "recommended" way.

rhpnt

chrisgrill3 wrote:

Is it safe to create a non-clustered index on the Sales Shipment Line table in SQl? Need to optimize a query used in an external reporting application and it would really increase the performance of the query. Thanks.

It IS safe and DON'T do it in NAV (as opposed to what others are writing)! Such doing is even suggested for improving db performance of NAV queries - so go ahead - BUT test it first on a separate non production db!

David_Singleton

rhpnt wrote:

It IS safe and DON'T do it in NAV (as opposed to what others are writing)! Such doing is even suggested for improving db performance of NAV queries - so go ahead - BUT test it first on a separate non production db!

There is an old joke:

A guy goes into a car dealership and sees the latest model Rolls Royce. He asks the sales person how much it costs. The salesperson replies "Sir if you need to ask how much it costs, then you can't afford it."

You need to understand that joke before you go giving out advise.

rhpnt

David Singleton wrote:

There is an old joke:
A guy goes into a car dealership and sees the latest model Rolls Royce. He asks the sales person how much it costs. The salesperson replies "Sir if you need to ask how much it costs, then you can't afford it." You need to understand that joke before you go giving out advise.

Hmm... Obviously we don't share the same kind of humor because I don't understand that as a joke, it's more like a fact - like my advice.

stryk

I absolutely agree with "rhpnt"!

NAV indexing just sucks; it has very limited options and the out-of-the-box indexes are insufficient. If you create some - (again: just some) - custom indexes on SQL site you could benefit from all the options available: Includes, Filtered, Descending ...

Creating an index is failsafe as long as you stay away from NAV naming conventions. The worst thing which could happen is that while creating the index you might block users ...

If you proceed like this - thus creating a custom index directly on SQL - you could also work muich quicker: you could implement the index wile users are working; if you change a NAV table the users will get a "definition error" and have to re-logon. If you are using "Enhanced" security you are srewed ...
Hence, you also instantly get a feedback if your solution works or not. No harm if you have to re-create(change your custom index on SQL site - a big hassle on NAV ...

So I highly recommend to proceed like this! At least you could find out how to solve the problem; if this could be done by an index which is compatible to NAV "Keys", well, then you could replace your index with the corresponding NAV Key afterward ...

Of course, if you have this Non-NAV indexing then you need to think about documentation etc. as the index is not visible within NAV.
And last but not least: of course you should know how a proper index should look like! Using Tuning Advisor or other Auto Pilots will NOT do the job with NAV, as their indexes in many cases cause a different problem ...

kriki

chrisgrill3 wrote:

In this case it is the Sales Shipment Line table which gets written to when shipping. There is enough time between shipping packages that an affect on write speed would be probably go unnoticed. The report is run multiple times throughout the day by accounting personnel and could potentially make them more efficient. I guess if it did not help the report and/or slowed down shipping I could remove the index with no ill effects, correct? Thanks.

1 index generally doesn't make a difference (especially on the sales shipment table). But before adding any index, think about it.

The important thing is: don't add indexes directly in SQL Server. It will work without problems, but sooner or later it will bite you in the hand (documenting it in the NAV-table helps because NAV does not know it).

@stryck: let's keep hammering Microsoft they should make available those properties in NAV! Next week we can have another round of hammering!

stryk

kriki wrote:

@stryk: let's keep hammering Microsoft they should make available those properties in NAV! Next week we can have another round of hammering!

Please bring two hammers. Due to private reasons I cannot attend next week ... ](*,)

David_Singleton

stryk wrote:

kriki wrote:

@stryk: let's keep hammering Microsoft they should make available those properties in NAV! Next week we can have another round of hammering!

Please bring two hammers. Due to private reasons I cannot attend next week ... ](*,)

Me too, something just came up and I had to cancel yesterday. So Alain bring three hammers.

rhpnt

kriki wrote:

The important thing is: don't add indexes directly in SQL Server. It will work without problems, but sooner or later it will bite you in the hand (documenting it in the NAV-table helps because NAV does not know it).

So you're suggesting not to create indexes in SQL Server because of documentation issues!? :shock:

kriki wrote:

@stryck: let's keep hammering Microsoft they should make available those properties in NAV! Next week we can have another round of hammering!

Maybe the whole SQL Management Studio should be a part of NAV...

David_Singleton

rhpnt wrote:

kriki wrote:

The important thing is: don't add indexes directly in SQL Server. It will work without problems, but sooner or later it will bite you in the hand (documenting it in the NAV-table helps because NAV does not know it).

So you're suggesting not to create indexes in SQL Server because of documentation issues!? :shock:

No he is saying this is ONE OF the reasons. The point being that if you have to ask then you really shouldn't be doing it until you know.

But to be honest the easiest way to find out if a dog bites is to stick your hand out and see if you get bitten.

JasminkaT

Depending on NAV exe version you're running on, app will be using dynamics or FFO cursors mainly. (FFO mostly on form browsing, dynamic otherwise)
In later versions, it's mainly dynamic cursors. There is significant difference:
For Dynamic cursors, sql query optimizer will tend to optimize for ORDER BY clause.
For FFO, it will tend to optimize for WHERE clause

Meaning: if your index is on sql side only, ORDER by (generated by ndbcs.dll) will contain currently used NAV index (specified in code, or default if none specified).
So in most cases, your query optimizer will be optimizing for this index, and the optimized plan will be created for that one and it is quite likely the index you created will be ignored.
Hence, to be safe, create index on NAV side, then make sure you also use it (code).

There will be exceptions to these rules of course, and scenarios where optimizer will behave differently, but to be safe, create it on nav side.
Either way, it's rather safe. You'll soon discover if it helped or not, and you can take it away.

stryk

http://www.mibuso.com/dlinfo.asp?FileID=1398

Well, you're right about FFC and DC - but actually this has got nothing to do with the subject of creating indexes on SQL Server site.
Actually DC is another PRO on this site: DC screw up if there's a remarkable difference between ORDER BY fields and WHERE fields. With NAV it is granted (by default) that ever "Key" (= ORDER BY) has a corresponding 1:1 SQL index. Thus, DC only work well if you only filter on fields which are used in the sorting order ... this means, that you have to create another "Key" whenever you want to filter on different fields ...

On SQL site you could also create indexes which fully support DC - independently from NAV: there are cases where you want to use a different sorting order apart from the filter fields!
Again: you need to know what to do and how to do it right - but this is something everybody could learn!

David_Singleton

stryk wrote:

Again: you need to know what to do and how to do it right - but this is something everybody could learn!

=D> =D> =D>

Exactly the point I am trying to make. Just like the guy could find out the price of the Rolls Royce before he starts visiting the show room.

rhpnt

As usual, the longer the thread the more it deviates from the original case. This guy posted a simple clear question (which is rare for this forum). Instead of a clear answer he gets tons of useless posts about whether or not he/she should do it in the NAV Client, why the NAV Client is unable to manage MS SQL Server indexes, jokes about buying a Rolls Royce etc... The only connection to NAV in the initial post is that the index should be created on a NAV table. For MS SQL Server a NAV table is a simple table. Nothing more and nothing less. Why is everybody debating about the NAV client if a third party reporting application needs a special index on a SQL Server table?

JasminkaT

you're quite right, I don't think any of us read the question properly... he did mention EXTERNAL application...

chrisgrill3

Thanks for all the replies. I am going to do it in SQl, just wanted to make sure NAV would not complain if the index was created outside of it.

stryk

NAV would only complain if you use its naming conventions, or if you overwrite an existing NAV index - so don't name them $1 or $2 or $something. If you stay away from these NAV thingys NAV is not aware of your customized index, but SQL is able to use it. But have in mind, that under few circumstances NAV could drop/recreate a table or view, then your index would vanish.
(On "Sales Shipment Line" it's rather unlikely that this would happen)

matttrax

rhpnt wrote:

As usual, the longer the thread the more it deviates from the original case. This guy posted a simple clear question (which is rare for this forum). Instead of a clear answer he gets tons of useless posts about whether or not he/she should do it in the NAV Client, why the NAV Client is unable to manage MS SQL Server indexes, jokes about buying a Rolls Royce etc... The only connection to NAV in the initial post is that the index should be created on a NAV table. For MS SQL Server a NAV table is a simple table. Nothing more and nothing less. Why is everybody debating about the NAV client if a third party reporting application needs a special index on a SQL Server table?

The point is not to always just answer the question. If someone asked about adding fields from SQL we could say "Yes you can", but that wouldn't be good advice. It's important to talk about best practices, where things can or might go wrong, so that everyone can learn.

DenSter

rhpnt wrote:

Why is everybody debating about the NAV client if a third party reporting application needs a special index on a SQL Server table?

Because when we say "yes" to the question is "can I add indexes", without qualifying, most of the time, the external app's developers think it's safe to do whatever they feel like to the table design right in SQL Server, because "it is just another table". The result is often that indexes get screwed up, performance goes down the drain, fields get added or modified, and you have a huge mess on your hands.

For instance: viewtopic.php?f=33&t=49839

The added value of forums like this is that people have experiences to share, and caution is usually warranted when you're talking about modifying table design straight on SQL Server.

Would I allow Jorg to modify my customer's SQL Server table? Absolutely, because HE knows what he can/cannot do. Would I allow just any other SQL Server DBA do that? Not on your life, because I know what it's like to fix the mess that gets left.

rhpnt

DenSter wrote:

Because when we say "yes" to the question is "can I add indexes", without qualifying, most of the time, the external app's developers think it's safe to do whatever they feel like to the table design right in SQL Server, because "it is just another table". The result is often that indexes get screwed up, performance goes down the drain, fields get added or modified, and you have a huge mess on your hands.

Don't agree on that and you are generalizing too much. We're talking about adding one index to a SQL table. That doesn't mean that "everything else" is allowed. After reading the thread one gets the impression of quite the opposite - that "nothing" beyond the NAV client is allowed.

DenSter wrote:

The added value of forums like this is that people have experiences to share, and caution is usually warranted when you're talking about modifying table design straight on SQL Server.

I understand caution as that someone is encouraged to do/try something with the appropriate measure of safety. This thread however is saying the complete opposite: "Don't even try it!" - with no real argument.

DenSter wrote:

Would I allow Jorg to modify my customer's SQL Server table? Absolutely, because HE knows what he can/cannot do.

I respect Jörg as an expert very much but even he wasn't born with the SQL knowledge he has today. It was all trial/error and countless hours of experimenting (also my experience).

DenSter wrote:

Would I allow just any other SQL Server DBA do that? Not on your life, because I know what it's like to fix the mess that gets left.

Again you're off topic - we don't know what position this guy has (DBA, developer, NAV consultant,...) and it really doesn't matter.The question he asked points out that he knows SQL indexes and is aware of the danger by doing something without asking first. If he screws up then he'll have to clean it up and learn something from it. Didn't we all? And sure, it's always easier to leave things as they are - but with such attitude we'd still be on version 1.0.

kriki

DenSter wrote:

Would I allow Jorg to modify my customer's SQL Server table? Absolutely, because HE knows what he can/cannot do. Would I allow just any other SQL Server DBA do that? Not on your life, because I know what it's like to fix the mess that gets left.

I can add something about a not so "any other SQL Server DBA": a year or 2 ago, I talked to an Italian SQL Server MVP(!), specialised in performance tuning, and he told me that he has been asked a few times to tune a DB that turned out to be a NAV DB. He said to the customer that he doesn't touch that kind of DB's because he knows he can do more damage than heal.

DenSter

rhpnt wrote:

Don't agree on that and you are generalizing too much

Fixing performance problems on SQL Server is a large part of my job, and by the time customers get to me there's always something wrong. It doesn't happen every time, when people do start modifying the table design directly on SQL Server, and there are problems, those problems are generally very significant. You wouldn't believe some of the problems that I've seen caused by changing table design directly, and it always starts with "can I add an index?". Then another index is added, and then an existing index is changed, and then a data type is "fixed", or fields get added.

In my opinion, when you need an additional index, the best way to go about doing that is to add it in the NAV table designer, and we'll just have to agree to disagree there. The inconvenience of having users restart their NAV session is minor compared to the problems that could be caused by doing something wrong. It's much easier to manage, because this way the table design is inside the NAV object at all times, and you don't have the additional layer of objects to manage.

DenSter

rhpnt wrote:

with such attitude we'd still be on version 1.0.

You know I get that you like to stir the pot, and I usually enjoy seeing people overreact to your banter, but I think that's a bit unfair. We're not here to keep people from evolving or anything. These issues that we're trying to prevent are real issues, and yelling "off topic" and dismissing people's warnings is not helping.

I guess the important piece here that we all recognize is the fact that the OP was cautious enough to ask, which is a good thing. What I see happen a lot though, is that after that first question, the next index is added without communicating, and it goes bad really fast from there.

Maybe what we should preach here is that when there is an external system that needs indexes, that the admins for both systems communicate well together.

matttrax

DenSter wrote:

Maybe what we should preach here is that when there is an external system that needs indexes, that the admins for both systems communicate well together.

That's always my concern of doing things outside of NAV that can be done inside of it.

Like adding trigger code in SQL. I've seen trigger code added that deleted an order during posting, and then when the NAV code came along that did the same thing it errored out. Couldn't post for a couple of days, and because it was done directly in SQL, but happening in NAV, it was damn near impossible to figure out for the NAV developer (i.e. me).

Anyway, like Denster said, the point is to be careful if you're doing things outside of NAV. There may be a good deal of semi-off-topic responses here, but if someone comes along and reads them, and they get that person thinking about the right things, then I think they have done their job of educating.

stryk

Well, as this is drifting into a general debate about "DOs & DON'Ts" I'd like to add my two cents:
For many years now, even back in native times, NAV developers have learned to "extpand" the NAV capabilities whenever necessary. There were OCX/COM components created, later on .NET stuff etc. and integrated into the NAV system.
IMHO expanding NAV by using SQL features follows the same strategy!

And - as mentioned before - whenever you fiddle with something, you have to do it right! But this does not just apply to createing SQL indexes or triggers, this also means programming .NET or COM components or whatever.
And last but not least: this applies to the C/AL programming as well.

Hence, it's all a matter of skill, knowledge & experience ...

matttrax

stryk wrote:

And - as mentioned before - whenever you fiddle with something, you have to do it right! But this does not just apply to createing SQL indexes or triggers, this also means programming .NET or COM components or whatever.
And last but not least: this applies to the C/AL programming as well.

Hence, it's all a matter of skill, knowledge & experience ...

Totally agree.

For the programming side, though, I can see a call to a .NET component in the code. Those things are integrated, but as a developer I can see them along with everything else. I can't see SQL triggers or indexes added through SQL, and when they cause problems in NAV, it is a royal pain. I realize I might not be able to see the code in the .NET or OCX components either, but at least I know they are being called. Personal opinion here, and jaded from bad experiences , but unless there is a seriously beneficial reason to do something like this directly in SQL, it is more trouble than it is worth.

David_Singleton

stryk wrote:

Hence, it's all a matter of skill, knowledge & experience ...

Jorg,

whilst I totally agree with this, I wonder if you do? Because this statement completely contradicts the argument that you are supporting. As I see it there are two points of view here, one that knowledge in itself is important, the other being that skill and experience are just as important as knowledge. Or to paraphrase Matt, do we tell people where to buy fish (knowledge) or do we teach them how to fish (skill) and the give them time to learn to fish (experience).

In general (and including this thread) one of the reasons your contributions are so valuable, is that you not only share your knowledge, but you also add tips from your experience. You also encourage people to gain skills. But the other side of this thread is; do we tell people where to buy fish, if that person does not have the skill and experience to be able to buy the right fish. And knowing that next time they will again go to the same market maybe to buy a completely different fish. One that that market does not have.

The important issue here that most of us (but not everyone) understands is that in a years time someone (a complete beginner) will find this thread, and simply read the reply they want "yes its safe to go playing in the SQL back end" that is all they will read and they will damage a client. The question here (and I have asked this many times) is do we as a community have some obligation to provide valuable help back to the long term community, or should we just answer the question and move on.

And to be honest I am pretty sick and tired of threads like this, where it seams that trying to do the right thing only gets you in trouble, better when some one asks "is it possible to increase the length of the "No." field in the Item table, just to answer YES and close the thread.


http://dynamicsuser.net/blogs/singleton ... ision.aspx