Large amount of planning needs to go to the building database applications. There are many things that can affect performance in a negative way if you do not follow best practices.
It may seem a good performance in a test environment with only a few thousand rows in the database but when running the application, and begin to grow a database, you may design considerations bad it became clear very quickly.
When designing a database and building code for this query, there is a simple design considerations and best practices that can help to ensure that such data base grows, the system is still responding.
Here are five tips :
Tables 1 / design with the appropriate indexes
Can be the lack of appropriate indicators caused a significant decrease in performance. As shown in the table becomes larger and larger, must be examined through improved data necessary to recover the rows based on the WHERE clause in the query. Imagine using a phone book with no indicator to tell you to a page where he was the local plumber. This will be slower to find them without telling you that the page be in the Page 302 or 500 manual pages of your site. The query optimizer has the same problem in the case where an index.
2 / Do not use SELECT * FROM
It is easy to write SELECT * FROM. Write a query in this method is much faster than writing out the required fields of the table. Well you reap what you sow. If you take the time to build a SQL statement correctly, and return on investment and will improve response time. This is because the SQL query to pull data less than if all the columns listed. If you only need 3 columns instead of 10, which consists of the table simply select the three.
3 / Make sure that the use of a stored procedure
There are many benefits to using stored procedures. One benefit is that the optimizer can be called the cache the execution plan. Custom SQL requires more time and resources to implement because the optimizer has to work on the implementation of a better plan for the implementation of the query.
4 / Do not use the index
There is a time to use the index, but generally should be avoided unless absolutely necessary. Mohsen best to deal with data sets. It is not too large to do this very quickly using direct line.
5 / Make sure that you have a maintenance plan in place
In SQL Server, you can build a treatment plan to help rebuild the indexes and statistics update. In the cell, and this can be done using the TABLE OPTIMIZE as part of the process at once. Ensure that the maintenance plan in place helps to control the retail index. So far, statistics help to ensure that the optimizer can use the best implementation plan for the query.