Home | Overview | How Do I | FAQ | Sample | Tutorial | ODBC Driver List
This article describes how to restrict the number of records that a query returns. Topics covered include:
One of the great keys to good database performance is to restrict how many records you select. In general, the more records you select, the greater the required memory and the slower the performance. DAO and MFC let you filter the records that a query selects, and you can specify filtering criteria at run time rather than design time. The mechanism works as follows:
WHERE [State] = "NY"
WHERE [State] = [State Code]
State Code is the parameter name.
Filtering records by any of the approaches described below relies on the SQL WHERE clause. You can also use the HAVING clause if you are using GROUP BY. For information about these keywords, see the following topics in DAO Help:
And see the topic "SELECT Statement (SQL)" in DAO Help.
The MFC DAO classes let you filter a recordset in two ways:
Tip These two approaches are equivalent in terms of performance. The only difference is whether you build the WHERE clause in the SQL string that you use to create the recordset or you let MFC build the clause using a value you’ve supplied for m_strFilter.
Important You can’t use m_strFilter (or its companion m_strSort, which specifies an SQL ORDER BY clause for sorting) if you create your recordset from a CDaoTableDef or CDaoQueryDef object.
The following example shows filtering with m_strFilter (the first approach above):
// Filter records with m_strFilter but no parameter
// strStudentID is a value probably obtained from
// the user
rsEnrollmentSet.m_strFilter = "[Student ID] = " + strStudentID;
try
{
// Open the recordset using the filtered string
rsEnrollmentSet.Open( );
// ...
}
// ...
MFC appends the value of m_strFilter to the recordset’s SQL as long as there is not a WHERE clause in the SQL string already.
The following example shows filtering with a pre-specified WHERE clause (the second approach above):
// Filter records with the SQL keyword WHERE
CString strSQL = rsEnrollmentSet.GetDefaultSQL( ) +
"WHERE [Student ID] = " + strStudentID;
try
{
// Open the recordset using the filtered SQL string
rsEnrollmentSet.Open( dbOpenDynaset, strSQL );
// ...
}
// ...
The example calls GetDefaultSQL to obtain the SQL string defined for the recordset's class at design time, using ClassWizard or AppWizard. Then it concatenates a WHERE clause, part of which is based on run-time information in strStudentID
.
In either case, the result is a recordset that contains a smaller number of records because of the filtering.
Note The filtering and sorting mechanisms described here are not available for table-type recordsets. To filter or sort records in a table-type recordset, you must call DAO directly. Set the Filter and Sort properties of the recordset. To specify which index (if any) is active for the recordset, call CDaoRecordset::SetCurrentIndex. For information about calling DAO directly, see Technical Note 54.
In situations where your application executes the same query repeatedly, it is more efficient to create a stored querydef object that contains the SQL statement. Queries stored in the database execute faster and can be used by anyone with access to the database.
If your application needs to alter WHERE clause arguments in a query, you can also add a PARAMETERS clause to your query that permits the Microsoft Jet database engine to substitute values into the query at run time. Before running parameter queries, your application must substitute values for each of the parameters as stored in the Parameters collection of the querydef.
In general, parameterizing queries improves performance. The parameterized SQL statement doesn't have to be recompiled each time you run the query.
To create a parameter query
If you are working with a database accessed by Microsoft Access, the parameter name is used as a prompt string. Keep this in mind if you expect Microsoft Access users to use this query.
Shown below is a typical PARAMETERS clause:
CString strParam = "PARAMETERS [Student Ident] TEXT; ";
The parameter name is enclosed in square brackets here because the name contains a space. Otherwise the brackets are unnecessary.
strSQL = strParam + "SELECT * FROM Enrollment WHERE Enrollment.[Student ID] = [Student Ident]";
CDaoQueryDef qd( m_dbStudentReg );
qd.Create( "Find Enrollments", strSQL );
qd.Append( );
First, you need to gain access to the querydef. You can either use the querydef object just created, or reference the stored querydef object from the QueryDefs collection. The example shows using the querydef just created.
COleVariant varParamValue( strStudentID );
qd.SetParamValue( "[Student ID]", varParamValue );
Because this query returns records, you need to create a recordset to capture the result set.
CEnrollmentSet rsEnrollmentSet( &m_dbStudentReg );
rsEnrollmentSet.Open( &qd, dbOpenDynaset );
The parameter is defined as part of the SQL statement and becomes part of a PARAMETERS clause. You set the value of the parameter, at run time, by calling the querydef object's SetParamValue member function. This function takes:
For more information and a different example (presented in the Basic language rather than C++), see the topic "Creating Parameter Queries with DAO" in DAO Help.
In the Class Library Reference, see CDaoQueryDef and CDaoRecordset. In particular, see CDaoQueryDef::SetParamValue and CDaoQueryDef::GetParamValue.
See Also DAO: Where Is...