10%

Try : Insurtech, Application Development

Edtech(5)

Events(31)

Interviews(9)

Life@mantra(10)

Logistics(1)

Strategy(13)

Testing(6)

Android(40)

Backend(28)

Dev Ops(2)

Enterprise Solution(20)

Frontend(28)

iOS(37)

Javascript(13)

AI in Insurance(24)

Insurtech(57)

Product Innovation(33)

Solutions(13)

Augmented Reality(7)

Customer Journey(7)

Design(6)

User Experience(21)

Artificial Intelligence(93)

Bitcoin(7)

Blockchain(14)

Cognitive Computing(7)

Computer Vision(6)

Data Science(13)

FinTech(41)

Intelligent Automation(25)

Machine Learning(43)

Natural Language Processing(10)

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

MantraTalks Podcast with Richard Roy Mendonce: Covid-19 & the Disruption in Healthcare

11 minutes, 21 seconds read

The outbreak of COVID-19 has put immense pressure on the healthcare sector. The supply chain of medical supplies was hit. The sudden surge of patients made it difficult to manage the hospital operations. Since priority had to be given to COVID patients, regular consults and elective surgeries were delayed. 

To go one step further and understand the disruption in healthcare amidst these adverse conditions, we interviewed Mr. Richard Roy Mendonce, Head Digital Strategy at Yashoda Hospitals to shed light on the role of technology in combating the current challenges faced by healthcare and possible mitigation strategies.

Mr. Richard Roy Mendonce has a strong domain expertise within the Healthcare Industry and has successfully infused digital transformations in various organizations like Columbia Asia Group of Hospitals, Sakra World Hospital, and Manipal Hospitals Group that ensured better customer experience and increased business. 

A Digital Strategist, he currently leads the digital efforts at Yashoda Hospitals, which is among the oldest and biggest healthcare groups in the region. He has nearly a decade of experience in digital marketing, digital strategy and digital transformation, with a distinctive ability to develop highly effective and measurable strategies that drive revenue growth, new customers, brand awareness and reputation. 

Constantly inspired & fascinated by the dynamics of the digital landscape, he has developed a skill set built on the art of leveraging digital technologies focused to deliver positive user experiences and achieve business objectives. In 2019, he was awarded as one of the 50 Most Influential Strategy Leaders by COM Global at World Marketing Congress.

Connect with Mr. Richard Roy Mendonce – LinkedIn

Watch the interview: 

The excerpt from the interview:

Covid-19 & the Disruption in Healthcare

Many hospitals are reassessing their digital marketing strategy and budgets in light of the uncertain economic situation. Most healthcare organizations can benefit from taking this time to strategize and plan for the future, rather than putting the brakes on. Please share some key insights into the changing patient behavior and the steps you are taking to reach them? Also, How will the healthcare marketing landscape change Beyond COVID-19?

Mr. Richard: In terms of healthcare, especially telemedicine, COVID-19 has completely cut down the channel of visiting doctors in-person for a consult. Lack of options has increased more acceptance towards Telemedicine. A couple of months back, we compared the benefits and comfort of direct consultation to an online one. We were reluctant to have those experiences but now acceptance has increased. 

Another thing I feel is —  we do not need high-end technology or equipment. When we hear of telemedicine, what comes first to our mind is jazzy computers, high-tech connections, software, etc.; but that is not the case. Even a simple SMS/call/WhatsApp call is enough to connect with a doctor. We don’t really need any high-end equipment to start a telemedicine service. 

Today, most of the spending is being diverted to digital channels rather than traditional offline ones and it will continue to happen. Digital channels are more trackable, more efficient, and more controllable. Even digital connect to engage with offline channels is gaining momentum. Healthcare set-ups will have offline referral networks, business partners. Traditionally, there would be a sales team who go meet and connect with them. Now with the social distancing and lockdowns, even that connection is replaced with digital connections such as webinars, video calls, etc. 

Communication in marketing has also changed. Before COVID-19, the communication was “Don’t ignore your health, come to us”. During the COVID-19 situation, the communication was “Come to us only if it is an emergency, it’s better to stay at home”. Post COVID-19, the communication might be- “Wherever you are, we are accessible, come to us or use our online services.” 

