Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Tuning SQList for optimal performance

Via the "Settings" tab of SQList Manager, you can configure and tune SQList to meet your requirements and optimal performance.

In this article we will explain each settings.

Important: after you have changed the settings, remember to save the change using the "Save Profile" button.



Setting Description
Max list items retrieved when checking for updates This is the number of updated/new rows SQList retrieves from a list each time it checks for updates to export to the SQL database. For more details, see this support article.
Max list items retrieved when checking for deletions This is the number of rows SQList retrieves from a list each time it checks for deleted rows. SQList does not use the Recycle bin to detect deletions but compares the items in the SharePoint list with the rows in SQL table instead.
Seconds to wait between cycles This setting defines the amount of time, in seconds, SQList wait between cycles. For more details, see this support article.
Heartbeat frequency in seconds This setting defines the frequency, in seconds, SQList sends a heartbeat message to the event log. To disable the heartbeat, set this value to 0.
Re-load profile cycle frequency This setting is deprecated and has been kept for backward compatibility. Please ignore it.
 
Number of Sites processed in parallel This setting defines the maximum number of Sites that SQList processes in parallel.
Set to 0 (zero) to leave it to the machine to use as all available threads.
Number of Replications processed in parallel This setting defines the maximum number of Replications that SQList processes in parallel. Note that one replication can export multiple sites (if sub-sites are included).
Set to 0 (zero) to leave it to the machine to use as all available threads.
Number of Lists processed in parallel This setting defines the maximum number of Lists within a site that SQList processes in parallel.
Set to 0 (zero) to leave it to the machine to use as all available threads.
Note above the settings above:

By default, these settings are configured for optimal performance with most common situation. It could be tempting to set all three settings to 0 and leave it to the machine to use as many as it can, however this not always brings the best results.
Based on our tests, we recommend the following rule of thumb:
  • if you are exporting a large number of Sites, but not many Lists from each, then give a high thread limit to the Sites, and a lower one to the Lists;
  • vice-versa, if you are exporting a small number of Sites, but each containing many Lists to be exported, then assign a low limit to the Sites, and a high limit to the Lists;
  • the limit for the Replications works as an overall setting, so when you have configured the Site's and the List's limits, you can use this setting to increase or decrease performance and load on the servers.
We recommend that you leave this settings to their default initially, and change them only if needed.
 
Generate clean column names for tables This setting defines whether SQList will remove special characters and normalise the name of the column created in the SQL table. Note that this could cause duplicate names (e.g "Address1" and "Address 1" would generate a duplicate if the " " is removed).
Automatically handle duplicate columns This setting defines whether SQList will automatically handle duplicate column names after special characters are removed as part of the cleaning described by the setting above.
Export SharePoint system lists This setting defines whether SQList export SharePoint system lists (e.g Master page templates, workflow settings, etc.)
Never drop tables from database This setting defines whether SQList will automatically drop a table in the SQList database, when the corresponding list in the SharePoint site is deleted. For an example, see this support article.
Do not add prefix to table names This setting defines whether SQList prepend the site name to the SQL tables. For an example, see this support article.
Abort list synchronisation when document download fails This setting defines whether SQList will abort updating a list when downloading a document or an attachment fails. For an example, see this support article.
Ignore ANSI warnings This setting defines whether SQList will abort updating a list when downloading a document or an attachment fails. For an example, see this support article.
 
Default database schema name This setting defines the default schema name SQList uses to create the SQL tables.
Table prefix separator This setting defines the character to use as a separator between the site name and table name in the SQL table.
Table suffix separator This setting defines the character to use as a separator between the table name and the suffix appended for many-2-many lookup and attachments in the SQL table.
 
Length for DECIMAL columns This setting defines the total length of a decimal column in the SQL table, when a numeric list column is exported. The number of decimal position is set from the decimal positions defined in the SharePoint list's column.
Default length for NVARCHAR columns This setting defines the length of a varchar column in the SQL table, when a single-line list column is exported.
 
Column type for numbers with zero decimals This column defines whether SQList will export a numeric list column with zero decimals as an integer or as a decimal with 0 decimal precision.
Column type for External ID values This setting defines the type of column SQList uses to store the ID of external sources.
Force 2 decimals for numbers displayed as percentage This setting defined whether a columns configured in SharePoint to "display as percentage" is exported as an integer of a fraction (e.g. 3% exported as 3 or 0.03)
 Abort all replications if one fails By default, SQList (ver. 4.3 onward) will continue with the other replications if one fails. By selecting this option you can change this behaviour to abort all replications if one fails.
This is useful if you want to ensure that all sites you are exporting are fully synchronised in the database.