WinSQL » Knowledge base

Document information

Document ID: 827
Subject: WinSQL pauses for a long time while writing SQL queries
Creation date: 7/29/09 5:05 PM
Last modified on: 12/12/18 10:09 AM


Details


Users often complain about WinSQL not responding while typing queries, especially 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:

  1. Meta Data Lookup (Table and Field names)
  2. 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.

IMPORTANT:
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 memory.

Data Lookup

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.

IMPORTANT:
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 selecting Edit/Options from the main menu and choosing the Intelli Tips tab.

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?

Important: This area is reserved for useful tips. Therefore, do not post any questions here. Instead, use our public forums to post questions.

Navigation

Social Media

Powered by 10MinutesWeb.com