RE: Spam:Re: [Axapta-Knowledge-Village] Clusteredindex on inventsum & inventdim

Sun, 18 Sep 2005 23:56:42 -0700

hi
 
cluster index slows down inserts (or am I wrong), if you activate serial numbers then you keep on adding records to inventdim table (I assume so, never worked on serial numbers)
 
regards
 
harry


TVG Tom Vergote <[EMAIL PROTECTED]> wrote:

Hi Harry, thanks for your response,

 

How does having serial number active change anything?

 

I still don’t feel to confident setting that index, maybe there is something else wrong with this query?

 

select count(recid) from InventSum
where InventSum.itemId == WMSOrderTrans.itemId
&& InventSum.Closed == false
&& InventSum.PhysicalInvent > 0
exists join inventDimCount index hint ADUSearchIdx
where inventDimCount.inventDimId == InventSum.inventDimId
&& (inventDimCount.configId == inventDim.configId
&& inventDimCount.InventSizeId == inventDim.InventSizeId
&& inventDimCount.InventColorId == inventDim.InventColorId
&& inventDimCount.InventLocationId ==
inventDim.InventLocationId
&& inventDimCount.wMSLocationId == zone
&& inventDimCount.inventBatchId;

 

Regads Tom

From: Axapta-Knowledge-Village@yahoogroups.com [mailto:Axapta-Knowledge-Village@yahoogroups.com] On Behalf Of Harry (Harshawardhan Deshpande
Sent: vrijdag 16 september 2005 8:30
To: Axapta-Knowledge-Village@yahoogroups.com
Subject: Spam:Re: [Axapta-Knowledge-Village] Clusteredindex on inventsum & inventdim

 

hi

 

There might be some implications of changing the value of primary index (for e.g. caching)

 

You are absolutely right about the cluster index on inventDim, you MAY have performance problems only if you are serial number dimension is active

 

regards

 

harry

TVG Tom Vergote <[EMAIL PROTECTED]> wrote:

I'm investigating a performance issue at a customer's site and I've narrowed
it down to a single query involving inventsum & inventdim.
The problem is pretty strange, needing to reindex every now and then.

For many reasons it's advised to have a clustered index on every table
(would avoid the fragmentation i'm seeing now for example) but inventsum and
inventdim have no clustered index, and inventdim hasn't even got a primary
key (dimidx is defines as allowduplicates=no)

But I'm wary of changing these, because I assume since these are very core
tables, navision/ms evaluated the options and picked this design for a reason.
Then again I don't see how adding a clustered index on inventdimid (always
increasing, never gets changed) could hurt that much

Anyone have any insight?
Could I create trouble elsewhere if I add those primarykeys and clustered
indexes? Are these design decisions maybe taken because oracle needs to be
supported?

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Sharing the knowledge on Axapta.



SPONSORED LINKS
Business finance course Business to business finance Small business finance
Business finance consultant Business finance magazine Business finance uk

YAHOO! GROUPS LINKS



Reply via email to