sql - Need help optimizing this tSQL Query -

i'm not dba , unfortunately don't have dba consult within @ our company. wondering if give me recommendation on how improve query, either changing query or adding indexes database.

looking @ execution plan of query seems outer joins killing query. query returns 350k results, takes 30 seconds complete. don't know db's, don't think good? perhaps i'm wrong?

any suggestions appreciated. in advance.

as side note being create orm , not me directly. using linq-to-sql.

select      [t12].[value] [discoveryenabled],      [t12].[value2] [isconnected],      [t12].[interface],      [t12].[description] [interfacedescription],      [t12].[value3] [duplex],      [t12].[value4] [isenabled],      [t12].[value5] [host],      [t12].[value6] [hostip],      [t12].[value7] [mac],      [t12].[value8] [macadded],      [t12].[value9] [portfast],      [t12].[value10] [portsecurity],      [t12].[value11] [shorthost],      [t12].[value12] [snmplink],      [t12].[value13] [speed],      [t12].[value14] [interfacestatus],      [t12].[interfacetype],      [t12].[value15] [isuserport],      [t12].[value16] [vlan],      [t12].[value17] [code],      [t12].[description2] [description],      [t12].[host] [devicename],      [t12].[net_ouid],      [t12].[displayname] [net_ou],      [t12].[enclave] (     select          [t1].[discoveryenabled] [value],          [t1].[isconnected] [value2],          [t0].[interface],          [t0].[description],          [t2].[duplex] [value3],          [t0].[isenabled] [value4],          [t3].[host] [value5],          [t6].[address] [value6],          [t3].[mac] [value7],          [t3].[macadded] [value8],          [t2].[portfast] [value9],          [t2].[portsecurity] [value10],          [t4].[host] [value11],          [t0].[snmplink] [value12],          [t2].[speed] [value13],          [t2].[interfacestatus] [value14],          [t8].[interfacetype],          [t0].[isuserport] [value15],          [t2].[vlan] [value16],          [t9].[code] [value17],          [t9].[description] [description2],          [t7].[host], [t7].[net_ouid],          [t10].[displayname],          [t11].[enclave],          [t7].[decommissioned]     [dbo].[idb_interface] [t0]         left outer join [dbo].[idb_interfacelayer2] [t1] on [t0].[idb_interface_id] = [t1].[idb_interface_id]         left outer join [dbo].[idb_laninterface] [t2] on [t1].[idb_interfacelayer2_id] = [t2].[idb_interfacelayer2_id]         left outer join [dbo].[idb_host] [t3] on [t2].[idb_laninterface_id] = [t3].[idb_laninterface_id]         left outer join [dbo].[idb_infrastructure] [t4] on [t0].[idb_interface_id] = [t4].[idb_interface_id]         left outer join [dbo].[idb_addressmapipv4] [t5] on [t3].[idb_addressmapipv4_id] = ([t5].[idb_addressmapipv4_id])         left outer join [dbo].[idb_addressipv4] [t6] on [t5].[idb_addressipv4_id] = [t6].[idb_addressipv4_id]         inner join [dbo].[art_asset] [t7] on [t7].[art_asset_id] = [t0].[art_asset_id]         left outer join [dbo].[nsd_interfacetype] [t8] on [t8].[nsd_interfacetypeid] = [t0].[nsd_interfacetypeid]         inner join [dbo].[nsd_interfacecode] [t9] on [t9].[nsd_interfacecodeid] = [t0].[nsd_interfacecodeid]         inner join [dbo].[net_ou] [t10] on [t10].[net_ouid] = [t7].[net_ouid]         inner join [dbo].[net_enclave] [t11] on [t11].[net_enclaveid] = [t10].[net_enclaveid]     ) [t12] ([t12].[enclave] = 'usmc') , (not ([t12].[decommissioned] = 1)) 

linq-to-sql query:

return t in db.idb_interfaces              join v in db.idb_interfacelayer3s on t.idb_interface_id equals v.idb_interface_id              join u in db.art_assets on t.art_asset_id equals u.art_asset_id              join c in db.net_ous on u.net_ouid equals c.net_ouid              join w in                (from d in db.idb_interfaceipv4s                 select new { d.idb_interfaceipv4_id, d.idb_interfacelayer3_id, d.idb_addressmapipv4_id, d.idb_addressmapipv4.idb_addressipv4.address })              on v.idb_interfacelayer3_id equals w.idb_interfacelayer3_id              join h in db.net_enclaves on c.net_enclaveid equals h.net_enclaveid enclaveleftjoin              in enclaveleftjoin.defaultifempty()              join m in                (from z in db.idb_standbyipv4s                 select new                 {                   z.idb_interfaceipv4_id,                   z.idb_addressmapipv4_id,                   z.idb_addressmapipv4.idb_addressipv4.address,                   z.preempt,                   z.priority                 })              on w.idb_interfaceipv4_id equals m.idb_interfaceipv4_id standbyleftjoin              k in standbyleftjoin.defaultifempty()              t.art_asset.decommissioned == false              select new netidbgriddataresults              {                devicename = u.host,                host = u.host,                interface = t.interface,                ipaddress = w.address,                aclin = v.inboundacl,                aclout = v.outboundacl,                virtualaddress = k.address,                virtualpriority = k.priority,                virtualpreempt = k.preempt,                interfacedescription = t.description,                enclave = i.enclave              }; 

as rule (and general), want index on:

• join fields (both sides)
• common where filter fields
• possibly fields aggregate

for query, start checking join criteria. 1 of missing force table scan big hit.