Try : Insurtech, Application Development

Edtech(3)

Events(27)

Life@mantra(8)

Logistics(1)

Strategy(6)

Testing(4)

Android(42)

Backend(29)

Dev Ops(2)

Enterprise Solution(12)

Frontend(29)

iOS(37)

Javascript(13)

Augmented Reality(7)

Customer Journey(7)

Design(5)

User Experience(19)

AI in Insurance(19)

Insurtech(47)

Product Innovation(27)

Solutions(6)

Artificial Intelligence(79)

Bitcoin(7)

Blockchain(14)

Cognitive Computing(6)

Computer Vision(5)

Data Science(11)

FinTech(38)

Intelligent Automation(19)

Machine Learning(41)

Natural Language Processing(5)

Top 10 SQL Query Optimization Tips to Improve Database Performance

5 minutes, 18 seconds read

SQL Query optimization is a process of writing thoughtful SQL queries to improve database performance. During development, the amount of data accessed and tested is less. Hence, developers get a quick response to the queries they write. But the problem starts when the project goes live and enormous data starts flooding the database. Such instances slow down SQL queries response drastically and create performance issues.

When working with large-scale data, even the most minor change can have a dramatic impact on performance.

SQL performance tuning can be an incredibly difficult task. Even a minor change can have a dramatic impact on performance. Here are the 10 most effective ways to optimize your SQL queries. 

  1. Indexing: Ensure proper indexing for quick access to the database.
  2. Select query: Specify the columns in SELECT query instead of SELECT* to avoid extra fetching load on the database.
  3. Running queries: Loops in query structure slows the sequence. Thus, avoid them.
  4. Matching records: Use EXITS() for matching if the record exists.
  5. Subqueries: Avoid correlated sub queries as it searches row by row, impacting the speed of SQL query processing.
  6. Wildcards: Use wildcards (e.g. %xx%) wisely as they search the entire database for matching results.
  7. Operators: Avoid using function at RHS of the operator.
  8. Fetching data: Always fetch limited data.
  9. Loading: Use a temporary table to handle bulk data.
  10. Selecting Rows: Use the clause WHERE instead of HAVING for primary filters.

SQL Query Optimization Tips with Examples

Tip 1: Proper Indexing

An index is a data structure that improves the speed of data retrieval operations on a database table. A unique index creates separate data columns without overlapping each other. Proper indexing ensures quicker access to the database, i.e. you’ll be able to select or sort rows faster. The following diagram explains the basics of indexing while structuring tables.

SQL query optimization - basics of structuring tables

TIP 2: Use SELECT <columns> instead of SELECT *

Specify the columns in the SELECT clause instead of using SELECT *. The unnecessary columns place extra load on the database, which slows down not just the single SQL, but the whole system.

Inefficient

SELECT * FROM employees

This query fetches all the data stored in the “employees” table such as phone number, activity dates, notes from sales, etc. which might not be required for a particular scenario.

Efficient

SELECT first_name, last_name, mobile, city, state FROM employees

This query will fetch only selected columns.

Tip 3: Avoid running queries in a loop

Coding SQL queries in loops slows down the entire sequence. Instead of writing a query that runs in a loop, you can use bulk insert and update depending on the situation. Suppose there are 1000 records. Here, the query will execute 1000 times.

Inefficient

for ($i = 0; $i < 10; $i++) {  
  $query = “INSERT INTO TBL (A,B,C) VALUES . . . .”;  
  $mysqli->query($query);  
  printf (“New Record has id %d.\ “, $mysqli->insert_id);
}

Efficient

INSERT INTO TBL (A,B,C) VALUES (1,2,3), (4,5,6). . . .

Tip 4: Does My record exists?

Normally, developers use EXITS() or COUNT() queries for matching a record entry. However, EXIT() is more efficient as it will exit as soon as finding a matching record; whereas, COUNT() will scan the entire table even if the record is found in the first row.

Inefficient

IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE ‘%JOHN%’) > 0 PRINT ‘YES’

Efficient

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE ‘%JOHN%’)
PRINT ‘YES’

Tip 5: A big NO for correlated subqueries

A correlated subquery depends on the parent or outer query. Since it executes row by row, it decreases the overall speed of the process.

Inefficient

