mibuso.com

Microsoft Business Solutions online community
It is currently Fri Sep 19, 2014 7:53 pm

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Is it dangerous to put custom indexes on SQL Server?
PostPosted: Wed Jul 18, 2012 9:25 pm 
Offline

Joined: Thu Oct 14, 2010 2:35 am
Posts: 63
Location: Orem, Utah
Country: United States (us)
We started utilizing Ignite to do SQL Server database monitoring, and it recommended that we put an index on a specific table in SQL Server. I'm not able to replicate that specific index by creating new Keys on the table, so I wanted to do it directly through SQL. I thought I've heard that you shouldn't do this, but I wanted to ask for advice from everybody.

I've put indexes on SQL server before, but never in a Navision instance, so I'm wondering if it's dangerous to do.

I've tested this out by creating the index in our test system, and it seems like everything's still working okay.

Is it okay to do this with Navision?

_________________
Joseph Dewey
Microsoft Dynamics NAV User


Top
 Profile E-mail  
 
 Post subject: Re: Is it dangerous to put custom indexes on SQL Server?
PostPosted: Wed Jul 18, 2012 10:35 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 9047
Location: 3rd rock from sun
Country: United States (us)
NAV in the key window has a sql index column where you can specify the sql index.
I suggest to follow this process. Create indexes on sql works fine but the problem is that when you do a nav backup, those indexes changes are not copied and thus all the tuning you've done will be lost.

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Is it dangerous to put custom indexes on SQL Server?
PostPosted: Thu Jul 19, 2012 1:56 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
If you add indexes, or any way what so ever touch a Navision database out side of Navision, the database will collapse, all data will be deleted, and the server and any clients connected to it will disintegrate on the spot. It will also locate and delete any and all backups ever created.

or...

So long as you have a good understanding of SQL and don't do anything stupid, of course you can add Indexes to a Navision database. Rashed points out the core issue, that there are cases where Navision will over write what you did, but so long as you save everything as scripts, and be aware of what is being done in Navision and reapply what might be lost you should not have any problems.

Your job is to understand the needs of your users and to provide them with the optimal solution for them. If doing this will help the users to be more efficient then do it, just be aware of what could happen. You key issue is to make sure it doesn't affect performance.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: Is it dangerous to put custom indexes on SQL Server?
PostPosted: Thu Jul 19, 2012 8:32 am 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Oct 16, 2003 8:50 am
Posts: 12440
Location: Brno
Country: Czech Republic (cz)
Quote:
I'm not able to replicate that specific index by creating new Keys on the table

What is the problem?

_________________
Kamil Sacek
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.


Top
 Profile E-mail WWW  
 
 Post subject: Re: Is it dangerous to put custom indexes on SQL Server?
PostPosted: Fri Jul 20, 2012 5:37 pm 
Offline

Joined: Thu Oct 14, 2010 2:35 am
Posts: 63
Location: Orem, Utah
Country: United States (us)
Hi David,

That's a great idea to save everything that I did as scripts, so if (or when) we move the database, then we can recreate all of the customs. Excellent response, destroying the world, and then recreating it before my eyes.

Hi Kine,

The titular problem of my post is that I couldn't find any documentation on how dangerous it is to put custom indexes on SQL Server.

Details on the specific fish that I'm trying to catch with this information is that I'm trying to speed up the batch posting of Item Journals, where we have a batch of about 6000 negative adjustment entries that we need to post at a certain time every day. This batch takes about 30-45 minutes to post, and I'd like to find a way to make it take less than five minutes. I already moved the inventory value entry adjustments and GL entries to a separate batch that runs at night.

I found that the SQL statement that was taking the most time was this one:
Code: Select all
(@P1 varchar(20),@P2 varchar(10))
SELECT SUM("Qty_ (Base)") 
FROM "Prod_Asia"."dbo"."Unicity Thailand$Warehouse Entry" 
WITH 
   (
      UPDLOCK
   ) 
WHERE (("Bin Code"=@P1)) 
AND (("Location Code"=@P2))
Which is very confusing why NAV is trying to sum by location and bin, but not by item, but I trust that the creators of NAV knew what they were doing with inventory posting. And my tool recommended that I make this index:
Code: Select all
CREATE NONCLUSTERED INDEX [location_bin_qty]
ON [Prod_db].[dbo].[Comany A$Warehouse Entry]
([Location Code],[Bin Code]) INCLUDE ([Qty_ (Base)])
But, you can't make that exact index inside of NAV, because NAV always slaps the fields of the primary key on every index, which in this case in [Entry No_].

So, I put that index directly on SQL Server, and it stopped SQL Server from doing a full table scan when NAV posts the Item Journals, and it helped a little, but it didn't significantly speed up the posting.

So, if you have any ideas on my next step for significantly speeding up a huge batch post of negative adjustments in Item Journals, that would be awesome.

_________________
Joseph Dewey
Microsoft Dynamics NAV User


Top
 Profile E-mail  
 
 Post subject: Re: Is it dangerous to put custom indexes on SQL Server?
PostPosted: Sun Jul 22, 2012 10:42 am 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Jul 19, 2005 4:49 pm
Posts: 4180
Location: Olst
Country: Netherlands (nl)
If you read the query and try to interpret it, you see that NAV wants a total of something.

This is where you need to know NAV. NAV uses SIFT for this. This is a built in technology that will maintain these sums automatically during transactions without any coding. In the native database this was done my magic. In SQL this is done by Indexed Views.

That was the sales part.

To fix this issue you need to activate a SIFT. This has to be done from inside the NAV metadata. I would advise to get a NAV specialist explain how to do this.

It's not rocketscience. I bet if you search for SIFT you can do it yourself. Or purchase Jorgs book.

_________________
Mark Brummel | Freelance Dynamics NAV (Navision) Specialist

Author of the book : Microsoft Dynamics NAV Application Design

MY BLOG : http://markbrummel.wordpress.com


Top
 Profile E-mail WWW  
 
 Post subject: Re: Is it dangerous to put custom indexes on SQL Server?
PostPosted: Mon Jul 23, 2012 6:42 am 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Oct 16, 2003 8:50 am
Posts: 12440
Location: Brno
Country: Czech Republic (cz)
From your post we can see that the problem is not "how to create custom index dierctly on SQL", but how to create index "location Code", "Bin Code" (or rather "Bin Code", "location Code" because bin code could have better selectivity) with sumindexfield "Qty. (Base)". And all this could be done from within NAV, as Mark wrote.

_________________
Kamil Sacek
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.


Top
 Profile E-mail WWW  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: Majestic-12 [Bot] and 10 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: