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

[TVG Tom Vergote]

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 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  Visit your group "Axapta-Knowledge-Village" on the web.    To unsubscribe from this group, send an email to:  [EMAIL PROTECTED]    Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.