Database Hints

When you send a query to a database, the database engine works out how to execute that query using various heuristics.  This usually results in an extremely efficient plan.  However, sometimes you can work out a better plan based on your knowledge of the database or of the broader application context.  For example, you might have found through testing that the database engine is not using an index which could improve performance.  Or you might know that it doesn’t matter if a particular query reads rows that are in the process of being written, meaning the database engine can skip the safety overhead of a lock.

In such situations, some databases allow you to pass hints to the database engine on how to execute the query.  This section shows how to specify these hints on LightSpeed queries.

In all cases, it’s important to remember that hints are exactly that – hints.  The query planner doesn’t have to obey them.  You are providing advice on how to execute the query: the database engine is free to overrule that advice.

Index Hints

An index hint advises the database to use a particular index.  You can pass an index hint using the WithIndexHint operator, providing the name of the index you want to use.

var orders = unitOfWork.Orders
                       .WithIndexHint("IX_OrderDueDate")
                       .Where(o => o.DueDate < today)
                       .ToList();

You can pass multiple index names to WithIndexHint if required.

At the time of writing, index hints are supported on Oracle and SQL Server.  Using an index hint on another database does not cause an error – since hints are advisory anyway – but is ignored.

Table Hints

A table hint advises the database about the way the query uses the table being queried.  Table hints can be used for a variety of tasks.  For example, the SQL Server NOLOCK table hint indicates that the table need not be locked during the query, trading improved performance for the risk of dirty reads.  You can pass a table hint using the WithTableHint operator.

var orders = unitOfWork.Orders
                       .WithTableHint("NOLOCK")
                       .Where(o => o.DueDate < today)
                       .ToList();

Table hints are database-specific – LightSpeed simply passes the raw hint text to the database.  At the time of writing, table hints are supported only on SQL Server.  Using a table hint on another database does not cause an error – since hints are advisory anyway – but is ignored.

Database Hints Using Query Objects

If you are using query objects instead of LINQ, you can provide hints through the Query.Hints object.

Query query = new Query(Entity.Attribute("DueDate") < today);
query.Hints.Indexes.Add("IX_OrderDueDate");
query.Hints.TableHints.Add("NOLOCK");
var orders = unitOfWork.Find<Order>(query);