You work for a manufacturing company The company has recentl

You work for a manufacturing company. The company has recently hired a programmer who does not have knowledge of SQL Server, but has been tasked to develop queries. As a database administrator, you have been tasked with the following to help him/her out with understanding queries and performance. Address the following in your discussion posts:

What design considerations will the developer need to be aware of to ensure queries are optimized? Why?

What should the developer be aware of when sorting data in a result set? Why?

Explain how you can test a prototype query to fine-tune its performance characteristics.

Solution

1. Design Consideration to ensure queries are optimized:

            i) IN : IN is efficient when most of the filter criteria is in the sub-query.

            ii) EXISTS : EXISTS is efficient when most of the filter criteria is in the main query.

            iii) Joins : Joins will help you in avoiding lot of sub-queries

-------------------------------------------------------------------------------------------------------------------------------------------------------------

What should the developer be aware of when sorting data in a result set? Why?        

   1. Time taken to fetch the result : Because it defines the efficiency of the database design and query performnance     

           2. Necessary Resultant Set: One have to have a set of sample result set, to know the result driven is correct or not.

          3. Usage of Group by clause: Group by clause is used to drive the result set as well as the sorting of result.

          4. Null and Distinct Values: While sorting the value or expecting distinct values be carefull, since null value is considered only one time in result values in case of distinct clause.

------------------------------------------------------------------------------------------------------------------------------------------------------------

How to test a prototype query to fine-tune its performance characteristics ?

                                  Prototype queries are tested once the database designing nearby the complete phase, to check the success of design.

To check prototype queries, we should want to have sample outputs or expected outputs, which will allow us to fine tune the query comparing the resultant set of Prototype queries.

However on prototype queries we can\'t achieve 100% fine tuning, comparing to original developed design.