SELECT c.Name, c.City,(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

Here, the problem is — the inner query is run for each row returned by the outer query. Going over the “company” table again and again for every row processed by the outer query creates process overhead. Instead, for SQL query optimization, use JOIN to solve such problems.

Efficient

SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co   ON c.CompanyID = co.CompanyID

Tip 6: Use wildcard characters wisely

Wildcard characters can be either used as a prefix or a suffix. Using leading wildcard (%) in combination with an ending wildcard will search all records for a match anywhere within the selected field.

Inefficient

Select name from employees where name like ‘%avi%’

This query will pull the expected results of Avishek, Avinash, Avik and so on . However, it will also pull unexpected results, such as David, Xavier, Davin.    

Efficient

Select name from employees where name like ‘avi%’.

This query will pull only the expected results of Avishek, Avinash, Avik and so on. 

Tip 7: Avoid using SQL function on the RHS of the operator

Often developers use functions or methods with their SQL queries. 

Inefficient

Select * from Customer where YEAR(AccountCreatedOn) == 2005 and  MONTH(AccountCreatedOn) = 6

Note that even though AccountCreatedOn has an index, the above query changes the WHERE clause in such a way that this index cannot be used anymore.

Efficient

Select * From Customer Where AccountCreatedOn between ‘6/1/2005’ and ‘6/30/2005’

Tip 8: Always fetch limited data and target accurate results

Lesser the data retrieved, the faster the query will run. Rather than applying too many filters on the client-side, filter the data as much as possible at the server. This limits the data being sent on the wire and you’ll be able to see the results much faster.

Tip 9: Drop index before loading bulk data

If you want to insert thousands of rows in an online system, use a temporary table to load data. Ensure that this temporary table does not have any index. Since moving data from one table to another is much faster than loading them from an external source; you can now drop indexes on your primary table, move data from temporary to the final table, and finally recreate the indexes.

Tip 10: Use WHERE instead of HAVING

HAVING clause filters the rows after all the rows are selected. It is just like a filter. Do not use the HAVING clause for any other purposes. 

In the SQL Order of Operations, HAVING statements are calculated after WHERE statements. Therefore, executing the WHERE query is faster.

Hope you enjoyed reading these tips for SQL query optimization. If you have any questions, feel free to drop a comment or write to us at hello@mantralabsglobal.com.

You can learn more about SQL queries and syntax at W3Schools tutorial.

About Author: Avishek Kumar Singh is a Senior Tech Lead at Mantra Labs —  a leading application development service provider in insurtech and e-commerce domains. He has years of experience in developing robust web and mobile applications for enterprises.

Suggest reading – LAMP/MEAN Stack: Business and Developer Perspective

Cancel

Knowledge thats worth delivered in your inbox

Across the Insurance ecosystem, a special fraction within the industry is noteworthy for its adoption of new technologies ahead of others. However slow but sure, uberization of insurance has conventionally demonstrated a greater inclination towards digitization. Insurers now more than ever, need big data-driven insights to assess risk, reduce claims, and create value for their customers. 

92% of the C-Level Executives are increasing their pace of investment in big data and AI.

NewVantage Partners Executive Survey 2019 

Artificial Intelligence has brought about revolutionary benefits in the Insurance industry.

AI enriched solutions can remove the ceiling caps on collaboration, removes manual dependencies and report errors.

However, organizations today are facing a lot of challenges in reaping the actual benefits of AI.

5 Challenges for AI implementation for Insurers

5 AI Implementation Challenges in Insurance

Lack of Quality training data

AI can improve productivity and help in decision making through training datasets. According to the survey of the Dataconomy, nearly 81% of 225 data scientists found the process of AI training more difficult than expected even with the data they had. Around 76% were struggling to label and interpret the training data.

Clean vision, Process, and Support from Executive Leadership

AI is not a one time process. Maximum benefits can be reaped out of AI through clear vision, dedicated time, patience and guided leadership from industry experts and AI thought leaders.

Data in-silos

Organizational silos are ill-advised and are proven constrictive barriers to operational productivity & efficiency. Most businesses that have data kept in silos face challenges in collaboration, execution, and measurement of their bigger picture goals. 

Technology & Vendor selection

AI has grown sharp enough to penetrate through the organizations. As AI success stories are becoming numerous investment in AI is also getting higher. However big the hype is, does AI implementation suits your business process or not – is the biggest question. The insurtech industries have continued its growth trajectory in 2019; reaching a funding of $6B. With the help of these insurtech service firms, Insurance organizations have made progress, tackling the age-old insurance ills with AI-powered innovations.

People, Expertise and Technical competency

‘Skills and talent’ in the field of AI is the main barrier for AI transformation in their business.

Still playing catch-up to the US, China, and Japan — India has doubled its AI  workforce over the past few years to nearly 72,000 skilled professionals in 2019. 

Are you facing challenges with your Insurance process but have no idea where the disconnect is? Is your Insurance business process ripe for AI in the year 2020?

What is the right approach?

Join our Webinar — AI for Data-driven Insurers: Challenges, Opportunities & the Way Forward hosted by our CEO, Parag Sharma as he addresses Insurance business leaders on the 13th of February, 2020.

Register for the live webinar by Parag Sharma (AI Thought Leader & CEO Mantra Labs). 

Cancel

Knowledge thats worth delivered in your inbox

Ratemaking, or insurance pricing, is the process of fixing the rates or premiums that insurers charge for their policies. In insurance parlance, a unit of insurance represents a certain monetary value of coverage. Insurance companies usually base these on risk factors such as gender, age, etc. The Rate is simply the price per ‘unit of insurance’ for each unit exposed to liability. 

Typically, a unit of insurance (both in life and non-life) is equal to $1,000 worth of liability coverage. By that token, for 200 units of insurance purchased the liability coverage is $200,000. This value is the insurance ‘premium’. (This example is only to demonstrate the logic behind units of exposure, and is not an exact method for calculating premium value)

The cost of providing insurance coverage is actually unknown, which is why insurance rates are based on the predictions of future risk.  

Actuaries work wherever risk is present

Actuarial skills help measure the probability and risk of future events by understanding the past. They accomplish this by using probability theory, statistical analysis, and financial mathematics to predict future financial scenarios. 

Insurers rely on them, among other reasons, to determine the ‘gross premium’ value to collect from the customer that includes the premium amount (described earlier), a charge for covering losses and expenses (a fixture of any business) and a small margin of profit (to stay competitive). But insurers are also subject to regulations that limit how much they can actually charge customers. Being highly skilled in maths and statistics the actuary’s role is to determine the lowest possible premium that satisfies both the business and regulatory objectives.

Risk-Uncertainty Continuum

Source: Sam Gutterman, IAA Risk Book

Actuaries are essentially experts at managing risk, and owing to the fact that there are fewer actuaries in the World than most other professions — they are highly in demand. They lend their expertise to insurance, reinsurance, actuarial consultancies, investment, banking, regulatory bodies, rating agencies and government agencies. They are often attributed to the middle office, although it is not uncommon to find active roles in both the ‘front and middle’ office. 

Recently, they have also found greater roles in fast growing Internet startups and Big-Tech companies that are entering the insurance space. Take Gus Fuldner for instance, head of insurance at Uber and a highly sought after risk expert, who has a four-member actuarial team that is helping the company address new risks that are shaping their digital agenda. In fact, Uber believes in using actuaries with data science and predictive modelling skills to identify solutions for location tracking, driver monitoring, safety features, price determination, selfie-test for drivers to discourage account sharing, etc., among others.

Also read – Are Predictive Journeys moving beyond the hype?

Within the General Actuarial practice of Insurance there are 3 main disciplines — Pricing, Reserving and Capital. Pricing is prospective in nature, and it requires using statistical modelling to predict certain outcomes such as how much claims the insurer will have to pay. Reserving is perhaps more retrospective in nature, and involves applying statistical techniques for identifying how much money should be set aside for certain liabilities like claims. Capital actuaries, on the other hand, assess the valuation, solvency and future capital requirements of the insurance business.

New Product Development in Insurance

Insurance companies often respond to a growing market need or a potential technological disruptor when deciding new products/ tweaking old ones. They may be trying to address a certain business problem or planning new revenue streams for the organization. Typically, new products are built with the customer in mind. The more ‘benefit-rich’ it is, the easier it is to push on to the customer.

Normally, a group of business owners will first identify a broader business objective, let’s say — providing fire insurance protection for sub-urban, residential homeowners in North California. This may be a class of products that the insurer wants to open. In order to create this new product, they may want to study the market more carefully to understand what the risks involved are; if the product is beneficial to the target demographic, is profitable to the insurer, what is the expected value of claims, what insurance premium to collect, etc.

There are many forces external to the insurance company — economic trends, the agendas of independent agents, the activities of competitors, and the expectations and price sensitivity of the insurance market — which directly affect the premium volume and profitability of the product.

Dynamic Factors Influencing New Product Development in Insurance

Source: Deloitte Insights

To determine insurance rate levels and equitable rating plans, ratemaking becomes essential. Statistical & forecasting models are created to analyze historical premiums, claims, demographic changes, property valuations, zonal structuring, and regulatory forces. Generalized linear models, clustering, classification, and regression trees are some examples of modeling techniques used to study high volumes of past data. 

Based on these models, an actuary can predict loss ratios on a sample population that represents the insurer’s target audience. With this information, cash flows can be projected on the product. The insurance rate can also be calculated that will cover all future loss costs, contingency loads, and profits required to sustain an insurance product. Ultimately, the actuary will try to build a high level of confidence in the likelihood of a loss occurring. 

This blog is a two-part series on new product development in insurance. In the next part, we will take a more focused view of the product development actuary’s role in creating new insurance products.

Cancel

Knowledge thats worth delivered in your inbox

Loading More Posts ...
Go Top

May i help you?

Our Website is
Best Experienced on
Chrome & Safari

safari icon