Freelancer 로고서비스의 이용 방법일자리 탐색 로그인 회원 가입 프로젝트 등록
Find Jobs
Hire Freelancers
Get Ideas
The ultimate guide to hiring a web developer in 2021 The 10 best tools for web development

How to make SQL queries faster and more efficient

Speed up SQL queries and retrieve data faster using these 23 tricks.
2021. 9. 20. • 6 분간 읽음
표지 사진

Speed up your SQL queries and retrieve data faster from your SQL server

SQL stands for Structured Query Language,  a programming language that lets you retreive data from a databse. Performance matters when retrieving this data, which is why it's important to learn how to write efficient SQL queries. Because efficient queries = faster retrieval. 
Regular SQL queries, when optimized for better and faster performance, save time for both  database administrators  and SQL developers. To achieve faster, more efficient and credible database queries, you need a fast SQL server.
If you have no one to guide you in how to achieve this, there are experts to help. You can hire an SQL professional  from Freelancer  to explain the process to you, or learn ways to make your SQL queries faster.

Below are 23 rules to make your SQL faster and more efficient

1. Batch data deletion and updates

When you are deleting or updating your data, use as small batches as you possibly can. This will avoid loss or killing of your data in case there is a rollback.
Working on smaller batches also enhances concurrency, as data other than the part you are deleting or updating can continue doing other work.

2. Use automatic partitioning SQL server features

Features of the data engine’s automatic partitioning is a big advantage to SQL Server Enterprise users. Split single partitions into multiples to move larger data amounts, by using SWITCH instead of DELETE and INSERT.
This is because instead of inserting and deleting large data amounts, you are only changing the metadata. This takes a very short time.

3. Convert scalar functions into table-valued functions

Convert your scalar function to a table-valued function in order to make the performance faster, and reduce the time used.

Featured Work in Programming

Portfolio item image
Conversion PSD to HTML
by puneetjaini
Portfolio item image
Trade Analysis Tool
by amusto
Portfolio item image
Delphi Application to Consume SOAP
by qprotex
Portfolio item image
WPF Hybrid App for Desktop & Tablet
by amusto

4. Instead of UPDATE, use CASE

In 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.

5. Reduce nested views to reduce lags

Performance lags come about due to nesting views that are inside other views, which are also inside other views and so on.
This nesting causes too many data returns for every single query, which either makes the database crawl, or completely give up and give no returns.
Minimizing nesting is a simple way to make your SQl query efficient and significantly improve speeds.

6. Data pre-staging

Data pre-staging means you can have your reports faster. Join data tables ahead of reporting, presentation or writing time to avoid joining them together at once.

7. Use temp tables

Temporary tables  come in handy in several situations, especially when you are joining a small table to a larger one.
Speed up query execution in your SQL server by taking any data needed out of the large table, transferring it to a temp table and join with that.
This reduces the power required in processing.

8. Avoid using re-use code

When you use another person’s code, chances are you might pull more data than you really need. Cutting this data down may be hard, but if you trim a re-used code to your needs, there is no reason for ending up with huge data clusters.

9. Avoid negative searches

Nothing slows data as much as carrying out negative searches. To avoid this, rewrite your queries with better indexes, especially for large amounts of data.

10. Avoid cursors

It is always best to avoid cursors, because they can slow you down. If you can’t avoid them make use of temp tables to help save time and speed up SQL queries.

11. Use only the correct number of columns you need

When you code all queries with SELECT, you pull off more data than you need. So here's how to make the SELECT query faster: before doing a SELECT, make sure you have the correct number of columns against as many rows as you want. This will speed up your processes.

12. Count your rows using the system table

If you need to count your rows, make it simple by selecting your rows from sysindexes.  Below is the best way to add rows to your table.
“SELECT rows FROM sysindexes WHERE object_name (id) = ‘T1’ AND indexid = 1”

13. Don’t count everything in the table

If you need to check that some data exists, you will need to carry out an action. People often try this:
SET @CT = (SELECT COUNT (*) FROM dbo.T1);If @CT > 0BEGIN <Do something>END
This is unnecessary and time wasting. The best action to take is:
If EXISTS (SELECT 1 FROM dbo.T1)BEGIN<Do something>END
This helps you to avoid counting every item on the table. When you use EXIST, the SQL server recognizes it and acts, making faster returns.
If this still seems too hard to understand, do not hesitate  to hire an SQL expert to help you .

14. Do not use Globally Unique Identifiers (GUIDs)

To order table data, avoid using  GUIDs  as much as possible because they can easily cause very fast break off your table.
Use IDENTITY or DATE for dramatic break off that will take only a couple of minutesand substantially speed up your SQL query.

Freelance Programming Experts

사용자 아바타
국기 () Dmytro B. @d1mf13
25 USD / hour
5.0 (158 건의 리뷰) PHP Script Install Windows Desktop System Admin Linux
Visit profile
사용자 아바타
국기 () Junaid A. @JunaidAhmad92
50 USD / hour
5.0 (44 건의 리뷰) Wireless Electronics Microcontroller Electrical Engineering Embedded Software
Visit profile
사용자 아바타
국기 () Shivam P. @shivampanchal
20 USD / hour
4.8 (159 건의 리뷰) PHP JavaScript Python Website Design Copywriting
Visit profile
사용자 아바타
국기 () Paris Pallas @paris2785
34 USD / hour
4.9 (655 건의 리뷰) Proofreading Excel SQL Powerpoint Oracle
Visit profile

