Before reading the questions, you can watch the video here about writing efficient queries.
Question: In general, what is the definition of the most efficient query?
Answer: A query that runs quickly and uses the least amount of resources is the most efficient query.
Question: How is it possible that a query taking lots of IO resources running faster than other queries?
Answer: In my example, along with the high IO the query is also taking high CPU and that eventually is leading the query to run faster in terms of time.
Question: Do you always tune your query for IO?
Answer: Often yes but not always. From my many years of experience as a SQL Server Performance Tuning Consultant, I know that the IO is often slow and most troubled resources. It always a good idea to tune queries for the IO because that also leads to better CPU utilization and eventually faster queries.
Question: What is more optimal join or inner join?
Answer: Honestly, they both are the same. I am personally surprised that this question kept on coming up again and again whereas the demo which I have discussed is not about the join. Here is another video that I have build which discusses the JOIN or INNER JOIN.
Question: Is there any way you can help us to tune our SQL queries?
Answer: Yes, it is totally possible to hire me to help you tune your SQL queries and database. You can read more about my extremely popular service Comprehensive Database Performance Health Check. After reading the post if you have any questions, do not hesitate to reach out to me.
Reference: Pinal Dave (https://blog.sqlauthority.com)