Thanks everyone... I tried a join but in my testing it made my method of creating journals of 300 lines 5 seconds slower per journal so i guess in this case a join might not be the best But thanks for all your advice it is great to be able to get quality help and feedback :) Thanks everyone once again :) James _____
From: development-axapta@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of stuzimmy Sent: 02 November 2007 12:05 To: development-axapta@yahoogroups.com Subject: [development-axapta] Re: select first only James, A better query might be to join the two tables. Why select WMSPallet and then query InventPalletBT. You could be looping throught thousands of records only to find a match in the other table on a small subset. Let your DBMS handle that for you. The only way to guarantee the records are fetched in any particular order is to order them that way in your where clause. Otherwise you're at the mercy of the indexes on the table and results can vary. Hope this helps. while select WMSPallet order by WMSPalletId join InventPalletBT where inventPalletBT.PalletID == WMSPallet.wMSPalletID { } --- In development- <mailto:development-axapta%40yahoogroups.com> [EMAIL PROTECTED], James Flavell <[EMAIL PROTECTED]> wrote: > > Hi again everyone, > > 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.wMSPalletId; > > } > > > 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] > [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
