Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Douglas Hart]

I am processing a huge File —- PM30200 — AP History

I am thinking if I add a date key – I can retrieve records quicker —- my SQL execution plan is showing that

it is doing a complete table scan and I am hoping a date key and a where date = ‘xx-xx-xxxx’ will speed it up significantly

Thanks

——————————
Douglas Hart
Dynamics Administrator
Forex Capital Markets
New York City NY
——————————

[Bruce Strom]

To the best of my knowledge you can add and index in SQL directly, Dr. Google agrees, search “adding index to great plains dynamics table”.

Dr. Google suggests:

http://www.accoladepublications.com/GP-System-Tips/increasing-performance.html

Use an index name that varies from the Great Plains naming convention.

——————————
Bruce Strom
Programmer
Associated Grocers of Florida
sunrise FL
————————————————————————-

[Curtis Cruz]

Just remember, when creating indices on GP tables, after upgrading, your indices will disappear.  This is due to GP copying the data from it’s original tables during an upgrade, it then creates the new table to include any changed fields or indices, and copies the data back to the newly created table thus removing your index.

So, be sure to script your indices to be used after an upgrade or service pack install if the upgrade hasn’t already added the index you are looking for.

  

——————————
Curtis Cruz
Database Administrator
Apex Healthcare Partners LLC
Montebello NY
————————————————————————-