10%

Try : Insurtech, Application Development

Edtech(5)

Events(33)

Interviews(10)

Life@mantra(10)

Logistics(1)

Strategy(14)

Testing(8)

Android(44)

Backend(28)

Dev Ops(2)

Enterprise Solution(22)

Frontend(28)

iOS(40)

Javascript(13)

AI in Insurance(29)

Insurtech(58)

Product Innovation(36)

Solutions(15)

Augmented Reality(10)

Customer Journey(8)

Design(7)

User Experience(25)

E-health(3)

HealthTech(6)

mHealth(3)

Telehealth Care(1)

Artificial Intelligence(103)

Bitcoin(7)

Blockchain(14)

Cognitive Computing(7)

Computer Vision(6)

Data Science(14)

FinTech(41)

Intelligent Automation(26)

Machine Learning(45)

Natural Language Processing(12)

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

Common FAQs

What is SQL optimization?

SQL optimization is a process of using SQL queries in the best possible way to get accurate and fast database results. The most common database queries are INSERT, SELECT, UPDATE, DELETE, and CALL. These are coupled with subqueries to filter the results. This is where people need to think of optimization to get accurate results with fewer resources and improve database performance.

What is SQL query tuning?

SQL optimization is also known as SQL query tuning. Basically, it is a process of smartly using SQL queries to increase the speed of fetching data and improve overall database performance.

What are the different query optimization techniques?

There are two most common query optimization techniques – cost-based optimization and rule (logic) based optimization. For large databases, a cost-based query optimization technique is useful as it table join methods to deliver the required output. Rule-based optimization combines two or more queries based on relational expressions. The following example illustrates rule-based query optimization.
rule-based query optimization

Cancel

Knowledge thats worth delivered in your inbox

Enhancing digital patient experience with healthcare chatbots

5 minutes read

Chatbots are fast emerging at the forefront of user engagement across industries. In 2021, healthcare is undoubtedly being touted as one of the most important industries due to the noticeable surge in demand amid the pandemic and its subsequent waves. The Global Healthcare Chatbots Market is expected to exceed over US$ 314.63 Million by 2024 at a CAGR of 20.58%.

Chatbots are being seen as those with high potential to revolutionize healthcare. They act as the perfect support system to agents on the floor by providing the first-step resolution to the customer, in terms of understanding intent and need, boost efficiency, and also improve the accuracy of symptom detection and ailment identification, preventive care, feedback procedures, claim filing and processing and more.

At the outset of the COVID-19 pandemic, digital tools in healthcare, most commonly chatbots, rose to the forefront of healthcare solutions. Providence St. Joseph Health, Mass General Brigham, Care Health Insurance (formerly Religare), and several other notable names built and rolled out artificial intelligence-based chatbots to help with diagnostics at the first stage before a human-human virtual contact, especially while differentiating between possible COVID-19 cases and other ailments. The CDC also hosts an AI-driven chatbot on its website to help screen for coronavirus infections. Similarly, the World Health Organization (WHO) partnered with a messaging app named Ratuken Viber, to develop an interactive chatbot for accurate information about COVID-19 in multiple languages. This allowed WHO to reach up to 1 billion people located anywhere in the world, at any time of the day, in their respective native languages.

For Care Health Insurance, Mantra Labs deployed their Conversational AI Chatbot with AR-based virtual support, called Hitee, trained to converse in multiple languages. This led to 10X interactions over the previous basic chatbot; 5X more conversions through Vanilla Web Experience; Drop-in Customer Queries over Voice Support by 20% among other benefits.

Artificial Intelligence’s role in the healthcare industry has been growing strength by strength over the years. According to the global tech market advisory firm ABI Research, AI spending in the healthcare and pharmaceutical industries is expected to increase from $463 million in 2019 to more than $2 billion over the next 5 years, healthtechmagazine.net has reported. 

Speaking of key features available on a healthcare chatbot, Anonymity; Monitoring; Personalization; collecting Physical vitals (including oxygenation, heart rhythm, body temperature) via mobile sensors; monitoring patient behavior via facial recognition; Real-time interaction; and Scalability, feature top of the list. 

However, while covering the wide gamut of a healthcare bot’s capabilities, it is trained on the following factors to come in handy on a business or human-need basis. Read on: 

Remote, Virtual Consults 

Chatbots were seen surging exponentially in the year 2016, however, the year 2020 and onwards brought back the possibility of adding on to healthcare bot capabilities as people continued to stay home amid the COVID-19 pandemic and subsequent lockdowns. Chatbots work as the frontline customer support for Quick Symptom Assessment where the intent is understood and a patient’s queries are answered, including connection with an agent for follow-up service, Booking an Appointment with doctors, and more. 

Mental Health Therapy

Even though anxiety, depression, and other mental health-related disorders and their subsequent awareness have been the talk around the world, even before the pandemic hit, the pandemic year, once again could be attributed to increased use of bots to seek support or a conversation to work through their anxiety and more amid trying times. The popular apps, Woebot and Wysa, both gained popularity and recognition during the previous months as a go-to Wellness Advisor. 

An AI Wellness Advisor can also take the form of a chatbot that sends regular reminders on meal and water consumption timings, nutrition charts including requisite consultation with nutritionists, lifestyle advice, and more. 

Patient Health Monitoring via wearables 

Wearable technologies like wearable heart monitors, Bluetooth-enabled scales, glucose monitors, skin patches, shoes, belts, or maternity care trackers promise to redefine assessment of health behaviors in a non-invasive manner and helps acquire, transmit, process, and store patient data, thereby making it a breeze for clinicians to retrieve it as and when they need it.

Remote patient monitoring devices also enable patients to share updates on their vitals and their environment from the convenience and comfort of home, a feature that’s gained higher popularity amid the pandemic.

A healthcare chatbot for healthcare has the capability to check existing insurance coverage, help file claims and track the status of claims. 

What’s in store for the future of chatbots in Healthcare? 

The three main areas where healthcare chatbots can be particularly useful include timely health diagnostics, patient engagement outside medical facilities, and mental health care. 

According to Gartner, conversational AI will supersede cloud and mobile as the most important imperative for the next ten years. 

“For AI to succeed in healthcare over the long-term, consumer comfort and confidence should be front and center. Leveraging AI behind the scenes or in supporting roles could collectively ease us into understanding its value without risking alienation,” reads a May 2021 Forbes article titled, The Doctor Is In: Three Predictions For The Future Of AI In Healthcare. 

Cancel

Knowledge thats worth delivered in your inbox

Loading More Posts ...
Go Top
bot

May i help you?

bot shadow