Click here to Skip to main content
15,063,287 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 11-Jun-14 2:02am
v2
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.

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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900