Suggestions to improve performance for pre-formatted SQL (version 3.9)

Category: Troubleshooting & error messages
Date: 30 Jul 2009
Product: SQL Prompt
Versions: 3.0-3.9

You may experience performance issues when pasting SQL from notepad or other text editor into SQL Server Management Studio.

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.

To do this, 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. Decreasing the number of lines speeds up performance. However, if you reduce this value too much you may experience problems.

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.

Note that 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.

Document ID: KB200711000190 Keywords: SQL,Prompt, Paste, Copy, text,slow,performance

Was this article helpful?

Search support