Database Optimization And Performance Tuning Paper
Databases are what store and let users access thousands of data. Speed and delivery of data from a database are very important. Having a high performance is very sought after. Having a fast and efficient database will give benefit users and companies heavily. Increasing database performance can be achieved through a variety of ways. Queries must be evaluated and modified to be the most efficient they can be. There is a variety of ways queries can be altered to increase performance three of them are refactoring bad code, requesting only the data that's needed, and reducing table size. Bad query code can decrease database performance in a big way. Many tend to add extra information into a query that isn't necessarily needed to get the data we want. Although the code that was originally used might give the data requested refactoring it will put less of a toll on the database.
Often times the query can be refactored to get the same outcome and be a smaller query which means the performance goes up. An example of this that IS taken from Kevin Kliens “Top 10 Tips for Optimizing SQL Server Performance” would be a T-SQL query that originally was 20 lines long but then got shortened to UPDATE. It will get the same outcome with less work that the query has to do. The goal here is too put less stress on the database by making queries as efficient as possible. Another way a query can be altered to improve performance would be to only include the information that's needed.
If someone wants to get specific information from a table but doesn't specify any column the query will search through the whole table. Where if a specified column was present in the query the results would have come back much faster. Not specifying columns in a big table will cause the database to process too much unneeded data and will take away resources. An example of this would be using SELECT * FROM employees. This will return all information from the table employees which is bad if just a couple of columns were needed. So to make the query faster and more specific we would add the columns in which the data would be located. SELECT Name, Email FROM employees. This would return less data which in turn would help the overall database performance out. One more way to improve performance by altering queries would be to reduce the table size.
Depending on the data that's needed there can be lots of tricks to apply within the query. For example, one can use a time period on the table if it has a date column to gather a certain amount of data. This makes the table smaller and in turn makes queries return faster which is what the ultimate goal is. In the end database performance is key to running a successful database. If speed and delivery aren't present databases will start to run out of resources and will take to long to get a job done. It's important to apply the three different ways when applicable. Refactoring code will help fix reused code with excess lines. Only requesting the information needed will help the database retrieve data quicker. Also making tables smaller by requesting data specifically can make query requests faster which in turn makes the overall database performance better.
