0

I need your help so i can resolve some problem on my job.

We have some reports that we run on Sql Server Reporting Services. The query on it its T-SQL language but the report is really slow so i ran the "Show Estimated Execution Plan" on SQL Server Management Studio and it tells me that i need the following indexes for the query:

CREATE NONCLUSTERED INDEX [InventJournalTable_JournalIdDataAreaId]
ON [dbo].[INVENTJOURNALTABLE] ([JOURNALID],[DATAAREAID])

CREATE NONCLUSTERED INDEX [InventTrans_InventTransOrigin]
ON [dbo].[INVENTTRANS] ([INVENTTRANSORIGIN],[DATAAREAID],[DATEPHYSICAL])
INCLUDE ([ITEMID],[QTY],[COSTAMOUNTPHYSICAL],[INVENTDIMID])

I created them and the query (before executed in 25 or more minutes) runs now in two seconds. But whenever the sync is run on AX, obviously, the indexes created has to be deleted.

Is there any trigger or something that i can execute when the synchro is finished so i can create again the indexes i need or any other future action?

If i create it on AX it includes the column "Partition" and is not good for my outside query, as it is still really slow.

Thanks in advance for your help.

JGutierrezC
  • 4,398
  • 5
  • 25
  • 42

1 Answers1

1

If you need to execute custom SQL statements when the database is synchronized, you can add your code to Application.dbSychronize(). As an example, you can look in that function to see how the system handles adding a stored procedure to the ShipCarrierStaging table.

Jay Hofacker
  • 3,439
  • 20
  • 14
  • Thanks for your answer Jay. I figured that out and i already have that part ready and working. The thing is that, on the Development Workspace when i do right-click (or on the command menu) on a table and i click Synchronize it also deletes the indexes but that process doesn't pass on Application.dbSynchronize(), i can tell you because i have added a break point on it. Do you have any idea on which class that process run from? – JGutierrezC Mar 07 '13 at 15:07