Report taking much time when multiple parameters are selected

Question

Report taking much time when multiple parameters are selected

eobiki10

SSCrazy

Points: 2390
More actions
December 17, 2013 at 10:18 am

#281396

Hi All,
Please how do I re-write this query as the report that consumes it is taking longer time to run when I select multiple parameters. I would want to do away with the table if there's a way.
CREATE PROCEDURE [dbo].[sp_Bank_Work_Report]
(
@StartPeriod INT,
@EndPeriod INT,
@MatterCode VARCHAR(MAX),
@Level CHAR(1),
@Profit_Center VARCHAR(MAX),
@Bank VARCHAR(MAX),
@WalkIn INT,
@BankOther INT,
@Fees VARCHAR (15)
)
AS
TRUNCATE TABLE BO_Custom.dbo.BankReviewWork
IF @Level = 'P'
BEGIN
WITH
CTE_Mins (Matter_uno, original_estimated_fee, original_contingent_fee, original_cost)
AS
(SELECT k.matter_uno
,k.rcvr_total
,k.rcvr_total_cntg
,k.cost_total, etc
)
INSERT INTO BO_Custom.dbo.BankReviewWork
SELECT p.period
,m.clnt_matt_code AS [Client Code]
,m.matter_name, etc
END
ELSE
BEGIN
WITH
CTE_Mins (Matter_uno, original_estimated_fee, original_contingent_fee, original_cost)
AS
(SELECT k.matter_uno
,k.rcvr_total
,k.rcvr_total_cntg
,k.cost_total, etc
)
INSERT INTO BO_Custom.dbo.BankReviewWork
SELECT p.period
,m.clnt_matt_code AS [Client Code]
,m.matter_name, etc
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Going to need more details than that to help here. From what you posted there is no need for the IF condition but I suspect you have probably have something in you where clause or such for that.

You might want to take a look at this article for how to post performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

At the very least an actual execution plan and even better if you include ddl and indexes for the tables.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ken.willekens SSChasing Mays Points: 637 More actions · Answer 2

As Sean already mentioned: we need more information in order to provide useful advice. As the procedure is now, the parameters aren't used anywhere so there wouldn't be a difference in execution time. Also the if condition is useless as both the if and the else-clause do the same thing. So without extra information all help we offer is like a shot in the dark.

That being said, I've got a problem with the varchar(max) declaration for Mattercode, ProfitCenter and Bank. Based on my experience for other financial companies, I am pretty sure those could/should be declared more constrictive like varchar(50) or something. I think you are seeing the slower reports when one of those parameters is being used in comparison when they are not being used.

The problem could also be caused by parameter sniffing. When the query uses an execution plan for multiple parameters that is optimized for a single parameter or different parameters. To test this you could pass the parameters to local variables at the beginning of the procedure and then use these variables in the rest of the procedure. If you see a difference in the execution plan you should read up on parameter sniffing and find a better, more permanent solution.

But as I said, all of this advise is based on assumptions. Without further information it is impossible to say what causes the difference in performance. An actual execution plan, the actual full procedure and the table definition of the used tables would go a long way in helping you solve this issue

eobiki10 SSCrazy Points: 2390 More actions · Answer 3

Thanks so much for your replies. I later solved the problem by applying an index in one of the table columns.

EO

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

Also, on another note. You should Never prefix a user sproc with "sp_" that is reserved for the system sprocs. You will gain some performance when you use a different naming convention.

Andrew SQLDBA