(Asked on: tuning in sql server - views. Sorry, I needed to review my post).
I need to add hint in the join (force using index on ID when doing the join), and on the select (force using index on name for the where clause), as last query mentioned in this post. I don't know what is the correct syntax doing join properly (for tuning), and what is the correct syntax for force index, that where clause will use it, while I am selecting from view.
I created a view in sql server 2012, such as:
create myview as
select mytable2.name
from mytable1 t1
join myTable2 t2
on t1.id = t2.id
I want that join table1 and table2 will be with correct index (id), but when I do:
select * from myview
where name = 'abcd'
I want that the last select will be with index of column 'name'.
What is the correct syntax in sql server with hints (tuning), that do the best run, as I have described?
I want to force using of index for join purpose only (the column = id), and forcing index name when doing:
select name from myview
where name = 'abcd'.
Something like
create myview as
select mytable2.name
/* index hint name on column name */
from mytable1 t1
join myTable2 t2
/* index hint name on column id - just for join */
on t1.id = t2.id
I don't want to force end-user that uses the view add hint when doing the view - just bring him the view as his with proper index hints. (or, if it is not possible - how can I do that).
Need samples, please.
I have such a simple view (no matter what are the tables based on ... it's very specified for the company I am working at. This is Microsoft Dynamics AX 2012+ some extra tables).
create view [dbo].[splSqlAltItemsView]
as
select i_orig.dataareaid, id_orig.inventsiteid, i_orig.itemid, i_alt.itemid altItemId,
orig_item.minTollerance, orig_item.maxTollerance
from
(
select orig_item.recid,
c_grade.value as grade,
c_materialFamily.value as MaterialFamily,
c_shape.value as shape,
c_thickness.value as thickness,
convert(real, isnull(c_minTollerance.value, 0.00)) as minTollerance,
convert(real, isnull(c_maxTollerance.value, 0.00)) as maxTollerance
from
(select e0.recid
from ECORESPRODUCT e0
where e0.recid in
(
select productid
from CMAPRODUCTATTRIBUTEVALUES c0
where name in ('Grade', 'Material Family', 'Shape', 'Thickness')
and productid > 0
group by productid
having count(1) = 4
)) orig_item
join CMAPRODUCTATTRIBUTEVALUES c_grade
on c_grade.PRODUCTID = orig_item.RECID
and c_grade.name = 'Grade'
join CMAPRODUCTATTRIBUTEVALUES c_materialFamily
on c_materialFamily.PRODUCTID = orig_item.RECID
and c_materialFamily.name = 'Material Family'
join CMAPRODUCTATTRIBUTEVALUES c_shape
on c_shape.PRODUCTID = orig_item.RECID
and c_shape.name = 'Shape'
join CMAPRODUCTATTRIBUTEVALUES c_thickness
on c_thickness.PRODUCTID = orig_item.RECID
and c_thickness.name = 'Thickness'
left join CMAPRODUCTATTRIBUTEVALUES c_minTollerance
on c_minTollerance.PRODUCTID = orig_item.RECID
and c_minTollerance.name = 'Min Tollerance'
left join CMAPRODUCTATTRIBUTEVALUES c_maxTollerance
on c_minTollerance.PRODUCTID = orig_item.RECID
and c_minTollerance.name = 'Max Tollerance'
) orig_item
join
(select alt_item.recid,
c_grade.value as grade,
c_materialFamily.value as MaterialFamily,
c_shape.value as shape,
c_thickness.value as thickness,
convert(real, isnull(c_minTollerance.value, 0.00)) as minTollerance,
convert(real, isnull(c_maxTollerance.value, 0.00)) as maxTollerance
from
(select e0.recid
from ECORESPRODUCT e0
where e0.recid in
(
select productid
from CMAPRODUCTATTRIBUTEVALUES c0
where name in ('Grade', 'Material Family', 'Shape', 'Thickness')
and productid > 0
group by productid
having count(1) = 4
)) alt_item
join CMAPRODUCTATTRIBUTEVALUES c_grade
on c_grade.PRODUCTID = alt_item.RECID
and c_grade.name = 'Grade'
join CMAPRODUCTATTRIBUTEVALUES c_materialFamily
on c_materialFamily.PRODUCTID = alt_item.RECID
and c_materialFamily.name = 'Material Family'
join CMAPRODUCTATTRIBUTEVALUES c_shape
on c_shape.PRODUCTID = alt_item.RECID
and c_shape.name = 'Shape'
join CMAPRODUCTATTRIBUTEVALUES c_thickness
on c_thickness.PRODUCTID = alt_item.RECID
and c_thickness.name = 'Thickness'
left join CMAPRODUCTATTRIBUTEVALUES c_minTollerance
on c_minTollerance.PRODUCTID = alt_item.RECID
and c_minTollerance.name = 'Min Tollerance'
left join CMAPRODUCTATTRIBUTEVALUES c_maxTollerance
on c_minTollerance.PRODUCTID = alt_item.RECID
and c_minTollerance.name = 'Max Tollerance'
) alt_item
on alt_item.grade = orig_item.grade
and alt_item.MaterialFamily = orig_item.MaterialFamily
and alt_item.shape = orig_item.shape
and (alt_item.thickness between orig_item.thickness - orig_item.minTollerance and orig_item.thickness + orig_item.maxTollerance or
orig_item.thickness between alt_item.thickness - alt_item.minTollerance and alt_item.thickness + alt_item.maxTollerance)
join inventtable i_orig
on i_orig.product = orig_item.recid
join InventItemInventSetup is_orig
on is_orig.dataareaid = i_orig.dataareaid
and is_orig.itemid = i_orig.itemid
and is_orig.inventdimid = 'AllBlank'
join InventDim id_orig
on id_orig.DATAAREAID = is_orig.DATAAREAID
and id_orig.inventdimid = is_orig.inventdimiddefault
join inventtable i_alt
on i_alt.product = alt_item.recid
and i_alt.DATAAREAID = i_orig.DATAAREAID
join InventItemInventSetup is_alt
on is_alt.dataareaid = i_alt.dataareaid
and is_alt.itemid = i_alt.itemid
and is_alt.inventdimid = 'AllBlank'
join InventDim id_alt
on id_alt.DATAAREAID = is_alt.DATAAREAID
and id_alt.inventdimid = is_alt.inventdimiddefault
and id_alt.inventsiteid = id_orig.inventsiteid
When I do:
select * from splSqlAltItemsView
where itemid = '12345'
there is lot of time consuming, even inventTable has an index on itemid.
When I do:
select * from splSqlAltItemsView
The view is runs for seconds.
Using hints in SQL Server is generally not recommended, as the query optimizer is designed to choose the most efficient execution plan based on statistics, indexes, and other factors. However, in some rare cases, using hints can be helpful to fine-tune performance. Hint can be useful if you know that the optimizer is not selecting the optimal indexes for your query. However, it's important to note that using hints can be risky, as they can become outdated or irrelevant over time as data changes and indexes are added or removed.
create myview as
select mytable2.name
from mytable1 t1 WITH (INDEX(index_name1))
join myTable2 t2
WITH (INDEX(index_name2))
on t1.id = t2.id
In this query, WITH (INDEX(index_name)) hint is used to specify the index to be used for each table. Note that the index name must match the name of an existing index on the table, otherwise the hint will be ignored.
It's important to use hints with caution and to thoroughly test their impact on query performance. In general, it's a good idea to rely on the query optimizer to choose the best execution plan, but in some rare cases, hints can be a useful tool for optimizing query performance.