Database Reference
In-Depth Information
So, instead of doing
SELECT * FROM ACCOUNTS WHERE BRANCH_ID = 7;
And counting and summing the rows in the client, do instead
SELECT count(*), sum(balance) FROM ACCOUNTS WHERE BRANCH_ID = 7;
With little research into the SQL language supported by PostgreSQL, you can do an amazingly
large portion of your computation using plain SQL.
And if SQL is not enough, you can use PL/pgSQL or any other of PostgreSQL's supported
embedded procedural languages for even more flexibility.
Application runs a huge number of small lookup queries
This can easily happen with modern ORM's (Object relational Mappers) and other toolkits, which
do a lot of work for the programmer, but at the same time hide a lot of what is happening.
For example, if you define an HTML report over a query in a templating language, and then
define a lookup function for resolving an ID inside the template, you may end up with a form
that does a separate small lookup for each row displayed, even when most of the values
looked up are the same. This does usually not pose a big problem for the database, as queries
of the form "
SELECT
name
FROM
departments
WHERE
id
=
7
" are really fast when the row
for id=7 is in shared buffers, but doing this query thousands of times still takes seconds, due
to network latencies, process scheduling for each request, and other factors.
The two solutions are as follows:
F
Make sure that the value is cached by your ORM
F
Do the lookup inside the query that gets the main data, so it can be displayed directly
How exactly to do these depends on the toolkit, but they are both worth investigating, as they
really can make a difference in speed and resource usage.
Simplifying complex SQL
There are two types of complexity which you can encounter in SQL queries.
First, the complexity can be directly visible in the query, having hundreds or even thousands
of rows of SQL code in a single query code. This can cause both maintenance headaches and
slow execution times as well.
The complexity can also be hidden in subviews, so that the SQL code of the query seems
simple, but it uses other views and/or functions to do part of the work, which can in turn still
use others. This is much better for maintenance, but still can cause performance problems.
