Sql Query性能问题（左外连接的最佳替代方案） [英] Sql Query performance issues (Best alternative of left outer join)

问题描述

我正在使用一个查询，其中我从数据库中获取所有列数据，我使用左外连接（我知道内连接可以提高我的性能，但这个内连接不符合我的要求，因为我必须拉所有数据，如果它只存在于一个表中）。我使用的总表是4和它们之间的左外连接，我在查询列上使用标量和表值函数，当我执行它时1000条记录需要超过5分钟，但它在最后执行，当我对超过50K的记录运行相同的查询时，查询返回没有结果继续执行没有响应。请告诉我如何摆脱这种性能问题。

  SELECT  * 
  FROM  ClientDetail cd 
  LEFT   OUTER  加入 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 

是查询

解决方案

请确保您已经为表添加了键，即主外键，在where子句中的列上创建索引。

你需要做的事情：

1）只选择必要的栏目

2）根据栏目你可以创建一些索引

3）在管理工作室使用执行计划可能是一些索引建议你遵循它（请记住选择必要的列）

它可能不是很好的解决方案，但在你的情况下这是找到适当的索引的最佳方法之一

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.

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

解决方案

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

这篇关于Sql Query性能问题（左外连接的最佳替代方案）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！