Hint tips are used to guide the query optimizer when, by default, it does not create reasonable query plans. Firstly, a small background in query optimizers:
Database programming is different from almost all other software because it has a mechanical component. Disk and rotation latency (expecting a particular sector to fall under the disk head) are very expensive compared to a processor. Different query resolution strategies will result in different amounts of I / O, often radically different amounts. Proper or incorrect use can significantly affect query performance. For an overview of query optimization, see this article.
SQL is declarative — you define the logic of the query and let the DBMS figure out how to solve it. The modern cost-based query optimizer (some systems, such as Oracle, also have a proactive query optimizer, maintained for backward compatibility) will conduct a series of query transformations. They support semantic equivalence, but differ in the order and choice of operations. Based on the statistics collected from the tables (sizes, histograms of key distribution), the optimizer calculates an estimate of the amount of work needed for each query plan. He chooses the most effective plan.
Cost-based optimization is heuristic and depends on accurate statistics. As the complexity of queries increases, heuristics can create incorrect plans that can potentially be extremely inefficient.
In this situation, you can use hints to request certain strategies in terms of the request, for example, the type of connection. For example, in a query that usually returns very small result sets, you can force nested loops to be inserted. You might also want to force the table join order.
O / R mappers (or any tool that generates SQL) generates its own query, which usually does not have a hint of information. In case this request does not work efficiently, you have limited options, some of which are:
Learn indexing tables. Perhaps you can add an index. Some systems (such as recent versions of Oracle) allow you to index joins in multiple tables.
Some database management systems (again, Oracle comes to mind) allow you to manually associate a query plan with a specific query string. Request plans are cached by the hash value of the request. If the queries are parameterized, the base query string is constant and will be resolved to the same hash value.
As a last resort, you can change the database schema, but this is only possible if you are running the application.
If you control SQL, you can prompt queries. In practice, this is quite rare, in fact, you need to do this. A more general failure mode on O / R cards with complex database schemas is that they can make it difficult to express complex query predicates or perform complex operations on large data arrays.
I tend to advocate the use of an O / R mapper for 98% of the work for which it is suitable and thrown into stored procedures, where they are a suitable solution. If you really need to hint at a request, this might be the appropriate strategy. If there is something unusual in your application (for example, some DSS), you only need to get away from the O / R mapper in a minority of situations. You can also find (again, for example, DSS tools that work with data together) that O / R matching is not a good strategy for an application.