Procedure is getting executed by .Net application which provides store list (it would be 800 some store list in it as a string). This procedure takes from 9seconds runtime to 1mins and 50seconds.

HirenP 2015-11-06 13:28:19

Version = SQL Server 2008R2 Enterprise Edition

This procedure is getting executed constantly and is taking between 9seconds runtime to over 1min sometimes. Wanted to know which part in the procedure is the bottleneck and is there any thing we can do to prevent it?

This is the procedure parameters listed as:

ALTER PROCEDURE [dbo].[usp_Transaction_GetTransactionToProcess]
@SourceStore VARCHAR(MAX) = ", — Empty for all or comma-delimited string of stores for use in IN clause – ex. 'C0001','C0002'
@TransactionTypeID INT = NULL, — NULL for all types – should pass NULL – not used anymore
@TopX INT = 1,
@TransactionTypeIDs VARCHAR(MAX) = " — Comma-delimited string of transaction type ids to include in results – ex. 3,4,5

We see EXEC(@SQL) is the highest counters with CPU and READS, but if we run the query building from the dynamic SQL runs in less than a seconds from SSMS.

Aaron Bertrand 2015-11-06 15:46:25
I'd really consider table-valued parameters for solving this problem (and avoiding all the #temp tables, too, probably). As is, SQL Server has no clue how to optimize because there could be one item in the list or 50,000.
HirenP 2015-12-08 17:12:16
Thank you Aaron we will give that a shot and see how it goes. I will post back agian after implementing it.

Thank you,
Hiren Patel