When we build an application that accesses or manipulates databases we have the responsibility to verify that our choices have been adequate.
Sometimes during encoding we don’t know exactly how the data will be used, which filters are most used, how many users will access the database simultaneously. In addition to having the differences between the development environment and the production environment.
What should we do to make sure that our queries and our model have and will continue to perform properly?
The tests are fundamental!
Mauro Pichiliane wrote a fantastic article on the iMasters portal where he talks about “Myth of Best Database Performance”, attention to what he writes:
“In order to quantify the performance improvements in a database, it’s necessary to perform tests, and tests that are in accordance with the environment, since it is necessary to take into account a number of factors.” Unfortunately, following ready-made recipes and unquantified general recommendations or based on little theory may even indicate a certain path, but such a practice makes unpredictable the measurement of results without tests. What I mean here is that without performance tests, whether generic or specific, it is very difficult to quantify effectively what the improvement of the implementation of such functionality X or Y or Z. And this has a very large impact on certain professionals to the point that information plays are propagated without any questioning. Now, it is inappropriate for a professional to say that he took such an attitude or developed a certain way that they told him, without proving with data or theoretical basis, that it was. And that’s a very close reality when it comes to improving database performance – especially among programmers. ”
But what must be tested?
Database tuning is an extensive subject that mainly concerns DBAs. In this article the intention is not exactly to do a tuning, but it shows you how you can make corrections that help improve the performance of your application. I dare call it a mini tuning!
Remembering again that there is no recipe for cake or magic formula … Everything must be tested, evaluated and then decided.
The items I would like you to notice are:
- Indices
- Implicit conversions
- Use Transactions
- Tables in memory
- Materialized Views
Indexes
Think of a book’s index … It helps you locate a particular information piece faster. In a table, or collection, or column family, or graph; the index has the same function, it makes the search for the data faster. *
Indexes are highly recommended to speed up queries, as long as tables don’t have constant updates, because in this case indexes can negatively impact application performance. Therefore, during application testing check if there are any excess indexes (in tables whose data changes frequently), and if there are queries in tables that don’t undergo many changes and that could have indexes.
* Note that Indexes exist in relational and NoSQL databases.
Implicit conversions
Implicit conversions are those where the database automatically converts data from one data type to another. For example, when a smallint is compared to an int, in this case the smallint is implicitly converted to int before the comparison.
Excessive implicit conversions may indicate modeling errors … So stay tuned!
Use of Transactions
Did you read the post about transactions?
Transactions are important for securing ACID properties in relational databases. But transactions can also be very costly because they consume resources and may detract from the application performance. Don’t use transactions for everything! Analysis of the need.
Attention, for example, to the level of isolation required, because this property can prevent the table from being accessed while updates occur.
Memory Tables
There are data that is often accessed by the application. Sometimes they are configurations, domain tables, temporary data. Anyway… There are few data, often accessed by the application. In this case either use key-value databases or access the database once and keep the data in memory. Avoid unnecessary database access.
Materialized Views
They are database objects that store the results of a query. Unlike a view that is a logical table, the materialized views “exist in the database”. It is as if the database created an internal trigger so that every data update in the columns used in the view also updates the materialized view.
Using the materialized view increases performance on data queries, but worsens performance on updates.
If you use this feature, make a careful evaluation when you finish coding.
Conclusion
Ending the encoding is very different from ending the application. Test, evaluate, verify that you have used the right resources. Use the tools to analyze the database (this week I’ll post the most commonly used tools for each type of database).
End your applications as the successful professional that I know that you are!
See also
See the other articles in this serie:
- 3 mistakes that beginner developers which prevent the creation of great applications
- Warning: Have you already chosen the database type?
- Why are you going to create the data model?
Next steps
We’ve finished the articles series about mistakes that prevent applications from beginning developers from succeeding.
None of the 3 subjects are finalized, we have much to learn together !!!
In the next few weeks we’ll know more about MongoDB. A document-oriented database, widely used around the world.
But do not worry, that with the news, I will not have problems creating a “branch”.
If you have any subject you would like to discuss here on the blog, please contact me, send your suggestion! After all, the blog is ours !!!
So you do not need to search my contacts are:
- @DaniMonteiroDBA (Twitter)
- DB4Beginners (Facebook)
- Danielle Monteiro
References
https://pt.stackoverflow.com/questions/138033/qual-a-diferença-entre-view-e-materialized-view
https://www.ibm.com/developerworks/br/java/library/j-ts6/index.html
https://gustavomaiaaguiar.wordpress.com/tag/fk/
If you liked, share this post! And if do you have any questions talk to me!
This article is available in Portuguese.