当选择从sql中的多十万条记录中获取少量记录时，如何提高性能 [英] How to improve the performance when select get few records from multi lakhs records in sql

问题描述

我在表中有近300万条记录。当我使用以下查询选择记录时，它至少需要45秒。

I have nearly 3 million records in a table. when i select the records from using the below query it takes atleast 45 secs.

  select a.user_Id,b.name,a.paid_date,
 (select count(user_Id) from tbl_payment_Master 
where user_Id=a.user_Id  and paid_date=a.paid_date group by paid_date) as noofpaymnt,
(select count(user_Id) from tbl_payment_Master 
where user_Id=a.user_Id  and month(paid_Date)=MONTH(a.paid_date) and year(paid_Date)=year(a.paid_date)) as noofpaymonth 
from tbl_payment_master as a 
inner join tbl_user_registration as b on a.user_id=b.user_id 
where a.premium_amount!=0 and a.user_id=@user_id 

怎样才能提高性能。

How could be improve the performance.

推荐答案

在子查询中而不是使用a.userid为什么不尝试使用直接用户变量，这可能会改变执行计划。这会让你得到一些改进



选择a.user_Id，b.name，a.paid_date，

（

从tbl_payment_Master选择计数（user_Id）

其中user_Id = @ user_id和paid_date = a.paid_date group by paid_date）as nopaymnt，

（

从tbl_payment_Ma选择计数（user_Id） ster

其中user_Id = @ user_id和

月（paid_Date）=月（a.paid_date）

和年（paid_Date）=年（ a.paid_date）

）

asofpaymonth


来自tbl_payment_master的


内部联接tbl_user_registration为b on a.user_id = b.user_id

其中a.premium_amount！= 0和a.user_id=@user_id





-------并尝试使用CTE -----------

Hi,

In sub-query instead of using a.userid why don't try using direct user variable, that may change execution plan.try this you will get little bit of improvement

select a.user_Id,b.name,a.paid_date,
(
select count(user_Id) from tbl_payment_Master
where user_Id=@user_id and paid_date=a.paid_date group by paid_date) as noofpaymnt,
(
select count(user_Id) from tbl_payment_Master
where user_Id=@user_id and
month(paid_Date)=MONTH(a.paid_date)
and year(paid_Date)=year(a.paid_date)
)
as noofpaymonth

from tbl_payment_master as a
inner join tbl_user_registration as b on a.user_id=b.user_id
where a.premium_amount!=0 and a.user_id=@user_id


-------and also try with CTE-----------

我会优化这个子句。

I would optimise this clause.

where user_Id=a.user_Id  and month(paid_Date)=MONTH(a.paid_date) and year(paid_Date)=year(a.paid_date))

我会从 paid_Date paid_Month 包含 yyyymm c $ c>在每个表格中。

然后将条款更改为：

I would create a new field paid_Month containing yyyymm from paid_Date in each table.
Then change the clause to:

where user_Id=a.user_Id  and paid_Month=a.paid_Month

这篇关于当选择从sql中的多十万条记录中获取少量记录时，如何提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！