How should i optimize this query

ErikU 2017-10-06 20:06:40

SELECT dbo.T_Berichten.*, dbo.T_Studenten.Stud_naam + ' ' + dbo.T_Studenten.Stud_voornaam as naam_zender
FROM dbo.T_Berichten INNER JOIN
dbo.T_Users ON dbo.T_Berichten.Zender_ID = dbo.T_Users.Usr_ID INNER JOIN
dbo.T_Studenten ON dbo.T_Berichten.Zender_ID = dbo.T_Studenten.Usr_ID
WHERE dbo.T_Berichten. Usr_ID=@session_loginid and dbo.T_berichten.ber_map_id=@map

UNION

SELECT dbo.T_Berichten.*, dbo.T_Docenten.Doc_naam + ' ' + dbo.T_Docenten.Doc_voornaam as naam_zender
FROM dbo.T_Berichten INNER JOIN
dbo.T_Users ON dbo.T_Berichten.Zender_ID = dbo.T_Users.Usr_ID INNER JOIN
dbo.T_Docenten ON dbo.T_Berichten.Zender_ID = dbo.T_Docenten.Usr_ID
WHERE dbo.T_Berichten. Usr_ID=@session_loginid and dbo.T_berichten.ber_map_id=@map
–order by dbo.T_Berichten.ber_dat_v desc

SDyckes2 2017-10-10 19:59:17
Without a bit more context, this query does not look like it needs much tuning. The duration is 31 milliseconds. What is the issue that would require this query to be optimized?

Upon examination of the query plan, the 2 Key Lookups stand out as potential optimizations. If you were to remove the Key Lookups, you could decrease the number of reads. You could remove the Key Lookups with an alteration of one of the indexes on the T_Berichten table.

Both the [NonClusteredIndex-20170620-131216] and *[\_dta_index_T_Berichten_5_1810157544__K3_K7_6]* indexes are very similar and may be able to be consolidated. Adding the 2 columns, *[Ber_Dat_V]* and *[Ber_Map_ID]* to the *[\_dta_index_T_Berichten_5_1810157544__K3_K7_6]* index changes the hit ratio score from 53% to 90%, a good indication the updated index would help the query. But ALWAYS test.