Make the DB Optimized

Recently I got a issue with slowness in a data pipeline.Real issue was particular flow id was taking so much time to get it done. But the same time there are some other things are too supposed to happening.It is because of previous task took lot of time.

I had a look on that particular SQL file and search for that particular tables with go with that query. Then I get a sample instance ids and execute to get a idea . didn’t get much idea, so i decided to use explain sql key word.from that usually i am getting how many raws are getting affected and what can i do for make it fast. mmmm… wasn’t very much help full actually. Now what i have decided to go for DDL file and check what’s happening .

I have not index the table , wait i can use that to make it fast .but i just need to know more about this so these are the ways that make you query fast.

while we are updating the tables from the SQL query, an UPDATE statement writes longer to a table than a CASE statement, because of its logging. An inline CASE statement chooses what is preferred before writing it on the table, thus making it a very simple way to speed up your SQL query.

so instead of just telling that I would like to give some examples

SQL query returns the maximum hourly rate for each job title in the HumanResources.Employee table. The HAVING clause restricts the titles to those that are held by men with a maximum pay rate greater than 80 dollars or women with a maximum pay rate greater than 60 dollars.

focus on the two CASE statements with in the HAVING statement , and read what we are gonna get from the above paragraph.Then easy to grab it once.

2.

we could store some rows on one machine and others on another machine. The idea to split the data by row is called horizontal partitioning.

ALTER TABLE
shopping_carts
PARTITION BY RANGE(id)
(
Partition p0 VALUES LESS THAN (1234),
Partition p1 VALUES LESS THAN (4567),
Partition p2 VALUES LESS THAN MAXVALUE
);

3.

Using OR statement is kind of tricky , if you are gonna match two couple of ID s at same query in joined table using OR keyword ,it has heavy impact on the performance.Basically ,instead of doing this,

try this kind of behavior:

from that we can reduce the execution time and the total reads happens on the SQL server.

4.

Even though i mentioned that I have used the index ,didn’t explain how it’s work.Here we go:

Assume we have an index for a primary key. This will create an ordered list of primary key values in a separate table, each entry has a pointer points to the relative value in the original table.

So, whenever we want to access a table using the primary key, it will use binary search algorithm (takes time of O(LogN)) to access the required value in the Index table, and then, go to the relative value in the original table.

when we are using composite indexes by having multiple columns ,they are getting sorted according to their values.

Create index:

If we want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas.

ALTER table by create or destroy Index:

--

--

--

Software Engineer | Data Engineer | AI Enthusiast

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Multiple ways to configure Spring

CS373 Spring 2021 Week 2: Shikhar Gupta

Emulators, Machines & Preservation

Setup Your SEO Toolbox

the differences between static and dynamic libraries

Native Mobile Apps are the New Flash

Debugging the error: $ is not defined

Database per service — Microservices Design Pattern

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Gayan Sanjeewa

Gayan Sanjeewa

Software Engineer | Data Engineer | AI Enthusiast

More from Medium

Numeric vs. Decimal Data Types in SQL

NOSQL VS SQL MISCONCEPTIONS

Common SQL Queries: ORDER BY, LIMIT, BETWEEN, NULL, COUNT, GROUP BY

How to import JSON file into SQL Server Database | Worksheet Systems Blog