15. Avoid triggers

It is not necessary to use triggers, as whatever you plan on doing to your data will go through the same transactions as the previous operations.
If you go ahead and use triggers, you could lock several tables until the trigger completes its cycle. Split the data into different transactions to lock up just a few resources, making the transactions go faster.

16. Separate large and small transactions

If you handle several tables in one transaction, you might lock them all until your transaction is complete. Avoid blocking off transactions by breaking them into several routines, with each routine operating singularly at a time.
This will reduce the amount and number of blocks, and will free up tables for operations to continue taking place.

17. Do not double dip

Double dipping is running different queries on tables and later putting the queries on temp tables, then joining the large tables and temp tables together.
This takes a huge toll on performance. To. make your SQL query more efficient, query only the large tables once.

18. Whenever you can, use stored procedures

Stored procedures have so many advantages that make your work easier, and writing queries faster. They slow down traffic because with stored procedures, calls become shorter.
If you use profiler, and other tools that allow you to identify statistics concerning performance, it gets easier to trace. When you use stored procedures, you can use your plans of execution repeatedly.

19. Avoid ORM

Object-Relational Mappers  (ORMs) give the worst code in the world of technology today. This poor code is responsible for much of the bad performance you will encounter.
If you are not able to completely avoid them, the best you can do is minimize them by writing stored procedures that are completely your own, and have ORM use yours instead of those it creates.

20. Go slow

Do not ever assume you have to complete every task of updating and deleting in one single day. This is wrong, especially archiving data.
Take your time and work on the operation for as long as you need, while making use of the small batches. Working too quickly to finish the work only slows down your queries, and this might bring down your systems.

21. Use cursors less

Cursors cause many problems, especially to speed. Besides low speed, they can also cause blockages where one operation leads to the blockage of other operations.
This can last for longer than expected and it affects your systems concurrency, slowing everything down. Speed up your SQL queries by avoiding cursors.

22. Use AWR and ADDM

As queries get older from too much use, their performance tends to worsen. These uses are from upgrades, structural changes, database changes and applications.
To understand these changes better and learn about the plan of execution, use Automatic Database Diagnostic Monitor (ADDM) and automatic workload repository (AWR).
This will help to increase SQL query speed over a period of time.

23. Avoid using the keyword DISTINCT

If you can reach all your objectives in other ways, avoid using the keyword DISTINCT as much as possible.
When you use DISTINCT you incur an extra operation, which slows all the queries down and makes it almost impossible to get what you need.


SQL queries are not just about writing, but about writing them to achieve efficiency. It is only when they run efficiently and perform fast enough that applications and databases can produce the expected results.
Ignoring important issues can affect the performance of data retrieval from the databases.

관련 스토리

The 10 best tools for web development
9 분간 읽음
The ultimate guide to hiring a web developer in 2021
11 분간 읽음
5 programming languages you need to learn and 5 you should avoid
8 분간 읽음
Top 10 tools for front end web development
5 분간 읽음
Top 10 tools for back end web development
5 분간 읽음

최신 정보의 수신

저희 뉴스 레터에 가입하시면, 주요 주제에 대한 최신 정보를 받아 보실 수 있습니다.
가입해 주셔서 감사합니다! 저희가 보내 드리는 소식은 고객님의 메일 수신함에서 살펴 봐 주시기 바랍니다.
이미 가입이 되어 있는 이메일 주소입니다.
죄송합니다만, 원인을 알 수 없는 오류가 발생하였습니다. 재시도하여 주시기 바랍니다. 같은 문제가 계속 반복되는 경우에는, 고객 지원팀 으로 다음의 오류 정보와 함께 문의하여 주시기 바랍니다. (Error Code: )

고객님의 프로젝트를 도와 드릴 저희 기술 부조종사팀과의 대화 시작

지금 도움 요청

고객님 맞춤형으로 추천해 드리는 게시글

게시글의 섬네일 Building your business' website from the ground up
Learn the complete end-to-end process of building a successful website for your business in our comprehensive guide 
19 min read
게시글의 섬네일 Your complete guide to hiring a programmer
You can hire a programmer to solve just about any complex problem, the problem is knowing how to hire the right professional for the job. Learn how..
13 min read
게시글의 섬네일 Why hiring a graphic designer is vital for your business in 2020
Great graphic design will solidify your brand identity and drive revenue. Find out how to hire a great designer and what you should expect to pay.
9 min read
게시글의 섬네일 Why your website needs great writing
The copy on your website matters. Hiring a professional writer will help you engage, inform and motivate your customers to convert to your offering.
4 min read
등록 사용자 전체 등록 건수(일자리)
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2022 Freelancer Technology Pty Limited (ACN 142 189 759)
There is no internet connection
미리 보기 화면을 준비 중...