How
to optimize table JOIN if there are many tables in a JOIN statement in the SQL
Server? How to decrease numbers of tables in a JOIN?
It is highly recommended we should not JOIN more
than four or five tables in a single query. This number varies according to
data volume of a table. Why?
I want to share a real world
example. One senior developer joined a new company. There was a database which
was facing a lot performance issues. To analyze the root cause, that developer
writes a query which joins four tables. Those
tables contain a very large volume of data. Hence that query took time to fetch
records and it locked four tables in significant period of time due to which
other queries got blocked. After some
time it blocked whole database. When
client came to know that new employee got fired. A very simple mistake became
cause this accident.
How
to avoid numbers of tables in a JOIN?
Basic question is if there is
business need to join more than five tables to fetch some data. What is
alternative approach? How to meet this requirement without joining five tables?
Answer is very simple. First join only three tables and load the intermediate
data in a temporary table. Then join remaining tables with the temporary table.
For example:
Business need:
SELECT T1.Id, T2.C, T3.C, T4.C, T5.C
FROM T1 INNER JOIN T2 ON T1.Id = T2.Id
INNER JOIN
T3 ON T3.Id = T2.Id
INNER JOIN
T4 ON T4.Id = T3.Id
INNER JOIN
T5 ON T5.Id = T4.Id
Our approach should be:
--Step 1
SELECT T1.Id, T2.C, T3.C
INTO #T
FROM T1 INNER JOIN T2 ON T1.Id = T2.Id
INNER JOIN
T3 ON T3.Id = T2.Id
--Step 2
SELECT #T.*,
T4.C, T5.C
FROM #T INNER JOIN T4 ON #T.Id = T4.Id
INNER JOIN
T5 ON T5.Id = T4.Id
But this solution is not as simple
as looks. There are many performance considerations before splitting complex
JOIN statement. A wrong splitting may decrease overall performance of the query
and a correct splitting may improve overall query performance significantly. For
example:
Data
set up:
--Creating any five
tables
CREATE TABLE
TA(
AID
INT PRIMARY KEY IDENTITY,
A1
INT,
A2
DATETIME DEFAULT(GETDATE())
)
CREATE TABLE
TB(
BID
BIGINT PRIMARY KEY,
B1
VARCHAR(10),
B2
UNIQUEIDENTIFIER
)
CREATE TABLE
TC(
CID
BIGINT PRIMARY KEY IDENTITY,
C1
UNIQUEIDENTIFIER DEFAULT(NEWID()),
C2
NVARCHAR(10)
)
CREATE TABLE
TD(
DID
BIGINT PRIMARY KEY IDENTITY,
D1
INT,
D2
NVARCHAR(10)
)
CREATE TABLE
TE(
EID
BIGINT PRIMARY KEY IDENTITY,
E1
VARCHAR(64),
E2
INT
)
GO
--Populating
records
INSERT INTO
TA(A1)
SELECT severity FROM sys.messages
GO 10
INSERT INTO
TB(BID,B1)
SELECT AID,LEFT(NEWID(),10) FROM TA
GO
INSERT INTO
TC(C1,C2)
SELECT NEWID(),B1 FROM TB
GO
INSERT INTO
TD(D1,D2)
SELECT CID,
V FROM TC ,(SELECT 'XXX' V UNION ALL SELECT 'YYYY') DT
UNION ALL
SELECT TOP(5) CID ,'ZZZ' FROM TC
GO
INSERT INTO
TE(E1,E2)
SELECT B1 + CAST(C1 AS VARCHAR(36)), 1
FROM TB INNER
JOIN TC ON TB.BID = TC.CID
Let’s assume our business
need is to join above all five tables and get output on the basis of those joined
tables. Below is our actual query:
DECLARE @P1 AS INT =
16,@P2 VARCHAR(1)= 'Z',@P3 AS INT = 1
SELECT *
FROM TA
INNER JOIN
TB ON TA.AID = TB.BID
INNER JOIN
TC ON TC.C2 = TB.B1
INNER JOIN
TD ON TD.D1 = TC.CID
INNER JOIN
TE ON TE.E1 = TB.B1 + CAST(TC.C1 AS VARCHAR(36))
WHERE TA.A1
= @P1
AND LEFT(TD.D2,1) = @P2
AND TE.E2
= @P3
As we discussed joining five
tables is bad practice. Also sometimes it decreases performance.
Average execution time of above query in my PC: 1 minute and 52
seconds
Total logical read:
Workfile: 34 * 97936
TD: 19416
TE: 22226
TC: 17844
TB: 14732
TA: 7838
= 3411880
To simplify above script, we
are going to split above join in two parts. First part will keep first three
tables and second part remaining tables:
--First part
DECLARE @P1 AS INT =
16,@P2 VARCHAR(1)= 'Z',@P3 AS INT = 1
SELECT *
INTO #T
FROM TA
INNER JOIN
TB ON TA.AID = TB.BID
INNER JOIN
TC ON TC.C2 = TB.B1
WHERE TA.A1
= @P1
AND TB.B2
IS NULL
--Second part
SELECT *
FROM #T T
INNER JOIN
TD ON TD.D1 = T.CID
INNER JOIN
TE ON TE.E1 = T.B1 + CAST(T.C1 AS VARCHAR(36))
WHERE LEFT(TD.D2,1) = @P2
AND TE.E2
= @P3
Average execution time of above query in my PC: 2 minutes and 38
seconds
Total logical read:
Workfile: 10 * 21544 + 94 * 69336
TD: 19416
TE: 22226
TC: 17844
TB: 14732
TA: 7838
#T: 24992
= 6840072
After simplifying the query
overall execution time has increased significantly. Logical read has become
double. After simplifying the query performance has decreased by almost 50% than
original query. Even that simplified query is better because large number of tables
in a single query may keep tables locked for long period of time which is
worst.
But this splitting of a
complex query is wrong. We have to think a lot before simplifying a complex
query. thumb rule is:
Split a complex query in such
a way that it has to fetch minimum possible records from a table. In the other word
logical read should be minimal.
Best way to split above query:
DECLARE @P1 AS INT =
16,@P2 VARCHAR(1)= 'Z',@P3 AS INT = 1
--First Part
SELECT *
INTO #P
FROM TD
INNER JOIN
TC ON TD.D1 = TC.CID
WHERE LEFT(TD.D2,1) = @P2
--Second part
SELECT *,TB.B1 + CAST(P.C1 AS VARCHAR(36)) AS N INTO#Q
FROM #P P INNER JOIN TB ON P.C2 = TB.B1
--Third part
SELECT *
FROM
#Q Q
INNER JOIN
TA ON TA.AID = Q.BID
INNER JOIN
TE ON TE.E1 = Q.N
WHERE TA.A1
= @P1
AND TE.E2
= @P3
Average execution time of above query in my PC: 22 seconds
Total logical read:
Workfile: 0
TD: 19416
TE: 22226
TC: 17844
TB: 14732
TA: 15
#P: 1
#Q: 1
= 74235
We can notice that logical
read has decreased almost 46 times than original query. Here we are gaining huge
performance. Hence, a skilfully simplification of a complex query may achieve
huge performance. Apart from this, an appropriate index on intermediate
temporary table can get extra performance benefit.
53 comments:
Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
PMP Certification
PMP Certification in Malaysia
I have been searching to find a comfort or effective procedure to complete this process and I think this is the most suitable way to do it effectively.
data science course in malaysia
data science certification
data science course
data science bootcamp malaysia
thanks for sharing nice information...
DevOps Training in Hyderabad
Thanks for sharing this information. I really like your blog post very much. You have really shared a informative and interesting blog post with people..
PMP Certification
PMP Certification in Malaysia
360DigiTMG PMP Course in Malaysia
Really impressive post. I read it whole and going to share it with my social circules. I enjoyed your article and planning to rewrite it on my own blog.
data science course
I am impressed by the information that you have on this blog. It shows how well you understand this subject.
data analytics course
big data analytics malaysia
big data course
Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here.
360DigiTMG PMP Certification
360DigiTMG PMP Certification in Malaysia
360DigiTMG PMP Course
360DigiTMG PMP Course in Malaysia
I am impressed by the information that you have on this blog. It shows how well you understand this subject.
data analytics course
big data analytics malaysia
big data course
A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one
data science course
It's late finding this act. At least, it's a thing to be familiar with that there are such events exist. I agree with your Blog and I will be back to inspect it more in the future so please keep up your act.
PMP Certification
PMP Course
PMP Course in Malaysia
PMP training in Malaysia
PMP Training
Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
data analytics course
big data analytics malaysia
big data course
I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well. In fact your creative writing abilities has inspired me to start my own Blog Engine blog now. Really the blogging is spreading its wings rapidly. Your write up is a fine example of it.
data science course
You actually make it look so easy with your performance but I find this matter to be actually something which I think I would never comprehend. It seems too complicated and extremely broad for me. I'm looking forward for your next post, I’ll try to get the hang of it!
360DigiTMG PMP Certification
360DigiTMG PMP Course
360DigiTMG PMP Course in Malaysia
360DigiTMG PMP Training in Malaysia
360DigiTMG PMP Training
I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read !! I definitely really liked every part of it and i also have you saved to fav to look at new information in your site.data science course
Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
data analytics course
big data analytics malaysia
big data course
I was just browsing through the internet looking for some information and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject. Bookmarked this page, will come back for more.
PMP Certification
PMP Course
PMP Training
PMP Course in Malaysia
PMP training in Malaysia
Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
data analytics course
big data analytics malaysia
big data course
I will be interested in more similar topics. i see you got really very useful topics , i will be always checking your blog thankshttps://360digitmg.com/course/certification-program-in-data-science
You actually make it look so easy with your performance but I find this matter to be actually something which I think I would never comprehend. It seems too complicated and extremely broad for me. I'm looking forward for your next post, I’ll try to get the hang of it!
360DigiTMG PMP CERTIFICATION
Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
data analytics course
big data analytics malaysia
big data course
I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.data science course
I will truly value the essayist's decision for picking this magnificent article fitting to my matter.Here is profound depiction about the article matter which helped me more.
data analytics course
I think I have never watched such online diaries ever that has absolute things with all nuances which I need. So thoughtfully update this ever for us.
difference between analysis and analytics
I think I have never watched such online diaries ever that has absolute things with all nuances which I need. So thoughtfully update this ever for us.
difference between analysis and analytics
I looked at some very important and to maintain the length of the strength you are looking for on your website
data science course
Somebody Sometimes with visits your blog normally and prescribed it as far as I can tell to peruse too.
iot training in delhi
I figure it could be more broad in the event that you get a football sports movement 360DigiTMG
Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.Best data analytics course in Hyderabad
I see some amazingly important and kept up to length of your strength searching for in your on the site
hrdf contribution
Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.data science course in Hyderabad
Stunning! Such an astonishing and supportive post this is. I incredibly love it. It's so acceptable thus wonderful. I am simply astounded.
masters in artificial intelligence
It is the intent to provide valuable information and best practices, including an understanding of the regulatory process.
Digital Marketing Courses in Hyderabad With Placements
It is the expect to give noteworthy information and best takes a shot at, including a perception of the regulatory cycle.
ai course in malaysia
I was looking at a portion of your posts on this site and I consider this site is really enlightening! Keep setting up..
360DigiTMG big data course in malaysia
Maybe you had an extraordinary handle on the topic, yet you neglected to incorporate your perusers. Maybe you should consider this from more than one point. data scientist training
Thanks for sharing this information Freyr Energy was founded on the principles of making solar energy affordable and accessible for everyone. In order to make adoption of solar energy a reality at the grass-root level, we have identified that consumer awareness, affordability and accessibility play an integral role. With our innovative platform, SunPro+, our extensive channel-partner network and our efficient service we ensure that these three factors are addressed to make sure your venture into solar energy is hassle-free. Best solar company in Hyderabad-freyr
Thanks for sharing a valuable blog. keep sharing.
uft vs selenium
top 10 new technologies
scope of web designing
hadoop technology future
devops interview questions and answers pdf
Our MSBI Training will provide you to learn the MSBI tools and solve data mining queries easily with realty. Our MSBI Online Course also includes live sessions.
msbi online training | msbi developer course
Thanks for sharing this information KSAC is one of the most trusted multi-specialty Ayurvedic hospitals in India. For over two decades, we provides 100% Evidence based Treatments for diseases, Naturally.Best Ayurveda Hospital in Hyderabad, Bengalore, India
Thanks for sharing this information
Kiran Infertility Centre is one of the leading infertility treatment clinics in India with branches in Hyderabad, Gurgaon and Bengaluru and is perhaps the best fertility clinic in Hyderabad providing world class treatment options for Infertility such as Surrogacy, I.V.F., I.C.S.I., IUI, Egg/ Oocyte Donation, PGS/ Embryo Donation, Oocyte/Embryo Freezing.Sai Kiran Hospital & Kiran Infertility Center
Thanks for sharing this information
With more than a decade of experience in working on Cisco Meraki Solutions, we are an established partner that provides sound networking solutions. We help customers migrate their IT infrastructure to the Full Stack of Cisco Meraki Solution with ease.wireless site survey companies in india
It's an excellent article!!! Such a piece of wonderful information and I was getting more concept to your blog. Thanks for your great explanations.
AWS certification course in Chennai
It's an excellent article!!! Such a piece of wonderful information and I was getting more concept to your blog. Thanks for your great explanations.
AWS certification course in Chennai
Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
data science training in pune
Cool stuff you have and you keep overhaul every one of us
data scientist training in aurangabad
Thank you for sharing!I would also recommend to know more about solar calculator
Thank you!
This is an excellent post I seen thanks to share it. It is really what I wanted to see hope in future you will continue for sharing such a excellent post.
full stack web development course in malaysia
How to Make Yahoo My Homepage Automatically?
Check out the steps as mentioned in the guide properly and know how to make Yahoo my homepage automatically. For this launch Chrome, tap to three vertical dots in the top right corner and from menu, click to settings. On the left, click on appearance menu and then on right, you can enable the option for show home button. In the text field, enter custom web address and enter the address to set as homepage like www.yahoo.com. After scrolling, you will find the option for opening a specific page or set of pages. Click on it and choose to add a new page. Here, enter the address that you want Chrome and tap to add; you can add multiple pages in same way and start Google Chrome. lasty, exit Google Chrome and then re-launch it.
Steps to Turn Off Notifications in Outlook
Let’s check out the steps to know about how to turn off notifications in Outlook devices. With the Outlook desktop inbox, click on the gear icon in the upper right corner of the screen. Here, select view all Outlook settings. Tap to general in the far left and choose notifications. Now, you need to scroll down to the desktop notifications section. If the Outlook notifications are still on, then the slider next to send me browser notification when a message arrives. Lastly, tap to send me browser notifications when a message arrives toggle. When the slider appears too white and gray, then your Outlook notifications will be turned off.
How to Deal with Outlook Email Search Not Working?
To deal with Outlook email search not working then start by updating the Microsoft Outlook. If you want to use the Outlook application then it might be because of some glitches and issue. So, start by updating the Outlook to troubleshoot the issue of Outlook email search not working issue. To update it, launch your Outlook device. After that, click to file tab and choose an office account. Lastly, tap on office updates and then click on update now option. These are the steps that you need to follow to troubleshoot the issue smoothly without facing any error.
Why is Yahoo Mail Not Working?
To know about why is Yahoo mail not working then first step is to fix internet issues. If your browser is loading websites slower that usual then check for internet connection. To check the speed of network then you need to perform the test by using the sites like https://www.speedtest.net/. Another way is to check the performance of internet connection then you need to open different website and then see whether they are opening without any issues.
Read More:- why is Yahoo mail not syncing
The new features at Yahoo allow you to check your login activity. If someone logged in to your Yahoo account without your permission, you could check it. After checking it, you can remove the login device and change your Yahoo account password. Todelete recent login activity in Yahoo Mail account, open the Yahoo Mail app, tap the human icon from the upper-left corner of the Yahoo mail app and select the Manage accounts option. To view your recent login activity, tap on the Account Info tab and tap Recent Activity. The previous login history will open, and a sign-out button will appear in front of the login device. Now, press the sign-out text link to delete login activity in the Yahoo account from this device.
Useful blog, Keep sharing with us.
Why MSBI Over Other BI Solutions
Why MSBI
Hopefully it will certainly be favorable for all. All the points are discussed in a very simple and also easy to understand process.
url opener
online filmek
uwatchfree
UnoGeeks Offers the best Oracle Fusion Financials Training in the market today. If you want to become Expert Fusion Financials Consultant, Enrol in the
Oracle Fusion Financials Online Training offered by UnoGeeks.
Post a Comment