如何在sql server 2008中优化此查询 [英] How to optimize this query in sql server 2008

问题描述

 选择 COUNT（id） as  tablecount 来自 tbl_Panelist 其中 tbl_Panelist.IsTrash = '  0' 和 tbl_Panelist.Subscribe = '  1' 和 tbl_Panelist.Pending = '  0' 和 tbl_Panelist.UserName 不  in （ select   distinct  UserName 来自 tbl_PanelistActivity 其中​​ tbl_PanelistActivity.ActivityDate>（GETDATE（） -  180））

解决方案

1 - 不要将IsTrash之类的东西存储为字符串。如果这就是你要存储的东西（1或0），请使用一点



2 - 尝试为你要检查的每一列添加一个索引，看它是否加速了up



3 - 如果可以避免，请不要使用DISTINCT。这里不需要它，数据库不需要在检查之前过滤列表是不同的。



4 - EXISTS确实更快，但摆脱了子查询更好：

 选择 COUNT（id） as  tablecount 来自 tbl_Panelist pl 
  left   join  tbl_PanelistActivity pa  on  pa.UserName = pl.UserName 和 pa.ActivityDate> （GETDATE（） -  180）
 其中 pl.IsTrash = '  0' 和 pl.Subscribe = '  1' 和 pl.Pending = '  0' 和 pa.Username    null  

这使用左连接，如果pa值为null，则值不是存在于pa表中。



5 - 为您的用户提供数字ID，以便数据库可以对其进行过滤，而不是在任何地方重复使用用户名。如果有人更改了用户名，你会通过数据库更改它吗？数字ID将使用旧用户名保留记录，并与新用户名相关联。

最好使用不存在这种情况，因为你不会遍历 PanelistActivity 中的每一行：

 选择 COUNT（id） as  tablecount 
 来自 tbl_Panelist  where  tbl_Panelist.IsTrash = '  0' 
 和 tbl_Panelist.Subscribe = '  1' 
 和 tbl_Panelist.Pending = '  0' 
 和 不 存在 
（选择用户名
 来自 tbl_PanelistActivity 
 其中​​ tbl_PanelistActivity。 ActivityDate>（GETDATE（） -  180）
 和 tbl_PanelistActivity.UserName = tbl_Panelist.UserName）

你也应该使用索引，存储过程等来优化你的数据库。

检查这个以获得更多

如何：优化SQL查询 [ ^ ]



0）从您的查询中，字段（ IsTrash ，订阅，待定）是数字还是文本数据类型？如果值只有0,1或只有数字，则使用numeric数据类型。

1）你应该使用 UserID 列而不是 UserName 。

2）同意 GuyThiebaut 。您应该使用 EXISTS 而不是 IN 。

select COUNT(id) as tablecount from tbl_Panelist where tbl_Panelist.IsTrash='0' and  tbl_Panelist.Subscribe='1' and tbl_Panelist.Pending='0' and tbl_Panelist.UserName not in (select distinct UserName from tbl_PanelistActivity where tbl_PanelistActivity.ActivityDate>(GETDATE()-180))

解决方案

1 - don't store things like IsTrash as a string. Use a bit if that's what you're storing ( 1 or 0 )

2 - try adding an index to every column you're checking to see if it speeds things up

3 - don't use DISTINCT if you can avoid it. It's not needed here, the DB does not need to filter the list to be distinct before checking on it.

4 - EXISTS is indeed faster, but getting rid of subqueries is better:

select COUNT(id) as tablecount from tbl_Panelist pl
left join tbl_PanelistActivity pa on pa.UserName = pl.UserName and pa.ActivityDate > (GETDATE()-180)
where pl.IsTrash='0' and  pl.Subscribe='1' and pl.Pending='0' and pa.Username is null

this uses a left join, if the pa values are null, then the values did not exist in the pa table.

5 - give your users a numeric id so the DB can filter on that, instead of reusing the username everywhere. What if someone changes their username, do you change it through the DB ? A numeric id will keep the records with the old username, associated with the new one.

It's better to use a not exists in this sort of scenario as you will not be iterating through every row in PanelistActivity:

select COUNT(id) as tablecount
from tbl_Panelist where tbl_Panelist.IsTrash='0'
and  tbl_Panelist.Subscribe='1'
and tbl_Panelist.Pending='0'
and not exists
(select UserName
from tbl_PanelistActivity
where tbl_PanelistActivity.ActivityDate>(GETDATE()-180)
and tbl_PanelistActivity.UserName = tbl_Panelist.UserName)

You should optimize your database too using Index, stored procedure, etc.,.
Check this for more
How To: Optimize SQL Queries[^]

0) From your query, the fields(IsTrash, Subscribe, Pending) are numeric or text datatype? If the values only 0,1 or only numbers then use numeric datatype.
1) You should use UserID column instead of UserName in subquery condition.
2) Agree with GuyThiebaut. You should use EXISTS instead of IN.

这篇关于如何在sql server 2008中优化此查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！