A VB developer comes to you, wanting your advice on the fastest way to implement some code in a SQL Server-based application she is writing. Essentially, the code is used to look up some values used to populate a drop-down box on one of her program's screens. The code uses a triple join to produce the necessary output for the drop-down box.
You take a look at the VB code and notice that the developer is using an ADO Recordset object with a Transact-SQL string to access the data on SQL Server.
The developer tells you that this particular code will run thousands of times each day, and because of this, it must perform as quickly as possible. In addition, the results of this query will vary from day-to-day as the underlying data in the tables change.
What, if any, suggestions can you offer the developer to boost the performance of her code?
1) Have the developer incorporate the Transact-SQL code into a stored procedure.
2) Have the developer rewrite the code in the form of a SUB SELECT, instead of trying to join three tables together to produce the necessary output.
3) Suggest to the developer to add an appropriate query hint to the code to speed up access to the data.
4) The developer can't do much to boost the code's performance, but as a DBA, you can do a better job of optimizing the indexes to speed it up.