SQL Prompt 3.xx - reducing memory usage to improve performance

When you use SQL Prompt with databases that contain a large number of objects, you may experience high memory usage; this can slow down performance on machines with inadequate specifications, or on which a large number of other applications are running.

This article explains how you can set SQL Prompt options to reduce memory usage and speed up performance. There are a number of settings in SQL Prompt that can help improve perfomance:

candidate list options
number of databases in the cache

Most settings for SQL Prompt can be changed in the SQL Prompt Options dialog box. When you have changed the settings you want, click OK to save them and close the dialog box.

To display the SQL Prompt Options dialog box

From the SQL Prompt menu, for example in SQL Server Management Studio, select Options:

Setting candidate list options

SQL Prompt populates the candidate list with items in the database schema. In large databases, this can slow down performance as SQL Prompt searches a complex schema. You can change the behaviour of the candidate list to reduce memory usage.

Open the SQL Prompt Options dialog box.
Select the Listed Candidates tab, then select Candidate Types and Filters.
Clear the following check boxes:
- List all columns in database after SELECT (this is cleared by default)
  Clearing this option means that the candidate list does not show all the columns for all tables and views in the database after a SELECT statement; it only lists the columns when you specify a particular table or view using the FROM keyword. This limits the number of columns the list has to display, and reduces memory usage.
- Filter candidate list by owner/schema prefix
  Clearing this option means that when you specify a schema, the candidate list shows all relevant objects, rather than only objects that are owned by that schema (owner). When the option is selected, SQL Prompt must search all the database objects to find those that match; this increases memory usage.
- Enable case-sensitive filtering of candidates (this is cleared by default)
  Clearing this option means that SQL Prompt does not filter objects in the candidate list based on whether they match the case of the first letter you type. When the option is selected, SQL Prompt needs to increase the amount of schema data it handles.

Limiting the number of databases in the cache

Caching databases increases memory usage, so if your memory usage is high, reducing the number to the minimum (3) may speed up performance.

From the SQL Prompt menu, select Cache Management.
In the SQL Prompt Cache Management dialog box, set the Maximum number of databases to hold in memory to 3.

Note that this is the minimum number of databases SQL Prompt allows you to cache.