StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStored procedure taking more than query
text
Body
copied!<p>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.</p> <p>What should I do?</p> <p>Thanks</p> <p>Atul yadav</p> <p>Here is my stored procedure code:</p> <pre><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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload