Hello:
I have created a SQL query that pulls data from the following four tables: RM00101, RM20101, RM20201, and CN00500.
The query is taking nearly two hours to execute and to display its data in SQL Management Studio.
In researching this, the majority of T-SQL experts advise placing indexes within these four tables.
Truthfully, I hesitate to do so for two reasons. First, I have read that having additional indexes can actually degrade SQL performance. Secondly, without the "supervision" of a Microsoft engineer, conducting modifications directly to the GP tables can corrupt those tables and, therefore, compromise GP.
Regardless, is there any harm of placing additional indexes into these tables? If so, per Microsoft Dynamics Best Practices, what is the way to go about doing this?
My query is, in essence, a big select statement. Would simply creating a stored procedure containing this query and executing the stored procedure allow for the data to be displayed much more quickly than two hours?
By the way, I found the article below in italics saying that, within reason, indexes can be added to the four tables.
Based on the syntax mentioned in this article, can you please advise me on what indexes I need to create? I can test in our development environment and advise our DBA of such.
Otherwise, I have heard that using temp tables and indexing in those temp tables would be a good idea. Does anyone have any SQL views that could be posted here to make such temp tables?
Thank you!
John
When hundreds of thousands of records exist in a single table in the database, things can slow down a bit. If the application is looking for information using a key, then this number of records should not be a problem. But to look for a single customer, for example, using their phone number, when the phone number is not indexed, can take some time in a large table.
Supplemental indexes can be added to these tables! Don't get too carried away as too many indexes will slow down the process of adding or updating records as all of the indexes need to be updated as well. But having 3-4 additional indexes should not be a problem.
The following command is typed into the SQL Management Studio in a query window to create a new index. Make sure to backup your database first and do this when no one else is on the system so mistakes can be corrected.
CREATE INDEX index_name ON table_name (column_name)
index_name is a name given to the index. The ERP system uses the rule AKtablename99 to indicate an Alternate Key on the table and serializes the keys. I use RLWKtablename99. Use what you want.
table_name is the name of the table, like SOP10100 for the Sales Transaction
column_name is the name of the column the index should be built upon. Two or more columns can be specified if needed but separate each with a comma.
If the index should be in decending order, add DESC at the end of the command.