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]
- Original Message
From: James Flavell [EMAIL PROTECTED]
To: Axapta-Knowledge-Village@yahoogroups.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.yahoo.com
[Non-text portions of this message have been removed]
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-Village@yahoogroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of Varden Morris
Sent: 01 November 2007 11:02
To: Axapta-Knowledge-Village@yahoogroups.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%40yahoogroups.com
[EMAIL PROTECTED]
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]
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 ==
{
//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]
- Original Message
From: James Flavell [EMAIL PROTECTED]
To: Axapta-Knowledge-Village@yahoogroups.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-knowledge-village.tk
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/Axapta-Knowledge-Village/
* Your email settings:
Individual Email | Traditional
* To change settings online go to:
http://groups.yahoo.com/group
RE: [Axapta-Knowledge-Village] select firstonly
Thanks a lot Varden,
I have not done much 'tunning' so thinking and trying various bits out so
your advice is great and will save me a lot of time in trial and testings
I am trying to imporve the data import and posting of inventory journals
(P/L and BOM journals) for some data migration scripts
One other thing I am not sure anyone can explain is why does the
JournalCheckPost class take longer to run if I set the DoNotCheck = true? I
would have thought skipping the check procedure would have made the posting
of a journal quicker but it seems to have the opposite effect...
Thanks again Varden
James
-Original Message-
From: Axapta-Knowledge-Village@yahoogroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of Varden Morris
Sent: 01 November 2007 11:37
To: Axapta-Knowledge-Village@yahoogroups.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 ==
{
//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]
- Original Message
From: James Flavell [EMAIL PROTECTED]
To: Axapta-Knowledge-Village@yahoogroups.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
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-Village@yahoogroups.com [mailto:[EMAIL PROTECTED] On
Behalf Of Varden Morris
Sent: Thursday, November 01, 2007 8:37 AM
To: Axapta-Knowledge-Village@yahoogroups.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 ==
{
//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]
- Original Message
From: James Flavell [EMAIL PROTECTED]
To: Axapta-Knowledge-Village@yahoogroups.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
Re: [Axapta-Knowledge-Village] select firstonly
You are most welcome. I have never looked into the problem with the journal
posting but I can point you in the required direction to look at it. You can
enable database tracing in DAX by selecting the SQL statements checkbox on
the tracing tab page of the Server Configuration Utility. On the DAX client
that you will be using to do the trace you should enable SQL trace on the SQL
tab page of the Options form and use a value of about 5000 milliseconds for the
threshold of long queries. Do the latter just before executing the bottleneck
process and disable it immediately after execution. You should then look
through the trace log for possible problems.
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: James Flavell [EMAIL PROTECTED]
To: Axapta-Knowledge-Village@yahoogroups.com
Sent: Thursday, November 1, 2007 9:49:52 AM
Subject: RE: [Axapta-Knowledge-Village] select firstonly
Thanks a lot Varden,
I have not done much 'tunning' so thinking and trying various bits out so
your advice is great and will save me a lot of time in trial and testings
I am trying to imporve the data import and posting of inventory journals
(P/L and BOM journals) for some data migration scripts
One other thing I am not sure anyone can explain is why does the
JournalCheckPost class take longer to run if I set the DoNotCheck = true? I
would have thought skipping the check procedure would have made the posting
of a journal quicker but it seems to have the opposite effect...
Thanks again Varden
James
-Original Message-
From: Axapta-Knowledge- [EMAIL PROTECTED] ups.com
[mailto:Axapta-Knowledge- [EMAIL PROTECTED] ups.com] On Behalf Of Varden Morris
Sent: 01 November 2007 11:37
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 ==
{
//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
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]
- Original Message
From: Harry Deshpande [EMAIL PROTECTED]
To: Axapta-Knowledge-Village@yahoogroups.com
Axapta-Knowledge-Village@yahoogroups.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 ==
{
//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
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-Village@yahoogroups.com [mailto:[EMAIL PROTECTED] On
Behalf Of Varden Morris
Sent: Thursday, November 01, 2007 9:27 AM
To: Axapta-Knowledge-Village@yahoogroups.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]mailto:vardenm%40yahoo.com
- Original Message
From: Harry Deshpande [EMAIL PROTECTED]mailto:harryd%40microsoft.com
To:
Axapta-Knowledge-Village@yahoogroups.commailto:Axapta-Knowledge-Village%40yahoogroups.com
Axapta-Knowledge-Village@yahoogroups.commailto:Axapta-Knowledge-Village%40yahoogroups.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 ==
{
//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
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]
- Original Message
From: Harry Deshpande [EMAIL PROTECTED]
To: Axapta-Knowledge-Village@yahoogroups.com
Axapta-Knowledge-Village@yahoogroups.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] commailto:vardenm% 40yahoo.com
- Original Message
From: Harry Deshpande [EMAIL PROTECTED] commailto:harryd% 40microsoft. com
To: Axapta-Knowledge- [EMAIL PROTECTED] ups.commailto:Axapta- Knowledge-
Village%40yahoog roups.com Axapta-Knowledge- [EMAIL PROTECTED]
ups.commailto: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 ==
{
//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
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-Village@yahoogroups.com [mailto:[EMAIL PROTECTED] On
Behalf Of Varden Morris
Sent: Thursday, November 01, 2007 9:36 AM
To: Axapta-Knowledge-Village@yahoogroups.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]mailto:vardenm%40yahoo.com
- Original Message
From: Harry Deshpande [EMAIL PROTECTED]mailto:harryd%40microsoft.com
To:
Axapta-Knowledge-Village@yahoogroups.commailto:Axapta-Knowledge-Village%40yahoogroups.com
Axapta-Knowledge-Village@yahoogroups.commailto:Axapta-Knowledge-Village%40yahoogroups.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] commailto:vardenm% 40yahoo.com
- Original Message
From: Harry Deshpande [EMAIL PROTECTED] commailto:harryd% 40microsoft. com
To: Axapta-Knowledge- [EMAIL PROTECTED] ups.commailto:Axapta- Knowledge-
Village%40yahoog roups.com Axapta-Knowledge- [EMAIL PROTECTED]
ups.commailto: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 ==
{
//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
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]
- Original Message
From: Harry Deshpande [EMAIL PROTECTED]
To: Axapta-Knowledge-Village@yahoogroups.com
Axapta-Knowledge-Village@yahoogroups.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] commailto:vardenm% 40yahoo.com
- Original Message
From: Harry Deshpande [EMAIL PROTECTED] commailto:harryd% 40microsoft. com
To: Axapta-Knowledge- [EMAIL PROTECTED] ups.commailto:Axapta- Knowledge-
Village%40yahoog roups.com Axapta-Knowledge- [EMAIL PROTECTED]
ups.commailto: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 ==
{
//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
