Optimizing SQL Queries

In a typical university course, performing an SQL query was always a fast thing. However, when you are working on a project that grows over time, you at first might not give the performance of a query much thought. As the project grew, I had complex queries and lots of records in the database, distributed over many tables. What I didn't expect, was that from one day to another, the performance of the page went from zero complaints to constant user complaints (the page loads too slowly). As I found out way later, the bottleneck comes suddenly. It does not build up. And at that point I was stressed and had to do an overnight shift, dealing with temporarily shutting down the users and optimizing the backend queries (take a look at the JOINS, and narrow it down as much as you can). For future reference I know that I will give SQL query performance way more attention, and as the query complexity and database size grows, you should in the same step perform a check how the performance scales.

Comments

Good advice for anyone stumbling into writing SQL. SQL Performance is a slumbering giant. While queries often run quick enough in development, in production (often with just a couple of thousands of rows in a table) it looks quite different if indexes are set wrong or some nested query doesn't perform as planned.
Artur Marcin Sz... - Mon, 12/10/2018 - 07:40 :::
No answers yet.