| Table of Contents | ||
|---|---|---|
|
...
Get only the columns you need
The more columns you have in the result, the more data needs to be transmitted over the network from the database and loaded to the memory of the application server. Therefore it is important to specify only for the columns you really need to get and work with. For example, if you need to work with 6 columns from a table having 30 column in total, it means that if you don’t specify to retrieve particular columns and fetch all, it means that 80% of the data transmitted over network and loaded in the memory will be not used and only eating memory and network resources. Performing such query repeatedly by many users or for many line items makes the situation even worse. When doing JOINs this risk is even higher, since that means additional columns are added to the result. Therefore it is important to always specify only the columns to be retrieved.
Join criteria on all key fields
Joining tables can siginificantly speed up gathering data needed for calculation, but only if the database can use index to lookup this data. Therefore it is strongly recommended to specify all key fields or majority of the key fields (left-to-right) in the joinining criteria. Remember that the order of the key fields of index matters, searching firstname in the phonebook indexed on [surname, firstname] causes a table scan.
Fast processing of stream row
Fetching the rows from a table blocks the database connection. Therefore the processing of a row should be as fast as possible, because it is blocking the database connection.
Avoid loading big amount of rows to memory
Collecting big amount of rows to memory using collect { it } and processing it later may lead to out of memory exception.
Transactional tables:
...
Sorting
If you need to perform sorting on big data, it should be done on an indexed field, otherwise it will take very long time.