Issue: DataGarage download times of certain tables taking too long.
Version: All versions of DataGarage
When downloading tables in DataGarage, especially large tables, there are some modifications that can be made to the download process which can improve performance and decrease download times.
- Delete indexes before each refresh.
- Remove any columns from the tables that are not referenced/needed in any of the webapps.
- Bring down only records required for the webapps.
The steps for each of the above modifications are below:
Delete indexes before each refresh.
(DataGarage 5.5 shown)
- Verify that the Refresh Index Option is set to Build Indexes and Primary Keys or Build Indexes Only.
(DataGarage/Target Setup/Vertical of Target)
Remove any columns from the tables that are not referenced/needed in any of the webapps.
(DataGarage 5.5 shown)
- Verify that the "Delete Target Table on Build" option is not selected. When this is selected, the targettable will be dropped and the DataGarage Cache entry for this table will be deleted. The entire source table will then be used to create the target table.
(DataGarage/Target Setup/Target/Source/Vertical of Source)
- Modify selection of columns for target table.
Click DataGarage/Target Setup/Target/Source/Tables Button.
Go to Vertical of Target Table.
Click View Design button on General Information tab from target table vertical.
Click Column Count button.
Delete column names from these Data Source Table Columns pages.
Delete Target Table from SQL Target Database. Go into SSMS and delete the target table out of the target database.
Rebuild the package in DataGarage for this target table as follows:
- Click DataGarage/Target Setup/Target/Source/Tables.
- Click Build Package for the target table. The target table without the columns that you deleted will be created.
- Click Refresh and the data will be copied from the source table to the new smaller target table.
Bring down only records required for the webapps
Add a where clause to restrict the amount of records downloaded as follows:
- Click DataGarage/Target Setup/Target/Source/Tables Button.
- On the vertical-Advanced Settings tab of the chosen table, enter a valid where clause in the "Where Clause Override" field and Save.
- Rebuild the Package and refresh the table as described above.