Microsoft Dynamics GP (Archived)

Making a SQL Query Execute Quickly

(0) Share

Report

Posted on by Community Member

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 display its data in SQL Management Studio.

In researching, 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?

Thank you!

John

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Community Member on at

Like (0)

Report

Oh, 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?

Was this reply helpful? Yes No
Suggested answer

Praveen Kumar RR 1,552 on at

Like (0)

Report

Hi John,

Please provide me list of columns you are trying to select in your select statement, i will create a view for you.

Creating Stored Procedures will be effective when compared to such large SQL Queries.

I am ready to create either Stored Procedure or View or Both for you based upon your requirement.

Thanks,

Praveen

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Praveen:

Thank you, for offering to conduct the programming. But, I have already done so.

I have tested pieces of my query's "UNION" statements against our data at great length and I feel like I know what I need to do. I'm, also, familiar with creating views and stored procedures.

It's just that my query is very slow. Last time, it took two hours to run. Now, it's taking five hours and does not finish executing!

I just want to know the ramifications of creating indexes, especially in terms of preventing problems during upgrades and in preventing corruption of data.

Also, I'd like to know if and how I should create temp tables. I think that I know the syntax. But, I don't know if these temp tables are created as part of a view instead of as a query.

Too, I'd like to know if simply "wrapping" my query within stored procedure syntax will make my query finish executing and display data.

Thank you, so much!

John

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

How often does this data need to be refreshed? Can you create a job that runs at night that first copies the data to another table and then have the next task create an index based on the needs of this query. You could view an execution plan to see what indexes are needed. This way when you come in the next day this query will run much faster because the table will be indexed properly.

Was this reply helpful? Yes No