Telemedicine in a Post-Pandemic India

In the short time since the Pandemic began, the impact of social distancing norms has changed our daily lives & routines. Due to which, services like live remote consultations and telemedicine are getting more attention. Telemedicine is likely a permanent beneficiary of the pandemic. Do you think it will remain a key mode of healthcare delivery even after restrictions are lifted? Are there other digitally-enabled services that can potentially find greater adoption in a Post-Pandemic India?

Mr. Richard: Telemedicine will continue to be one of the modes of care delivery but that will not replace the existing care delivery system. Rather, it will be a mix of both. People will opt for telemedicine for the initial consultation (a non-serious one) and post-treatment follow-ups or review visits or to update on reports. People might get accustomed to telemedicine services but I think that will never replace serious conditions or surgical specialty where doctors need to examine personally to deliver proper care. 

In terms of acceptance level of technology, there has been wider acceptance for non-clinical support systems. For example, chatbots in place to address customer service and AI-driven platforms to check symptoms and guide the patient to respective specialists. This is not for prescriptions, but to enable patients to help themselves in availing services. 

Related: Healthcare Chatbots: Innovative, Efficient, and Low-cost Care

Medical supplies: Another area where digital platforms should have a wider scale of adoption is traveling for non-essential medical supplies. Pharma delivery is one sector that can go entirely digital. We can also have a format where physical stores are eliminated. Delivery can be from warehouse to customer. 

Diagnostics: Apart from radiology, diagnostics can go completely digital. Home care such as remote ICUs, remote monitoring could have potentially greater adoption in the current scenario. 

Disruption in healthcare will also include technologies to strengthen medical education and training.

Operational Challenges in Healthcare

From the operations point of view, digital transformation alone cannot help in preparing for an outbreak of this scale. The reality is we also have to be prepared for the possibility of a next Pandemic wave. The pandemic itself is testing the digital readiness and operational resilience of hospitals, in digitizing services and bringing innovation into healthcare. What are the operational challenges, as far as digital capabilities go, that hospitals are facing currently? And, what steps must they take to bridge these gaps?

Mr. Richard: We all know that the entire system was not geared up for a pandemic of this scale. Hospitals are facing both operational and clinical challenges. However, I’ll address this one particular issue from a digital angle. 

The biggest challenge for any hospital is the lack of a digital care platform and is still heavily dependent on paper-based systems. Now we know that anything can be sanitized but how do we sanitize paper documents. Patients have to carry these documents, touch them, and exchange multiple hands which can be potential carriers of the virus. Now it is more important to keep all the medical records digitized. 

Another aspect is the nature of this virus which is highly communicable and is leading to the community spread of this disease. Therefore, hospitals have a responsibility to maintain data at a patient-level so that contact tracing becomes much more easier and automated. So, maybe a symptom can be added as a trigger in the system and automatically do a contact tracing and give a list of people they can reach out to.

Yet another aspect in healthcare which is prone to change is remote working. Most of the industries such as IT have already geared up for remote working but healthcare has not. Many of the processes still need people coming to the office and working on a computer that is in the network. So, the disruption in healthcare relies on digital platforms to ensure that staff is efficiently deployed.

Changes in the Patient Experience

Both outpatient and in-patient treatment for all major non-communicable diseases including emergencies have declined. Going forward, as the country tries to resume life in the New Normal, industries like retail are experimenting with touchless interfaces to boost the customer’s confidence in shopping in-store. What changes, if any, do you foresee to the physical patient experience?

Mr. Richard: Wherever possible, currently hospitals are trying to minimize contact. Like airports, one can print their boarding pass, even hospitals can ask the patients not to wait in a line at the reception but rather book an appointment and make payments online. Once the appointment is booked, patients can just come and wait for the doctor’s call. We have seen multiple robotic-assisted surgeries where contact with the patient is avoided. Similarly, some technologies may come up taking vitals from the patient in a no-contact manner. There are hospitals in the country that have introduced innovative robots who screen patients coming to the hospitals. There are lots of innovations possible in this area. 

