How should i optimize this query

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

MyQuery.pesession (467.1 kB)
avatar image By ErikU 0 asked Oct 06 at 08:06 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By SDyckes2 81 answered Oct 10 at 07:59 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x631
x445
x27
x17

asked: Oct 06 at 08:06 PM

Seen: 22 times

Last Updated: Oct 10 at 07:59 PM