Suggestions to improve performance for pre-formatted SQL
Published 29 October 2019
You may experience performance issues when pasting SQL from notepad or other text editor into SQL Server Management Studio.
Cause
SQL Prompt is optimized for use with large scripts when you first install it. However, if you experience slow performance, for example because you have a slow processor, you can reduce the number of lines of SQL code that SQL Prompt parses when it populates the candidate list.
How to fix
To reduce the number of lines of SQL code that SQL Prompt parses when it populates the candidate list:
- On the Options dialog box, click Listed Candidates > Performance
- Ensure that Search a fixed number of lines from the caret is selected, and then reduce the number of lines to search for variables and parameters.
When you open a SQL file or paste text into your query editor, SQL Prompt will automatically parse the new code for any scripted objects and assigned aliases. For large files and scripts, this may take a few seconds. If you do not want SQL Prompt to scan pasted text or opened SQL files, in the Listed Candidates > Performance page, clear the Search for objects and aliases when opening or pasting text check box option.
SQL Prompt stops searching an opened file or a pasted block of text after 5 seconds. For very large scripts, this may not be long enough to parse all the scripted objects and aliases. You can increase the length of time SQL Prompt scans the text; to do this, increase the value of the MaximumScanTimeMilliseconds property in the EngineOptions.xml file from its default value of 5000 milliseconds.
The EngineOptions.xml file is stored by default in your local user profile's application settings, for example C:\Documents and Settings\<username>\Local Settings\Application Data\Red Gate\SQL Prompt 3. To specify that SQL Prompt should always scan the whole file or block of text with no time limit, set the value to 0
.
If you continue to experience problems, please contact Red Gate Support.
