Writing Efficient Queries Homework Help, Assignment Help, Online Tutor, Tutoring

Submit Assignment

* *

Payment Online Assignment Help

Asp dot net Project Homework

Homework Help

sfsf

Writing Efficient Queries

There are some general philosophical rules of thumb to follow when performance-tuning SQL code:

• Database model design supports SQL code: The quality of SQL code depends completely on the quality of database model design, not only from a perspective of correct levels of normalization and denormalization, but also from the point of view of using appropriate structures. For example, a data warehouse database model design is needed for a data warehouse because over-normalized, granular, deep normal form tables, often used in OLTP databases, are completely inappropriate to the very large transactions, across many tables, required by data warehouses.

• The KISS Rule (Keep It Simple and Stupid): Any type of program code broken into simple, (preferably independent) pieces is always easier "to wrap your head around." Simple SQL commands with simple clauses are easy to write and easy to tune. Longer and more complicated queries are more difficult to write, and it's more difficult to get them to produce the proper results. Performance tuning is an additional step. If you have to tune some big nasty queries because they are running too slow, well, what can I say? If you had kept it simple, making them run faster would probably be a lot easier, and a lot more possible. Simplify first if overcomplexity is an issue. In the very least, simplicity can help you understand precisely what a query is doing, without giving you a headache just staring at lines of meaningless SQL code.

• Good table structure allows for easy construction of SQL code: Be aware of anything controlling the way or manner in which SQL code is constructed and written, other than, of course, the database model. In an ideal table structure, SQL code should be written directly from those table structures, or as subsets of it, not the other way around. Writing SQL code should not be difficult. You should not get a constant impression (a nagging doubt or hunch) that table structure doesn't quite fit. The structure of the database model should make for easy of SQL code construction. After all, SQL code supports applications. Don't forget that SQL code rests on the database table structure. If there is any kind of mismatch between application requirements and database structure, there is likely something wrong with the database model. Performance tuning SQL code in a situation such as this will likely be a serious problem.

• Breaking down into the smallest pieces: Break down the construction of SQL code commands, such as queries and DML commands (INSERT, UPDATE, and DELETE). Do not break down nonquery and non-DML type commands. For example, do not continually connect and disconnect from a database for every single code snippet of SQL database access executed. Either connect for a period of time, for each user, or connect at the start and end of sessions. On the other hand, make extensive use of subqueries if it helps to making coding easier. You can always merge subqueries back into the parent query later on.