Joining should not run slower than a while select with a second query inside it. Make sure you have valid covering indexes for the query.
Good luck. --- In [email protected], James Flavell <[EMAIL PROTECTED]> wrote: > > 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: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of stuzimmy > Sent: 02 November 2007 12:05 > To: [email protected] > 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 <djf1994@> > 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] >
