Tips

This topic provides information to help you to optimize SQL prompt for use with:

databases with complicated schemas
large scripts

Using SQL Prompt with databases with complicated schemas

If you use SQL Prompt with a database that has a complicated schema, you may experience some performance issues because of the amount of memory that SQL Prompt needs to use. If you have insufficient RAM installed in your PC, the performance of Windows and all your applications may degrade because the operating system has to swap data in and out of memory, onto disk and back again.

If you want to take full advantage of the features that SQL Prompt offers, you are recommended to upgrade your PC to increase the amount of physical memory to a minimum of 1 GB; for very complicated schemas, 2 GB may be required.

If you are unable to upgrade your PC, you can change your options and settings to reduce memory usage, as follows. However, this means that you will not be able to take full advantage of all of SQL Prompt's features.

Reduce the number of databases cached in memory.
When you reduce the number of cached databases, SQL is more likely to have to pull information for databases from disk, which is slower. Therefore, if you often work with multiple databases, you may wish to experiment with a higher number of cached databases to find the optimum performance.

On the Cache Management dialog box, set the Maximum number of databases to hold in memory to 3.
'Ignore' databases for which you are not interested in seeing information in the candidate list.
SQL Prompt does not cache information about databases you have chosen to ignore, so that memory is not used on these databases. The benefit is greater for larger (more complex) databases.

On the Options dialog box, click Connections, and then click Connections to Ignore. Click New to specify the databases that you want to ignore. For more information about this option, see Managing Connections
For SELECT statements, show columns only for the tables and views that you specified in the SQL statement.
SQL Prompt provides an option to show all columns in the database when the candidate list is displayed after the SELECT keyword. By default, this option is not selected and columns are displayed after the SELECT keyword only when you have specified a table. If you are working with complex databases, you are recommended not to select this option.

To ensure that the option is not selected, on the Options dialog box click Listed Candidates, and then click Candidate Types and Filters. If necessary, clear the List all columns in database after SELECT check box.
Do not filter the candidate list by schema (owner).
SQL Prompt provides an option to filter the candidate list by schema (owner), so that only candidates that belong to that schema (owner) are shown. This is very useful if you are working with SQL Server 2005 and you have a large number schemas to which objects might belong.

However, if most of your objects belong to the same schema (owner), this option is less useful and you may want to switch it off. For example, this may be the case if you are using SQL Server 2000 and your objects belong to the owner dbo. Switching off this option can reduce the amount of memory that SQL Prompt uses by up to 10%.

To ensure that the option is not selected, on the Options dialog box click Listed Candidates, and then click Candidate Types and Filters. If necessary, clear the Filter candidate list by owner/schema prefix check box.
Do not filter the candidate list by case.
SQL Prompt provides an option to filter the candidate list so it shows only those candidates that match the case of your typing. By default, this option is not selected and candidates are listed irrespective of case.

If you have switched this option on and you have a lot of object names that use mixed case, switching it off will reduce memory usage.

To ensure that the option is not selected, on the Options dialog box click Listed Candidates, and then click Candidate Types and Filters. If necessary, clear the Enable case-sensitive filtering of candidates check box.

Using SQL Prompt with large scripts

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, Variables and Parameters. 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; for details, see Troubleshooting.