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
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
By ErikU 0 asked Oct 06, 2017 at 08:06 PM
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.
By SDyckes2 96 answered Oct 10, 2017 at 07:59 PM