DB Connections are expensive Normally following things happen when your backend service talks to a database: service initiates a connection to DB using the database driver db does the authentication and establishes a network session (tcp) if succeeded program/service performs some db operation (CRUD) which follows authorization (if user/role is allowed to perform the requested operation) Now if the service decides to close the connection, it has to do these all over again which is quite an expensive process.

What are the things that should be on your checklist when you are facing a situation where your db queries are taking a lot of time? Cross check simple things such as if you would require pagination. If your filters are none (or too broad) even with optimized query plan/indexing query can take some time. Take the slow query, view the execution plan and cross check if you would require indexing, if your query is set to run with the indices (eg: is the query properly making use of functional indices?

ifnull <=> nvl Oracle: select nvl(null,1) from dual; Mysql: select ifnull(null,1) from dual; dual is optional in mysql. Oracle needs a ‘from expression’ for all the queries. Dual table is just a dummy to serve that syntax desc dual; Name Null Type ------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DUMMY VARCHAR2(1) fetching rownum in oracle and mysql Oracle: select rownum, $column from $table (rownum is a pseudocolumn in oracle) Mysql: select row_number() over() as row_num, $column from $table SET @rownum:=0; SELECT @rownum:=@rownum+1 AS rownum, $column FROM $table (for older mysql versions)

Personally I haven’t extensively used redis. My limited understanding of redis would be summarized as: redis is in-memory database, stored in RAM (making use of underutilized RAM space in servers) being stored in primary RAM it’s very fast being stored in volatile storage (RAM) it vanished as system goes off, however there are ways to persist it in the file system redis uses key value storage and supports quite a few datatypes including list, hashmaps, geocodes, etc how companies like twitter have made an extensive use of redis in caching for faster retrieval (low latency when the throughput demand is high) it can be used as pub-sub model (message broker) Trivia: Redis is an acronym for “Remote Dictionary Server”

DB Connections are expensive Normally following things happen when your backend service talks to a database: service initiates a connection to DB using the database driver db does the authentication and establishes a network session (tcp) if succeeded program/service performs some db operation (CRUD) which follows authorization (if user/role is allowed to perform the requested operation) Now if the service decides to close the connection, it has to do these all over again which is quite an expensive process.