SQL hints

Warning

Before using SQL hints, be sure to use all other query optimization features. This is a "double-edged weapon." You need to understand very well why you are using what hints and how it will affect performance, and also make all the necessary tests confirming the effectiveness of the changes you made!

XData supports using SQL hints to improve SQL query productivity. All databases supports this feature using various mechanics and can change various parameters of SQL execution...

Types of hints supported by XData for RDBMS...

MS SQL Server

Query hints Table hints

Oracle

Query hints

MySql

Query hints

PostgreSQL

PostgreSQL does not support hints for queries, but procedures that returns set of record required declare columns definition on call (see example below). Of course, this is not a SQL hint actually, but...

create or replace function GetRows(text) returns set of record as
$
declare
r record;
begin
for r in EXECUTE ''select * from '' || $1 loop
return next r;
end loop;
return;
end
$
language 'plpgsql';

select * from GetRows('Department') as dept(deptid int, deptname text);

SQLite

SQLite does not support hints.

Using hints in static mapping

Query hints:

// hint for empty string alias is applied to query
// possible to apply different hints on 
// select, insert, update, delete statements or their combination
[DataObject("T"),
 Hint("", "SQL_NO_CACHE", HintType.Select),
 Hint("", "HIGH_PRIORITY", HintType.Select | HintType.Insert),
 ...

Table hints (MS SQL Server only):

[DataObject("T"),
 DataTable("Test", "T"),
 Hint("T", "nolock"),
 ...

Procedure hints (PostgreSQL only):

[DataObject("T"),
 Procedure("T", "TestFunction9", ProcedureType.Function),
 Hint("T", "Id integer, Name varchar(20)"),
 Parameter("T", 1, "p_id1", typeof(int), DbType.Int32),
 Parameter("T", 1, "p_name1", typeof(string), DbType.String),
 Parameter("T", 1, "p_id2", typeof(int), DbType.Int32), 
 Parameter("T", 1, "p_name2", typeof(string), DbType.String),
 ResultSet("T")]
public class PostgreSqlTestFunction9 : IDataObject
{
    [Property("T", "Id", Flags = DataPropertyFlag.Id)]
    public int Id { get; set; }
    [Property("T", "Name")]
    public string Name { get; set; }
}

Using hints in dynamic mapping and dynamic queries

Query hints:

// hint for empty string alias is applied to query
// possible to apply different hints on 
// select, insert, update, delete statements or their combination
XDataMapping.GetStructure("T")
    .Hint("", "SQL_NO_CACHE", HintType.Select)
    .Hint("", "HIGH_PRIORITY", HintType.Select | HintType.Insert)...

Table hints (MS SQL Server only):

XDataMapping.GetStructure("T")
    .DataTable("Test", "T")
    .Hint("T", "nolock")...

Procedure hints (PostgreSQL only):

XDataMapping.GetStructure()
    .Procedure("T", "TestFunction8", ProcedureType.Function)
    .Hint("T", "Id integer, Name varchar(20)")
    .Parameter<Classifier[]>("T", 1, "p_tab", DbType.Object, 
        z => z.UdtDataType(null, "classifier_rec"), z => z.Array())
    .Select(x => new Classifier { 
        Id = x.Field<int>("T", "Id", z => z.Key()), 
        Name = x.Field<string>("T", "Name") })
    .AsQuery(DataScope, "p_tab".SetVar(param));