Ok, this seems to be a very healthy conversation. I know that the dataAreaId is apart of all indexes so this should lead to a performance boast if we select the dataAreaId in cases where no field is required from the database.
Regards, Varden Morris 276 Taralake Terrace N.E. Calgary, Alberta T3J 0A1 Canada (403) 366-8434 (H) (403) 615-1604 (C) [EMAIL PROTECTED] ----- Original Message ---- From: Harry Deshpande <[EMAIL PROTECTED]> To: "Axapta-Knowledge-Village@yahoogroups.com" <Axapta-Knowledge-Village@yahoogroups.com> Sent: Thursday, November 1, 2007 10:37:28 AM Subject: RE: [Axapta-Knowledge-Village] select firstonly No, I don't think tableId is part of the index. Let me check the actual SQL syntax generated and get back to you. Regards harry From: Axapta-Knowledge- [EMAIL PROTECTED] ups.com [mailto:Axapta-Knowledge- [EMAIL PROTECTED] ups.com] On Behalf Of Varden Morris Sent: Thursday, November 01, 2007 9:36 AM To: Axapta-Knowledge- [EMAIL PROTECTED] ups.com Subject: Re: [Axapta-Knowledge- Village] select firstonly Ok, I think I may understand what you are saying. Are you refering to a cover index where the database uses the fields in the index page as the data without using the data page (provided that all the required data fields are in the index)? If this is so, are you saying that the tableId is usually apart of all indexes created for tables in DAX? Regards, Varden Morris 276 Taralake Terrace N.E. Calgary, Alberta T3J 0A1 Canada (403) 366-8434 (H) (403) 615-1604 (C) [EMAIL PROTECTED] com<mailto:vardenm% 40yahoo.com> ----- Original Message ---- From: Harry Deshpande <[EMAIL PROTECTED] com<mailto:harryd% 40microsoft. com>> To: "Axapta-Knowledge- [EMAIL PROTECTED] ups.com<mailto:Axapta- Knowledge- Village%40yahoog roups.com> " <Axapta-Knowledge- [EMAIL PROTECTED] ups.com<mailto:Axapta- Knowledge- Village%40yahoog roups.com> > Sent: Thursday, November 1, 2007 10:29:52 AM Subject: RE: [Axapta-Knowledge- Village] select firstonly Hi If recid is not part of the index then the logic has to do one more lookup to get the recid. With tableId you get rid of this problem. There are some inventory report where I remember this design has been implemented. Regards Harry From: Axapta-Knowledge- [EMAIL PROTECTED] ups.com [mailto:Axapta- Knowledge- [EMAIL PROTECTED] ups.com] On Behalf Of Varden Morris Sent: Thursday, November 01, 2007 9:27 AM To: Axapta-Knowledge- [EMAIL PROTECTED] ups.com Subject: Re: [Axapta-Knowledge- Village] select firstonly Hey Harry, This sounds new and unclear to me. Can you please give me some more details on what you are saying here? Regards, Varden Morris 276 Taralake Terrace N.E. Calgary, Alberta T3J 0A1 Canada (403) 366-8434 (H) (403) 615-1604 (C) [EMAIL PROTECTED] com<mailto:vardenm% 40yahoo.com> ----- Original Message ---- From: Harry Deshpande <[EMAIL PROTECTED] com<mailto:harryd% 40microsoft. com>> To: "Axapta-Knowledge- [EMAIL PROTECTED] ups.com<mailto: Axapta- Knowledge- Village%40yahoog roups.com> " <Axapta-Knowledge- [EMAIL PROTECTED] ups.com<mailto: Axapta- Knowledge- Village%40yahoog roups.com> > Sent: Thursday, November 1, 2007 10:15:56 AM Subject: RE: [Axapta-Knowledge- Village] select firstonly Hi May be worthwhile to ' join tableid from inventTable' . This will help slightly with indexes that do not have recId. Regards Harry -----Original Message----- From: Axapta-Knowledge- [EMAIL PROTECTED] ups.com [mailto:Axapta- Knowledge- [EMAIL PROTECTED] ups.com] On Behalf Of Varden Morris Sent: Thursday, November 01, 2007 8:37 AM To: Axapta-Knowledge- [EMAIL PROTECTED] ups.com Subject: Re: [Axapta-Knowledge- Village] select firstonly You are most welcome. I have done countless tuning with joins as oppose to individual selects and see major improvement in performance when the dataset is large. Another thing you could do is "field select" instead of selecting all the fields from each table. This can lead to major performance improvement. If a table is needed in a join for a filter condition and the fields are not being used I usually select the RecId field only to improve performance instead of selecting all the fields. For example, while select ItemId, LineAmount from salesLine join recId from inventTable where inventTable. ItemId == salesLine.ItemId && inventTable. ItemGroupId == "XXXX" { //Use ItemID and LineAmount (only) here } Selecting two out of 60 fields means a significant reduction in the amount of data being fetched from the database sitting on one server to the AOS and/or client on other computers across a network. You have to be careful with the field select at times. It can lead to a bug if you decide to use an additional field and you forget to add it to the select statement. Regards, Varden Morris 276 Taralake Terrace N.E. Calgary, Alberta T3J 0A1 Canada (403) 366-8434 (H) (403) 615-1604 (C) [EMAIL PROTECTED] com ----- Original Message ---- From: James Flavell <[EMAIL PROTECTED] com> To: Axapta-Knowledge- [EMAIL PROTECTED] ups.com Sent: Thursday, November 1, 2007 9:17:25 AM Subject: RE: [Axapta-Knowledge- Village] select firstonly Thanks Varden, Yes you are very correct. I guess I will test with the join. The concern I had was both tables are quite large in number of transactions so I was not sure join would work out to be the best approach but I think yes I should test it out to see. :) Thanks James _____ From: Axapta-Knowledge- [EMAIL PROTECTED] ups.com [mailto:Axapta- Knowledge- [EMAIL PROTECTED] ups.com] On Behalf Of Varden Morris Sent: 01 November 2007 11:02 To: Axapta-Knowledge- [EMAIL PROTECTED] ups.com Subject: Re: [Axapta-Knowledge- Village] select firstonly You can surely save on the number of calls being made to the database by joining the two tables in the while select instead of separate calls to the database for the while select followed by another select. If you do not use the index hint the database will choose an optimum index, provided that indexes exist and the database is configured to collect and update the statistics periodically. Regards, Varden Morris 276 Taralake Terrace N.E. Calgary, Alberta T3J 0A1 Canada (403) 366-8434 (H) (403) 615-1604 (C) [EMAIL PROTECTED] <mailto:vardenm% 40yahoo.com> com ----- Original Message ---- From: James Flavell <[EMAIL PROTECTED] <mailto:djf1994% 40gmail.com> com> To: Axapta-Knowledge- <mailto:Axapta- Knowledge- Village%40yahoog roups.com> [EMAIL PROTECTED] .com Sent: Thursday, November 1, 2007 8:05:29 AM Subject: [Axapta-Knowledge- Village] select firstonly Hi again everyone, Thanks Christoph for the feebdack on index. I have added indexes just to play safe. Another query I have is with the firstonly command. I understand basically how it works but want to know how it really works in practice Given this code: while select WMSPallet order by WMSPalletId { select firstonly InventPalletBP where InventPalletBP. PalletId == WMSPallet.wMSPallet Id; } Is it better to use firstonly in such a situation? I mean I am going to be looping through pallets so if I dont use firstonly a number of inventpalletbp table records will be fetched together which in theory would be a good thing to save on database calls. But then i am wondering will the records that are fetched be in any particualr order (e.g. by palletid) or just from the same page in the SQL server? I guess it should be read ahead according to the where clause or do I need to explicitly put an order by or index command against the InventPalletBP select statement to be sure of this? Looking forward to your input Thanks James [Non-text portions of this message have been removed] ____________ _________ _________ _________ _________ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail. <http://mail. yahoo.com> yahoo.com [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] ____________ _________ _________ _________ _________ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail. yahoo.com [Non-text portions of this message have been removed] Sharing the knowledge on Axapta. Visit www.frappr.com/ axapta for axapta friends. Come and choose your preferred name for Microsoft Dynamics AX on the Axapta Knowledge Village, Visit www.axapta-knowledg e-village. tk Yahoo! Groups Links ____________ _________ _________ _________ _________ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail. yahoo.com [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] ____________ _________ _________ _________ _________ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail. yahoo.com [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com [Non-text portions of this message have been removed]
