When your Eclipse data has been replicated in the SQL server format, the system does not create indexes for you aside from those necessary for the real-time replication when files are enabled.
You have the ability in the Eclipse SQL screen to add indexes that will be meaningful to you. Each company has different needs. Therefore, there are aside from the necessary standard indexes Eclipse does pre-create additional indexes.
Your data is pushed into many different tables. Each table has a primary key to leverage for pulling your information, as needed. Also, an eclipse_id index is created by Eclipse to support the "deletes and inserts" that real-time replication performs as Eclipse records are changed. You are required to manage the Indexes that will enable your BAQs and Dashboards to perform quickly.
We highly recommend creating indexes within Eclipse. By doing this, you ensure that if a file version changed or the customer elected to re-sync a file regardless of the reason, those indexes are not deleted and you do not have to manually recreate the indexes in the SQL environment. This setup makes ongoing support and maintenance easier.
Important: We caution you not to create too many indexes. Adding indexes increases the SQL data size and "over-indexing" may slow the replication process and increase the size of the SQL database as a whole. |
Eclipse query phrases are not pure SQL. Therefore,when you are looking at the error tool in the BAQ, you are trying to locate the missing indexes so you know what to create.
After you have constructed your business activity query (BAQ), and you either receive a timed out response when you use the Analyze tool, or the response is not as quick as you want, it is likely that some indexes could vastly improve performance.
Note: The following instructions assume a basic working knowledge of SQL Server Management Studio which should already installed and connected to your replicated SQL Server instance.
Copy the Query Phrase text from the front page of the BAQ Editor
Paste the Query Phrase into SQL Server Management Studio in a New Query pane.
Click Execute to run your BAQ query directly in SQL
It is likely that the program will return SQL syntax errors for things such as Calculated fields or BAQ constants because the BAQ Query Phrase is not pure SQL. The SQL Server Management Studio error points to the problematic text which allows you to “clean up” the syntax by removing the offending variable (keep in mind, precision of results and business usefulness is not the objective at this point…the goal is to successfully execute the crux of the SQL command.
Once past any syntax errors, the query will run until it is complete in SQL Server Management Studio and will not timeout after 30 seconds as it does within Eclipse SQL Data Replication.
In SQL Server Management Studio, enable the Include Actual Execution Plan button.
You can also enable this button from the Query menu.
Click Execute.
Click the Execution Plan tab in the results pane at the bottom.
SQL identifies missing indexes with the actual execution plan and highlights them in green.
Place your cursor over the green text and note the specific recommended index.
Proceed to the next section to create that Index.
For example:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [eclipse].[sales_order_generation] ([ship_date])
INCLUDE ([sales_order_id],[ship_branch],[generation_id])
SQL Server Management Studio (SQL Server 2008R2)