Role of AR, VR and AI in Digital Healthcare 

Huge volumes of data are flowing into the cloud, not just from doctors’ offices and imaging centers, but also from remote devices and sensors worn or operated by patients. By harnessing the vast amounts of data and putting it to work in applications, it helps care providers to improve effectiveness and efficiencies. Do you see technologies like AR/VR/AI playing a role in the future of digital healthcare in India? Can you share some examples of areas that Yashoda Hospitals has begun experimentation or implementation with these technologies?

Mr. Richard: Artificial intelligence, Machine Learning, Augmented Reality, Virtual Reality, Cloud systems, etc. are the buzzwords these days. I do believe that these technologies will pick pace in the healthcare industry as well. But I see a challenge there. Though all the data is on the cloud, the data is held by individual stakeholders and corporations. And standardization of data is the biggest challenge right now. 

So, any company which is working towards utilizing these technologies should first look at technologies that can bring data on one platform which is usable, accessible, and standardized without compromising confidential information of the patient. In terms of innovation at Yashoda hospitals, we are working on a couple of them such as AI-based radiology systems, optimizing customer journeys in hospitals, manpower planning, etc. 

Related: Medical Image Management: DICOM Images Sharing Process

Let’s take the patient discharge process for instance. Transitioning a customer from ‘in-patient’ to ‘out-patient’ is a significant challenge for any hospital, since it involves multiple departments. You’ve even stated before that it takes the integrated view of HIS (hospital information systems), EMR (electronic medical records), inventory, billing, and real-time updates of treatment progress to facilitate discharge at the click of a button. What is your experience in the transformation process and the ground realities of addressing this critical pain point? 

Mr. Richard: Theoretically speaking, the discharge process takes a lot of time but the reason it takes so much time is because it involves multiple stakeholders at a time- internal as well as external. It further gets complicated when the insurance is involved. I think all healthcare providers are looking to simplify the discharge process. The only way it is possible is having technology cut across stakeholders and in real-time. So wherever possible, we can avoid these internal communication delays. 

Return to Normal: The way forward

As hospitals plan for the complicated return stage (once restrictions are lifted), the volume of footfalls, testing, etc. will gradually increase. What advice can you share with other healthcare leaders to prepare their organization on the frontline to manage specific risks regarding employee safety, patient outcomes, etc? What investments (in remote patient monitoring, medical equipment, CRM systems, etc.) should healthcare organizations be making to respond to ‘the return to normal’?

Mr. Richard: I think that the precautionary steps taken by most of the healthcare providers are commendable. It is much better than in other countries across the world. We are in touch with a few of the major chains and the precautions that are being taken are phenomenal. Starting from thermal screens and fever clinics at the entrance, social distancing blogs; we have implemented Cluster Systems within our hospitals. It is a system where the employees are clustered in certain areas to minimize cross-contamination between employees. 

In terms of investment in technology, clinical data can be good to start working on. A good EMR system that seamlessly integrates and exchanges data between all relevant information systems is the need of the time. This investment will not just be in terms of technology but also behavioral change. 

So the system has to be friendly to seamlessly capture the data and make it available across systems. Using data efficiently is important to guide clinical decision support, developing user experience protocols and creating empowerment for the patient. 

Summing up

COVID-19 has changed a lot in us. The lockdown has unlocked a lot of things. It is a good time to innovate. Essential services would be a keyword used for a very long time now in every aspect. Be it shopping, be it food, be it health. And social distancing will be a new lifestyle. 

In this session, Mr. Richard shared insights on the disruption in healthcare and the importance of technological innovations in the new normal for hospitals.


AI is going to be essential for Insurers to gain that competitive edge in the post-pandemic world. Check out Hitee — an industry-pecific chatbot for driving customer engagement. For your specific requirements, please feel free to write to us at hello@mantralabsglobal.com.

More insights from the industry stalwarts:

Cancel

Knowledge thats worth delivered in your inbox

Loading More Posts ...
Go Top
bot

May i help you?

bot shadow

Our Website is
Best Experienced on
Chrome & Safari

safari icon