Regarding Multiple join query - taking much time

Question

1.00/5 (1 vote)

See more:

, +

Hello All,

I am trying to fetch the record from multiple table by join those table, but It is taking 1.20 min for 22 Lakhs record. Facing performance issue.......

Below is the my query...

C#

SELECT COL1,COL2..........,COLX

FROM  TABLE1 T1 INNER JOIN
TABLE2 T2 ON T1.MID = T2.MID INNER JOIN
TABLE3 T3  ON T2.UserID = T3.UserID INNER JOIN
TABLE4 T4 ON T1.CTID = T4.CTID INNER JOIN
TABLE5 T5 ON T1.MTID = T5.MTID INNER JOIN
TABLE6 T6 ON T1.MID = T6.MID INNER JOIN
TABLE7 T7 ON T1.MID = T7.MID INNER JOIN
TABLE3 AS T8  ON T7.UserID = T8.UserID LEFT JOIN
TABLE9 T9 ON T1.MID = T9.MID

Can anybody having good idea for above query so that I can achieve good performance.

Your help is highly appreciated.

Thanks in advance.

Posted 20-Jun-15 19:58pm

S.A.K

Updated 21-Jun-15 6:04am

Suvendu Shekhar Giri

v2

Add a Solution

Comments

Michael_Davies 21-Jun-15 2:23am

Would be good to see entire SQL, just looking at it there seems no need for the joins at all as everything comes from TABLE1 and there are no WHERE clauses involving tables 2 to 9 so why reference them at all.

Also a look at the table schema's, presume the ID fields are all indexed?

S.A.K 22-Jun-15 3:11am

Thanks Michael_Davies, I have created Non-Cluster index for MID and UserId including some other columns, now it is taking almost 45 sec to fetch all records.
Is there any other things to increase performance?

Thanks in advance.

Jörgen Andersson 22-Jun-15 2:24am

Am I getting this right? You're trying to fetch 2.2 million records and wonder why it's taking some time?

What are you going to do with all these records?
If you are going to aggregate or filter them it's better if it's done already in the database.

S.A.K 22-Jun-15 3:16am

@Jörgen Andersson, Actually I am taking it from one view, which I am using it in one stored procedure. While accessing it in sp it is taking much more time.

Thanks.

Jörgen Andersson 22-Jun-15 4:07am

So if I understand you correctly, the query you have posted is actually a view, that you access in a stored procedure.

A good rule of thumb for performance is to NEVER use joins in a view.

Jitesh Hirani 6-Jul-15 6:14am

If your tables are always under Read/write operation and if you can proceed with past data(i.e. using NOLOCK) I would prefer to use

SELECT COL1,COL2..........,COLX

FROM TABLE1 T1 with (NOLOCK) INNER JOIN
TABLE2 T2 with (NOLOCK) ON T1.MID = T2.MID INNER JOIN
TABLE3 T3 with (NOLOCK) ON T2.UserID = T3.UserID INNER JOIN
TABLE4 T4 with (NOLOCK) ON T1.CTID = T4.CTID INNER JOIN
TABLE5 T5 with (NOLOCK) ON T1.MTID = T5.MTID INNER JOIN
TABLE6 T6 with (NOLOCK) ON T1.MID = T6.MID INNER JOIN
TABLE7 T7 with (NOLOCK) ON T1.MID = T7.MID INNER JOIN
TABLE3 AS T8 with (NOLOCK) ON T7.UserID = T8.UserID LEFT JOIN
TABLE9 T9 with (NOLOCK) ON T1.MID = T9.MID

This will read uncommitted data, but will not wait if you are performing read /write operation for other connection.

S.A.K 20-Jul-15 2:45am

Hi @Member 9800187 ,

Thanks for your response....

I am facing same issue with NOLOCK....

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)