|Subject:||WinSQL pauses for a long time while writing SQL queries|
|Creation date:||7/29/09 5:05 PM|
|Last modified on:||10/2/12 8:55 AM|
Users often complain about WinSQL not responding while typing
queries, specially if they are connecting to a large database. This
pause is caused by WinSQL's Intelli-tip feature that tries to run a
query in the back-ground.
WinSQL's Intelli-Tip feature tries to determine what objects
are available in the database as you type your queries. There are two
type of intelli-tips:
- Meta Data Lookup (Table and Field names)
- Data Lookup (Actual Data in the table)
Meta Data Lookup
|If your database contains thousands of
tables, it may take a long time to fetch the initial catalog. However,
once the catalog is fetched in memory, it can be much faster.
Meta Data lookup occurs right after SELECT, FROM, WHERE, INSERT, UPDATE, and DELETE
clause. This is when WinSQL tries to fetch available fields and table
names from the database to assist users to type their queries.
Meta Data lookup is typically very fast and is
usually not the cause of intermittent pauses. This is because meta data
is fetched from the database once and resides in local machine's
Data Lookup occurs after the operator is specified in a WHERE clause. For example, right after the following string is typed.
select * from Customer where Company =
As soon as an operator is encountered in the WHERE clause, WinSQL tries to fetch the data for that field.
Data Lookups are the common cause of frequent intermittent pauses in
WinSQL, particularly if you have a large database. We strongly
recommend that you configure WinSQL so that it does not fetch data for
every field in the database.
Modifying settings for Intelli-Tips
When connecting to a large database you might want to change the
default behavior of WinSQL regarding Intelli-tips. This is done by
from the main menu and choosing the Intelli Tips
This screen has two sections: Intelli Tips
and Data Lookup
. Un-check "Enable auto intelli tips
box to disable intelli tips completely. Press CTRL+SPACE while typing queries to bring the intelli tips window manually.
The section on the right hand side deals with Data Lookup. Select a desired value from the Enable/Disable Data Lookup combo box to configure Data Lookup. The table below describes each value.
|Disable data lookup
||Select this option to completely disable data lookup
|Enable for every column
||Select this option if you are connecting to a small database. WinSQL
will try to pull data for every column that is in the database. It will run
a SELECT distinct query as soon as you type the operator after a WHERE clause. Selecting this option will take a long time on large databases.
|Enable for columns that I choose
||This is the best option for large database. Data Lookup only appears for fields that you decide.
Follow the steps below to specify the fields for which Data Lookup should occur.
- Close the configuration window
- Connect to your desired database, if you are not connected to one.
- Click on the catalog tab
- Open the tree nodes on the left hand side until you see the fields for a table.
- Double click the desired field to bring up the following window.
Specify a SELECT query that will be run in the background to bring data for this field. WinSQL creates a default query for you.
IMPORTANT: You must click the OK button in order for WinSQL accept the settings. A warning message in RED indicates that Data Lookup is not yet enabled and will be done once you click OK.
Add a comment to this document
Do you have a helpful tip related to this document that you'd like to share
with other users? Please add it below. Your name and tip will appear at the
end of the document text.