Effective programming to interact with DB

Introduction•Thamaraikanni Panneerselvam•10+ years of IT experience•https://www.facebook.com/thamaraikanni.p•tamibe@gmail.com

DB & ORM

Interaction which need attention

•Fetching huge data• Interacting via ORM framework •Database connection maintenance•Misc.

Fetching Huge Data • Indexing•Avoid searching with non-indexed columns•Partitioning the huge table•Fetch the data in specific sequence from related table

Indexing

• Index page of the book. Most common Index types are B-Tree and Hash.•B-Tree Index type will have separate table with indexed columns sorted in ascending order. •Hash Index works with memory backend supports, it supports Key-value stores.

Indexing Benefits

•An index can filter and/or sort values •A leftmost prefix can be used

• Indexes on several columns are useful

•Order of columns in composite keys is important

Indexing Overhead•MySQL only uses 1 index per table per query

•Can't index full TEXT fields•Can speed up queries (good)

• Increases the size of your dataset (bad)

•Slows down writes (bad)

Indexing – Explain Plan

Partition

Partition•Partitioning divides a table into smaller parts called “partitions”•Queries works with specific memory blocks•Only works with Range and List partitioning•Ease of purging old data

Partition

Partition

Partition Overhead

•More indexes! •Wrong partition key•Partitions over 124 will slow down queries

Partition - issue

•Table had 13core records, which had 24 partitions, out of which 4 had values others are blank.•Response time was 800ms on load it crosses 1s.•Removed all the unused .•Response time reduced 400ms.•Resource utilization on DB for this specific query

reduced by 35%

DB connection pool

DB connection pool

•Readily available DB connections•Reduces the new DB connection creation•Minimizes the stale connections•Connection reused

DB connection Pool Parameters

• Initial Pool•Max Size•Timeout•Validator query

DB connection Pool - Issues

•All connections are in use.• Idle timeout•Maximum time exceed•User cancel the operation

ORM framework - Issues

•Loading all the child objects•Many select queries for related tables•OneToMany relationship•Hibernate object are used only for the persistence, for business service use transactional objects.

Misc.

•Caching•EXISTS •OUTER Join benefits• IN & IS NULL limitations• LIKE operator will not use Index

Example SELECT * FROM Employee WHERE department_id IN (SELECT department_id FROM department WHERE department_name = ‘Analyst’);

EXISTS SELECT * FROM Employee e WHERE EXISTS (SELECT 1 FROM department d WHERE department_name = ‘Analyst’ AND d.department_id = e.department_id);

Example SELECT * FROM Department d WHERE d.department_id not in (SELECT department_id FROM employee e);

Outer Joins

SELECT * FROM Department d left outer join Employee e ON e.department_id = d.department_id WHERE d.department_id is null;

LIKE Operator SELECT * FROM Employee where name like ‘John%’;

=> Index will be used it is range search.

SELECT * FROM Employee where name like ‘%Denslin’;

=> Index will not be used

Better Computation

Product

PromotionInventoryProduct Spec

Better ComputationSELECT p.product_id , COUNT(DISTINCT ( CASE WHEN ps.ProductSpec_id IS NOT NULL THEN ps.ProductSpec_id END )) T2_COUNT , COUNT(DISTINCT ( CASE WHEN i.inventory_id IS NOT NULL THEN i.inventory_id END )) T3_COUNT , COUNT(DISTINCT ( CASE WHEN pr.promotion_id IS NOT NULL THEN pr.promotion_id END )) T4_COUNT FROM (SELECT Column1 FROM Product WHERE product_id IN ( :values ) ) p LEFT OUTER JOIN ProductSpec ps ON ps.product_id = p.product_id LEFT OUTER JOIN Inventory i ON i.product_id = p.product_id LEFT OUTER JOIN Promotion pr ON pr.product_id = p.product_idGROUP BY p.product_id

Better Computation - example

Better Computation - example

Better Computation - example SELECT p.product_name , Min(case when pa.Type = ‘Category’ then pa.Name end) Category , Min(case when pa.Type = ‘Seller’ then pa.Name end) Seller FROM Product p, Product_association pa WHERE p.product_id = pa.product_idGROUP BY p.product_name

Q & A

Thank you