Database optimisation hints in LightSpeed

Databases are smart. You can feed them any crazy-ass query you like, and they’ll figure out some whizzy way to do it so that the answer comes back before you even asked the question. They know all the tricks, they know all the costs, and they know how to put them together to come up with the bestest plan ever for finding the results you want as quickly as possible. Yep, databases are smart.

Except when they’re dumb. Sometimes, you can provide a database with all the helpers it needs, you can index your data till the pips squeak, and the database still insists on coming up with its own bonkers query plan that takes six years to run and involves doing a full table scan of a three-zillion line table on a server in Timbuktu.

To deal with this, some databases allow you to provide hints to the query planner, gently — or not so gently — advising it to use a particular index or whatever, overriding or at least modifying the database’s own optimiser. For example, Oracle has specially formatted comments, and SQL Server has the FROM … WITH (INDEX) clause.

In the latest nightly builds of LightSpeed, we’ve added experimental support for providing index hints on queries. It’s pretty simple to invoke: just use the new WithIndexHint() method on a LINQ query, and you’re away:

var hoihos = UnitOfWork.Penguins
                       .Where(p => p.EyeColour == EyeColour.Yellow);

(If you’re not using LINQ, use the new Query.Hints property instead: query.Hints.Indexes.Add("IX_Appearance");.)

We want to stress that this is an experimental feature. Optimiser hints are by nature tricky to test, and we’re not experts on database tuning. So we’d welcome any feedback from folks who are willing to give this a bit of a beta test. We’d also like feedback on the behaviour — at the moment, we consider hints to be, well, hints, and ignore them if the database doesn’t support them or if we don’t know how to support them on the database at hand, which keeps code portable, but potentially disguises errors. (Not that you’ll always get errors from your database either. We were surprised when testing to find that Oracle would cheerfully accept an index hint for an index that didn’t exist.) Please post bugs or suggestions in the forums.

You can get index hints in the latest LightSpeed 3.11 nightly build (free edition from the downloads page, full edition from the store). They’re currently available for Oracle and SQL Server only — if there’s another database you’d like to see us support them on, let us know.

Are there any other optimiser hints you’d like to be able to pass to the database? Drop us a comment or post in the forums — you’ve got to be in it to win it!

Tagged as LightSpeed

Leave a Reply


Join our mailer

You should join our newsletter! Sent monthly:

Back to Top