Sql Query performance issues (Best alternative of left outer join)

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I am using a query in which i am getting all the columns data from the database, I am using left outer join (I know inner join can increase my performance but this inner join does not meets my requirements because i have to pull all the data if either it exists only in a single table).Total tables i am using are 4 and left outer join in between them, And i am using a scalar and table valued functions on the columns of query , when i executes it on 1000 records it takes more than 5 minutes but it executes at the end, when i run the same query on records having more than 50K the query returns no result keep on executing with no response.Kindly tell me how can i get rid of this performance issue.

SQL

SELECT *
FROM   ClientDetail cd
       LEFT OUTER JOIN Client_HealthDetail chd
            ON  cd.ClientId = chd.ClientId
       LEFT OUTER JOIN Client_AddressDetail cad
            ON  cd.ClientId = cad.ClientId
       LEFT OUTER JOIN Client_ContactDetail ccd
            ON  cd.ClientId = ccd.ClientId

This is the query

Posted 11-Jun-14 2:01am

sajid zafar_Iqbal

Updated 11-Jun-14 2:02am

Nirav Prabtani

v2

Add a Solution

Comments

norbitrial 11-Jun-14 8:04am

Have you well considered indexes on your tables? That could help a little.

Magic Wonder 11-Jun-14 8:09am

First of all identify the fields which are required to display in output. Then use that table which is having more records or whose whole data need to display as base table and then use left join with that table which contains 2nd more records to be displayed and so on.

2 solutions

Add a Solution

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)

PrakashCs.net · Answer 1 · 2014-06-11T03:16:00

Solution 2

Please make sure that you have added keys i.e. primary foreign key to your tables,Create index on columns which is in where clause.

Posted 11-Jun-14 3:16am

PrakashCs.net

Thava Rajan · Answer 2 · 2014-06-11T02:41:00

Things you need to do:
1) Select only necessary columns
2) Based on the columns you can create some index
3) use the execution plan in management studio, There might be some index suggestion to you follow it(Keep in mind select the necessary columns)
It might not good solution but in your situation this is one of the best way to find the appropriate index