Скачать или смотреть SQL Server Query Tuning Series - SQL Server Query Hints

SQL Server Query Tuning Series - SQL Server Query Hints #jbswiki #querytuning

Restoring Required Databases for Query Tuning Session -    • Видео  

Drop table Table1
select * into Table1 from Table1_Backup
alter table Table1 add constraint PK_Table1_Col1 Primary key (Col1)

Drop table Table2
select * into Table2 from Table2_Backup
alter table Table2 add constraint PK_Table2_Col1 Primary key (Col1)

Drop table Table3
select * into Table3 from Table3_Backup
alter table Table3 add constraint PK_Table3_Col1 Primary key (Col1)

Drop table Table4
select * into Table4 from Table4_Backup
alter table Table4 add constraint PK_Table4_Col1 Primary key (Col1)

Drop table Table5
select * into Table5 from Table2_Backup
alter table Table5 add constraint PK_Table5_Col1 Primary key (Col1)

set statistics io on

select T2.COl2,T3.Col2, sum(T1.Col7) as TotalSum
from Table1 T1 INNER JOIN Table2 T2
ON T1.COl1 = T2.Col1 and T1.Col4 = T2.Col14 and T2.Col11 IS NULL
INNER JOIN Table3 T3
ON T2.Col1 = T3.Col1
INNER JOIN Table4 T4 ON
T3.Col1 = T4.Col1
INNER JOIN Table5 T5 ON
T4.Col1 = T5.Col1
group by T2.COl2,T3.Col2
order by T2.COl2 desc option (RECOMPILE)

select T2.COl2,T3.Col2, sum(T1.Col7) as TotalSum
from Table1 T1 INNER JOIN Table2 T2
ON T1.COl1 = T2.Col1 and T1.Col4 = T2.Col14 and T2.Col11 IS NULL
INNER JOIN Table3 T3
ON T2.Col1 = T3.Col1
INNER JOIN Table4 T4 ON
T3.Col1 = T4.Col1
INNER JOIN Table5 T5 ON
T4.Col1 = T5.Col1
group by T2.COl2,T3.Col2
order by T2.COl2 desc OPTION (QUERYTRACEON 8780,RECOMPILE)

select T2.COl2,T3.Col2, sum(T1.Col7) as TotalSum
from Table1 T1 INNER JOIN Table2 T2
ON T1.COl1 = T2.Col1 and T1.Col4 = T2.Col14 and T2.Col11 IS NULL
INNER JOIN Table3 T3
ON T2.Col1 = T3.Col1
INNER JOIN Table4 T4 ON
T3.Col1 = T4.Col1
INNER JOIN Table5 T5 ON
T4.Col1 = T5.Col1
group by T2.COl2,T3.Col2
order by T2.COl2 desc OPTION (hash join)

select T2.COl2,T3.Col2, sum(T1.Col7) as TotalSum
from Table1 T1 INNER JOIN Table2 T2
ON T1.COl1 = T2.Col1 and T1.Col4 = T2.Col14 and T2.Col11 IS NULL
INNER HASH JOIN Table3 T3
ON T2.Col1 = T3.Col1
INNER JOIN Table4 T4 ON
T3.Col1 = T4.Col1
INNER JOIN Table5 T5 ON
T4.Col1 = T5.Col1
group by T2.COl2,T3.Col2
order by T2.COl2 desc

select T2.COl2,T3.Col2, sum(T1.Col7) as TotalSum
from Table1 T1 INNER JOIN Table2 T2
ON T1.COl1 = T2.Col1 and T1.Col4 = T2.Col14 and T2.Col11 IS NULL
INNER JOIN Table3 T3
ON T2.Col1 = T3.Col1
INNER JOIN Table4 T4 ON
T3.Col1 = T4.Col1
INNER JOIN Table5 T5 ON
T4.Col1 = T5.Col1
group by T2.COl2,T3.Col2
order by T2.COl2 desc OPTION (merge join)

select T2.COl2,T3.Col2, sum(T1.Col7) as TotalSum
from Table1 T1 INNER JOIN Table2 T2
ON T1.COl1 = T2.Col1 and T1.Col4 = T2.Col14 and T2.Col11 IS NULL
INNER JOIN Table3 T3
ON T2.Col1 = T3.Col1
INNER JOIN Table4 T4 ON
T3.Col1 = T4.Col1
INNER JOIN Table5 T5 ON
T4.Col1 = T5.Col1
group by T2.COl2,T3.Col2
order by T2.COl2 desc OPTION (FORCE ORDER)

select col2,col3 from Table3 where col4 like 'a%'
create index ix_test on table3(col4)
select col2,col3 from Table3 where col4 like 'a%'
select col2,col3 from Table3 with (index=IX_TEST) where col4 like 'a%'

set statistics io off

Drop table Table1
Drop table Table2
Drop table Table3
Drop table Table4


Disclaimer:
The views expressed on this Video are mine alone and do not reflect the views of my company or anyone else. All postings on this Video are provided “AS IS” with no warranties, and confers no rights.