Question

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.

Answer 1

Query Optimization:

1) fetch only the required colums or data, as it will unnecessarly flood the memory.

2) HAVING clause should be used to filter the rows after all the rows are selected.

3) minimize the number of subquery block in the main query. Subquery blocks the main query while it is running.

4) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.

5) Use temporary tables only when needed.

6) delete and update in batches. It improves the performance to great extent as all the query are executed at one go.

While sorting the data in resultset, always collection should be used. Data should be extracted into List and then it should be shorted there as it increases the performance.

To fine-tune performance chars, we sholud use stored procedure, Optimize Your Transact-SQL Code, choosing appropriate data types, check for the use of properly when needed, most of the time we sholud avoid using cursors.