Stored procedure is taking 5 minutes to execute, but if query of which I have written procedure then it will take on 4 sec. I did google and find parameters sniffing for improving procedures performance so implemented that and now it still taking 3 minutes.
存储过程需要5分钟来执行，但是如果查询我已经编写了过程，那么它将需要4秒。我做了谷歌并找到参数嗅探以改进过程性能，所以实现了它，现在还需要3分钟。
What should I do?
我应该做什么?
Thanks
谢谢
Atul yadav
Atul yadav
Here is my stored procedure code:
这是我的存储过程代码:
CREATE PROCEDURE [dbo].[get_all]
@para_factor_id int,
@para_factor_client_id int,
@para_factor_customer_id int,
@para_invoice_date_from date,
@para_invoice_date_to date,
@para_invoice_amount_from decimal(18, 2),
@para_invoice_amount_to decimal(18, 2),
@para_invoice_id int,
@para_schedule_number varchar(400),
@para_invoice_number varchar(400),
@para_schedule_id int,
@para_invoice_status_id varchar(50),
@para_marked_for_printing bit,
@para_user_id int,
@para_role_id int,
@para_current_date date,
@para_approval_status_id int = 0,
@para_is_verified int = 0,
@para_load_number varchar(400) = ''
WITH EXEC AS CALLER
AS
BEGIN
DECLARE @var_invoice_order INT
= (SELECT invoice_order
FROM factor_default_settings fds
WHERE fds.factor_id = @para_factor_id);
DECLARE @v_factor_id INT;
DECLARE @v_factor_client_id INT;
DECLARE @v_factor_customer_id INT;
DECLARE @v_invoice_date_from DATETIME;
DECLARE @v_invoice_date_to DATETIME;
DECLARE @v_invoice_amount_from DECIMAL (18, 2);
DECLARE @v_invoice_amount_to DECIMAL (18, 2);
DECLARE @v_invoice_id INT;
DECLARE @v_schedule_number VARCHAR (400);
DECLARE @v_invoice_number VARCHAR (400);
DECLARE @v_schedule_id INT;
DECLARE @v_invoice_status_id VARCHAR (50);
DECLARE @v_marked_for_printing BIT;
DECLARE @v_user_id INT;
DECLARE @v_role_id INT;
DECLARE @v_current_date DATETIME;
DECLARE @v_approval_status_id INT;
DECLARE @v_is_verified INT;
DECLARE @v_load_number VARCHAR (400);
SET @v_factor_id = @para_factor_id;
SET @v_factor_client_id = @para_factor_client_id;
SET @v_factor_customer_id = @para_factor_customer_id;
SET @v_invoice_date_from = @para_invoice_date_from;
SET @v_invoice_date_to = @para_invoice_date_to;
SET @v_invoice_amount_from = @para_invoice_amount_from;
SET @v_invoice_amount_to = @para_invoice_amount_to;
SET @v_invoice_id = @para_invoice_id;
SET @v_schedule_number = @para_schedule_number;
SET @v_invoice_number = @para_invoice_number;
SET @v_schedule_id = @para_schedule_id;
SET @v_invoice_status_id = @para_invoice_status_id;
SET @v_marked_for_printing = @para_marked_for_printing;
SET @v_user_id = @para_user_id;
SET @v_role_id = @para_role_id;
SET @v_current_date = @para_current_date;
SET @v_approval_status_id = @para_approval_status_id;
SET @v_is_verified = @para_is_verified;
SET @v_load_number = @para_load_number;
SET NOCOUNT ON;
SELECT TOP 300
inv.factor_id,
inv.invoice_id,
inv.schedule_id,
inv.term_id,
inv.factor_client_id,
inv.factor_customer_id,
inv_trn.client_name,
SUBSTRING (inv_trn.client_name, 1, 10) AS client_name_10,
inv_trn.customer_name,
SUBSTRING (inv_trn.customer_name, 1, 10) AS customer_name_10,
sch.schedule_number,
sch.payment_method_id,
inv.invoice_number,
inv.po_number,
inv.invoice_date,
inv.est_days_out,
(CASE
WHEN inv.manually_flagged = 1
THEN
1
ELSE
CASE
WHEN inv.flag_days > 0
AND inv.discount_calculation_date >
CONVERT (DATE, '1900/01/01')
THEN
CASE
WHEN DATEDIFF (DAY,
inv.discount_calculation_date,
@v_current_date) > inv.flag_days
THEN
1
ELSE
0
END
ELSE
0
END
END)
AS flag_days_icon,
inv.flag_days,
inv.float_days,
DATEADD (DAY, inv.est_days_out, inv.invoice_date) AS due_date,
inv.invoice_amount,
inv.credit_limit_exceeded_by_client,
inv.po_duplication_status,
inv.advance_amount,
inv.advance_calculation_decision_id,
inv.advance_percent,
inv.advance_calculation_formula,
(CASE inv.deduct_discount_from_advance
WHEN 1 THEN 'Yes'
ELSE 'No'
END)
AS deduct_discount_from_advance,
deduct_discount_from_advance AS deduct_discount,
inv.verified,
inv.estimated_advance_date,
inv_trn.notes_count,
inv.net_term,
inv_trn.total_adjustment_amount,
inv.approval_status_id,
inv_apr_stat.approval_status,
sch.submitted,
sch.made_by_client,
sch.submitted_by_client,
sch.authorized_by_client,
inv.invoice_status_id,
inv_st.invoice_status,
inv.verified,
inv.client_customer_id,
CAST (inv_trn.attachment_count AS INT) AS attachment_count,
ROUND ( (inv.advance_amount - inv_trn.advance_discount), 2)
AS payment_amount,
(inv_trn.escrow_advance - pmt.total_escrow_amount)
AS escrow_amount,
inv_trn.dilution_type_id,
inv_trn.dilution,
inv_trn.dilution_amount,
inv_trn.dilution_term_rate_id,
inv_trn.po_deduction_type_id,
inv.po_id,
inv.fuel_advance,
inv.fuel_adv_with_adj,
inv.dispute
FROM invoices inv
JOIN dbo.fun_get_client_list_profile_management (@v_factor_id,
@v_user_id,
@v_role_id,
1) fun_cl
ON inv.factor_client_id = fun_cl.factor_client_id
JOIN factor_customers fact_cust
ON inv.factor_customer_id = fact_cust.factor_customer_id
AND ( inv.factor_customer_id = @v_factor_customer_id
OR @v_factor_customer_id = 0)
JOIN factor_clients fact_cl
ON inv.factor_client_id = fact_cl.factor_client_id
JOIN client_customers cl_cust
ON inv.client_customer_id = cl_cust.client_customer_id
JOIN invoice_transaction_values inv_trn
ON inv.invoice_id = inv_trn.invoice_id
JOIN schedules sch
ON inv.schedule_id = sch.schedule_id
JOIN invoice_total_payments pmt
ON inv.invoice_id = pmt.invoice_id
AND pmt.payment_type_id = 1
JOIN invoice_approval_status inv_apr_stat
ON inv.approval_status_id = inv_apr_stat.approval_status_id
JOIN invoice_status inv_st
ON inv.invoice_status_id = inv_st.invoice_status_id
LEFT JOIN (SELECT inv_prop.invoice_id,
inv_prop.mark_for_printing
FROM invoice_properties inv_prop
JOIN invoices inv
ON inv_prop.invoice_id = inv.invoice_id
JOIN factor_clients fact_cl
ON inv.factor_client_id =
fact_cl.factor_client_id
JOIN factor_customers fact_cust
ON inv.factor_customer_id =
fact_cust.factor_customer_id
WHERE inv.factor_id = @v_factor_id
AND ( inv.factor_client_id =
@v_factor_client_id
OR @v_factor_client_id = 0)
AND ( inv.factor_customer_id =
@v_factor_customer_id
OR @v_factor_customer_id = 0)
AND fact_cl.active = 1
AND fact_cust.active = 1) AS inv_prop
ON inv_prop.invoice_id = inv.invoice_id
LEFT JOIN invoice_custom_field_values inv_cust_fld_val
ON inv.invoice_id = inv_cust_fld_val.invoice_id
AND inv_cust_fld_val.custom_field_id = 4
WHERE inv.factor_id = @v_factor_id
AND ( inv.factor_client_id = @v_factor_client_id
OR @v_factor_client_id = 0)
AND ( inv.factor_customer_id = @v_factor_customer_id
OR @v_factor_customer_id = 0)
AND fact_cl.active = 1
AND fact_cust.active = 1
AND cl_cust.active = 1
AND inv.invoice_amount BETWEEN @v_invoice_amount_from
AND @v_invoice_amount_to
AND inv.invoice_date BETWEEN @v_invoice_date_from
AND @v_invoice_date_to
AND (inv.invoice_id = @v_invoice_id OR @v_invoice_id = 0)
AND (inv.schedule_id = @v_schedule_id OR @v_schedule_id = 0)
AND sch.schedule_number LIKE '%' + @v_schedule_number + '%'
AND inv.invoice_number LIKE '%' + @v_invoice_number + '%'
AND ( inv.invoice_status_id IN
(SELECT items
FROM dbo.fun_split_string (@v_invoice_status_id,
','))
OR @v_invoice_status_id = '')
-- AND ( ISNULL (inv_prop.mark_for_printing,
-- @v_marked_for_printing) = 0
-- OR @v_marked_for_printing = 0)
AND ( ISNULL (inv_prop.mark_for_printing, 0) =
@v_marked_for_printing
OR @v_marked_for_printing = 0)
AND ( ( @v_role_id IN (2, 6)
AND ( sch.submitted_by_client = 1
OR sch.made_by_client = 0))
OR (@v_role_id IN (3, 10)))
AND ( inv.approval_status_id = @v_approval_status_id
OR @v_approval_status_id = 0)
AND (@v_is_verified = 0 OR inv.verified = 1)
AND ( (inv_cust_fld_val.value LIKE '%' + @v_load_number + '%')
OR @v_load_number = '')
AND inv.approval_status_id IN (1,2)
ORDER BY CASE WHEN @var_invoice_order = 1 THEN inv.invoice_date END ASC,
CASE WHEN @var_invoice_order = 1 THEN inv.invoice_id END ASC,
CASE WHEN @var_invoice_order = 2 THEN inv.invoice_date END DESC,
CASE WHEN @var_invoice_order = 2 THEN inv.invoice_id END DESC
END
2 个解决方案
#1
0
There are few things you need to change in the stored proc first.
在存储的proc中，您需要首先更改的内容很少。
1) implement indexes (if not there).
1)实现索引(如果没有)。
2) Do not use function in join "dbo.fun_get_client_list_profile_management" Instead of that you need to create a temp table/table variable and populate that with dbo.fun_get_client_list_profile_management and then join the temp table/table variable with the actual query. Because when you use function instead of table the function is called for each row results.
2)不要在join“dbo”中使用函数。fun_get_client_list_profile_management“而不是创建一个临时表/表变量，并用dbo填充它。fun_get_client_list_profile_management，然后将temp表/表变量与实际查询连接。因为当你使用函数而不是表时，每一行的结果都会调用函数。
3) Same thing you need to do for "inv_prop". Populate a temp table/ table variable with that subquery and then use that table in actual query.
3)“inv_prop”也需要执行。用该子查询填充临时表/表变量，然后在实际查询中使用该表。
4) Same thing goes for
4)同理
inv.invoice_status_id IN
(SELECT items
FROM dbo.fun_split_string (@v_invoice_status_id,
','))
OR @v_invoice_status_id = ''
Change it to table variables and instead of "IN" use join
将它更改为表变量，而不是“IN”使用join
Change those things and let us know the improvement.
改变这些事情，让我们知道改进之处。
#2
0
It could be that you have a bad plan in the cache for that stored procedure. Recompile it and see if the performance is comparable to the query.
它可能是您在缓存中有一个错误的存储过程计划。重新编译它，看看性能是否与查询相匹配。
EXEC sp_recompile N'dbo.get_all';
#1
0
There are few things you need to change in the stored proc first.
在存储的proc中，您需要首先更改的内容很少。
1) implement indexes (if not there).
1)实现索引(如果没有)。
2) Do not use function in join "dbo.fun_get_client_list_profile_management" Instead of that you need to create a temp table/table variable and populate that with dbo.fun_get_client_list_profile_management and then join the temp table/table variable with the actual query. Because when you use function instead of table the function is called for each row results.
2)不要在join“dbo”中使用函数。fun_get_client_list_profile_management“而不是创建一个临时表/表变量，并用dbo填充它。fun_get_client_list_profile_management，然后将temp表/表变量与实际查询连接。因为当你使用函数而不是表时，每一行的结果都会调用函数。
3) Same thing you need to do for "inv_prop". Populate a temp table/ table variable with that subquery and then use that table in actual query.
3)“inv_prop”也需要执行。用该子查询填充临时表/表变量，然后在实际查询中使用该表。
4) Same thing goes for
4)同理
inv.invoice_status_id IN
(SELECT items
FROM dbo.fun_split_string (@v_invoice_status_id,
','))
OR @v_invoice_status_id = ''
Change it to table variables and instead of "IN" use join
将它更改为表变量，而不是“IN”使用join
Change those things and let us know the improvement.
改变这些事情，让我们知道改进之处。
#2
0
It could be that you have a bad plan in the cache for that stored procedure. Recompile it and see if the performance is comparable to the query.
它可能是您在缓存中有一个错误的存储过程计划。重新编译它，看看性能是否与查询相匹配。
EXEC sp_recompile N'dbo.get_all';