sql server - SQL Multiple Table Join - Best Optimization -

hi hoping can sql theory out. have create set of reports use joins multiple tables. these reports running far slower , hoping optimize sql although knowledge has hit wall , cant seem find on google.

i hoping here can give me best practice guidance.

essentially trying filter on results set comes reduce number of rows included in later joins

items inner join blueitems on items.itemid = blueitems.itemid , blueitems.shape = 'square'           left join itemhistory on items.itemid = itemhistory.itemsid           left join itemdates on items.itemid = itemdates.itemid itemdates.manufacturedate between '01/01/2017' , '01/05/2017' 

i figure inner joining on blue items squares vastly reduces data set @ point?

i understand clause intelligent enough reduce data set on run time? mistaken? returning data , filtering on data?

any guidance on how speed kind of query fantastic, index's , such have been put in place. unfortunately database looked after else , creating reports based on database. limit me being able optimize queries rather data itself.

i guess @ point time me try , improve knowledge on how sql handles various ways can filter on data , try understand reduce dataset used , filter on it. guidance appreciated!

you mentioned primary keys indexed, case primary key fields. portion of current query possibly benefit first join items. other joins , where clause, these primary key fields not being used.

for particular query, suggest following indices:

alter table blueitems   add index bi_item_idx (itemid, shape) alter table itemhistory add index ih_item_idx (itemid) alter table itemdates   add index id_idx      (itemid, manufacturedate) 

for itemhistory table, index ih_item_idx should speed join involving itemid foreign key. column same name involved other 2 joins, , hence part of other indices. reason composite indices (i.e. indices involving more 1 column) want cover columns appear in either join or where clause.