search

ivylabs / suitecrm-analytics

4 stars 4 forks source link

Review Data Export SQL Query Performance #213

Closed harrisward closed 3 years ago

harrisward commented 3 years ago

Describe the bug When running cases and leads exports the query looks like it takes a long time. Check the SQL queries in the Table Input steps and make sure that the keys are indexed

**Component What component does this bug relate to? Other

**Solution File Optimise SQL queries

To Reproduce Steps to reproduce the behavior:

  1. Go to data exported
  2. Run an export with all the filters set to "All"

Expected behavior Faster exports / SQL queries

harrisward commented 3 years ago

Also check the Excel writing step in the ETl as this make take time to generate and NOT the query performance

fraserward commented 3 years ago

Generating an excel sheet with (excel output or excel writer) in Spoon is a inefficient both in time and resources. It is not recommended that this is used with any large data. Using csv / txt output step is recommend.

Any transformations using excel output steps should be reviewed.

New transformations should not use this step unless circumstances of small data returns data can be guaranteed.