So I have a newer T-SQL developer in my company and I am code reviewing his first big set of work for me. The only real big thing I have found in his code is the constant use of the REMOTE hint when accessing linked servers. Sample queries provided below so you can see what I am referring to if you are unaware.
Well, to be prefectly honest, I had no idea what the REMOTE hint was and had to go look it up. I've been doing T-SQL coding for the better part of 15 years and never run into this hint before. I searched and found some decent articles on the hint. I also found a lot of references to it not being the optimization you might be expecting and how you should avoid it, or only consider it if your local table row count is much smaller than your remote table row count. This happens to be the case in his queries so it might be important.
I ran his queries, both with and without the hints, and can't see it providing any benefit to the overall cost. I reviewed our established company T-SQL standards (which I wrote most of and am responsible for maintaining as the senior T-SQL developer) and we have nothing in our document about this.
Before I go to him and ask him to remove the hints, I'd like to know if anyone has experience with the REMOTE table hint. If so, any knowledge you can share to me to understand this better. This is a good developer and I don't want to have a conversation with him about something I don't at least have a better understanding of. Thanks for helping me educate myself.
Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer
Well, to be prefectly honest, I had no idea what the REMOTE hint was and had to go look it up. I've been doing T-SQL coding for the better part of 15 years and never run into this hint before. I searched and found some decent articles on the hint. I also found a lot of references to it not being the optimization you might be expecting and how you should avoid it, or only consider it if your local table row count is much smaller than your remote table row count. This happens to be the case in his queries so it might be important.
I ran his queries, both with and without the hints, and can't see it providing any benefit to the overall cost. I reviewed our established company T-SQL standards (which I wrote most of and am responsible for maintaining as the senior T-SQL developer) and we have nothing in our document about this.
Before I go to him and ask him to remove the hints, I'd like to know if anyone has experience with the REMOTE table hint. If so, any knowledge you can share to me to understand this better. This is a good developer and I don't want to have a conversation with him about something I don't at least have a better understanding of. Thanks for helping me educate myself.
Code:
--Without REMOTE Hint
SELECT
st.SyStudentId,
st.FirstName,
st.LastName
FROM LS_ODS.Students st
INNER JOIN OtherServer.CV_Prod.dbo.syStudent cst
ON st.SyStudentId = cst.SyStudentId;
--With REMOTE Hint
SELECT
st.SyStudentId,
st.FirstName,
st.LastName
FROM LS_ODS.Students st
INNER REMOTE JOIN OtherServer.CV_Prod.dbo.syStudent cst
ON st.SyStudentId = cst.SyStudentId;Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer