Speed Up your EZWriter Data Source (v19)

EZWriter Data Sources can be used to extract data out of the Momentus Enterprise database, which can then be used within your EZWriter Data Formats.  During this process, the data can then have calculations performed on it, or be manipulated in a number of ways.  Depending on the complexity of your EZWriter Data Source or the amount of data that is being returned, the EZWriter Data Source may take some time to run, or it may time-out before any results are returned.

The EZWriter Data Sources are created using SQL (Structured Query Language), and many recommendations for speeding up generic SQL queries also apply to EZWriter Data Sources.

One of the largest factors that can slow down an EZWriter Data Source is the sheer amount of data that is being returned.  This data typically consists of a number of rows, with each row displaying a number of fields within a table-like structure.  This data is then forwarded to the EZWriter Data Format, which also has to process all of the data.  Therefore, the overall speed of the report can be increased by limiting the amount of data being returned by the EZWriter Data Source.

1. Reduce the number of fields being returned by the EZWriter Data Source.
   Only return the fields from the Momentus Enterprise database that are going to be used within your report.
   Avoid using SQL statements such as 'SELECT * FROM EV870_ACCT_MASTER' which will return every field within that table.
   Instead, use 'SELECT EV870_ACCT_CODE, EV870_NAME FROM EV870_ACCT_MASTER' (or similar) where the required field names have been specified.
    
2. Reduce the number of rows being returned by the EZWriter Data Source.
   This can be done in a number of ways, and will greatly depend on the required format of the resultant report, but some examples may be to add additional WHERE clauses to filter the data based on certain criteria (such as a date range), or to use aggregate functions to summarize the data being returned.
   During testing, it can also be helpful to limit the number of rows being returned using the TOP clause, such as 'SELECT TOP 100 * FROM EV870_ACCT_MASTER'.
    
3. Reduce the number of fields and the number of rows being returned by the EZWriter Data Source.
   Combine both of the tips above to further improve performance.
    

If the EZWriter Data Source is still taking a long time to run, it is recommended to use tools such as Microsoft's SQL Server Management Studio that has a feature called 'Display Estimated Execution Path' which can show you the parts of your SQL query are taking the longest to execute, and therefore which areas you can try to optimize.

Some other generic SQL performance tips can include:

• Use table joins instead of subqueries
• Be careful when using wildcards (eg. 'WHERE FIELD LIKE '%')
• Use UNION instead of OR
• Avoid subqueries within the IN clause

If you have other SQL tips that can help speed up the EZWriter Data Sources, please include them in the comments below.