Tip of the week from Brenda Hopkins – Week 9

Speed up the Population of the JE numbers on the Correct / Copy Journal Entry Screen

Looking up Journal Entry corrections is slow because of the number of transactions.

GP provides an index for that table that can speed look-ups.

A small SQL Script to apply a non-clustered index to the GL20000 and GL30000 tables and this solves the problem.

Before running this Script make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. Click Connect.

3. On the File menu, point to New, and then click Query with Current Connection.

4. Copy the following code in the query pane.

CREATE NONCLUSTERED INDEX [SEE_GL30000PerformanceCustom] ON [dbo].[GL30000]
( [Back_Out_JE] ASC,
[HSTYEAR] ASC ) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [SEE_GL20000PerformanceCustom] ON [dbo].[GL20000]
( [Back_Out_JE] ASC,
[OPENYEAR] ASC) ON [PRIMARY]
go

5. In the database list, click the company database that you want to use, and then click Execute.

6. Repeat step 5 for each company database in which the problem occurs.