Enabling Paged Query Execution for the Database Connector

The amount of data to retrieve and the time it takes to execute a query can sometimes be a lengthy process. This is why the Database connector can execute queries in smaller subsets of data - called page result - allowing the retrieval of, for example, 5,000 rows per call. By doing so, timeouts can easily be avoided.

The paged query execution takes advantage of a feature found on most DBMS, which offers the possibility to retrieve the subset of query results by specifying the range of rows to crawl.

To take advantage of the paged query execution feature, you must properly configure your mapping file. You have to provide a query that takes advantage of the paging feature and supply two tokens (@startRow and @endRow) that will dynamically be replaced at run time by the connector with the actual range of data to retrieve. When these tokens are detected, the connector automatically enables paging execution.

Refer to the following for an excerpt of a mapping file with a query configured to run in paged mode against SQL Server:

SELECT * FROM (
  SELECT MESSAGE.MID,
    MESSAGE.SENDER,
    MESSAGE.DATE_,
    MESSAGE.MESSAGE_ID,
    MESSAGE.SUBJECT,
    MESSAGE.BODY,
    MESSAGE.FOLDER,
    ROW_NUMBER() OVER (Order By MESSAGE.MID) as LINE
  FROM MESSAGE
  WHERE MESSAGE.DATE_ like '2001-04-07%') RESULTS
WHERE RESULTS.LINE between @startRow and @endRow

All queries in a configuration file are independent. You can decide to use the paged query execution feature or not.

Example: A query can be written as a paged query, while its associated subqueries can be written without this feature.

Hidden parameter for page size (Integer)

By default, the connector is configured to query the database 5,000 records at a time when the paged query execution feature is enabled. However, this value can be overridden by configuring the QueryPageSize hidden parameter in the source configuration. The value should be positive and different from 0.

To configure the QueryPageSize hidden parameter

1. On the Coveo server, access the Administration Tool (see Opening the Administration Tool).

2. Add the QueryPageSize hidden parameter to the database connector for all database sources by specifying an Integer type and 5000 for the default value (see Adding an Explicit Connector Parameter).

3. Select the Index tab, and then select the Sources and Collections menu.

4. For each database source for which you want to change the default value for the QueryPageSize parameter:

   1. Under Collections, select the collection containing the database source.

   2. Under Sources, select the desired database source.

   3. In the navigation panel on the left, select General.

   4. In the Query Page Size box that now appears in the page, enter the desired page size value for this database source. The value should be positive and different from 0.

   5. Click Apply Changes.