Question

Database Server

The Union of Southwest Landscaping Professionals (USLP) has nearly 1,500 members located in Arizona, New Mexico, and Utah. There are approximately 500 members in each state. USLP sends a weekly newsletter to members in one of the states. It mails the newsletter to only one state per week to save money on postage. Members complain that retrieving the addresses is very slow. Much of this is because the database server is eight years old, but buying a newer one is out of question.

As the administrator of this system, what can you do to improve the performance of the system? Describe your recommendations. Explain and justify how your recommendations can be implemented. This is generic database.

Answer 1

I have taken sql as a example for generic database:

SQL tuning for a faster database:

           If you're running MySQL database. you're probably familiar with the basics of the sql language. while sql is relatively easy to pick up, there are always opportunities when it comes to optimising your database to run as quickly and efficiently as possible.

   Minor tweaks can often have major effects on database performance, especially when dealing with large volumes of data. by implementing some fairly straightforward sql tips,you can trim the fat to ensure your database is as fast as it can be, and deliver the best-possible experience to users.

Improve sql perfomance

      On certain platforms like shared hosting you won't be able to change the backend configuration. even if you do have access to the backend, it's not always practical to play around with your setup. sql statements dictate what is or isn't retrieved, and there are various ways to write an aql statement to deliver the same result. however, optimised sql statements will provide increased performance, because the database won't take the retrieve any records that are irrelevant to the query.

Optimising SELECT statements

    SELECT statements can be optimised by specifying the columns of the data you want to be returned, for example for a query that requires a home address from a table that contains complete personal profile data.

SELECT FROM

        this will be retrieve data from all the columns in the table. whether or not they contain home address records. if it's a large table, you can expect a significant performance hit as the database trawls through the whole thing.

SELECT street_address, tow _city,county,postcode FROM personal_info;

      now the query is optimised to retreve only the data that matters to the user, saving processing and time.

you can also use the limit clause to ensure only data from a certain number of rows is retrieved from a table. for instace, it you only need the first five records from a table with thousands of rows.

SELECT title, author, price FROM books;

you could write:

SELECTtitle, author,price FROM books LIMIT 5;

    now the database only needs to retrieve data from the first five rows of the table, skipping the rest for maximum efficiency.

   these are just a couple of basic examples to illustrate how simple it can be to optimise sql queries. SELECT statements are a priority for performance tuning since they carry out all database lookup operations, but there are many more possibilities.