How to improve SQL Server query performance in a stored procedure?

Question

I have a large table and would like to know if it's possible to improve performance for queries.

Running a simple query takes several minutes. See the execution plan below: https://files.fm/down.php?cf&i=pxgp25tr&n=ep.sqlplan

I'm using Microsoft SQL Server 2016, and I have this stored procedure:

How can I improve the performance of the procedure above?

This is my execution plan:

https://www.brentozar.com/pastetheplan/?id=r1m43JclQ

Is there a way to improve the select performance?

I don't have time to make this a complete answer right now, but make your table variables temp tables instead. — alroc
– alroc, Commented Jun 9, 2018 at 12:05
Is the large table you mentioned DbsTrade? How many rows? You might experiment with columnstore and perhaps a tabular model to maximize performance of large aggregations.. — Dan Guzman
– Dan Guzman, Commented Jun 9, 2018 at 12:20
Thanks for your help, I am weak in sql can you give me some hints... please. — Elham Azadfar
– Elham Azadfar, Commented Jun 9, 2018 at 13:18
Please see paste the plan for a better way to include an execution plan in your question. — HABO
– HABO, Commented Jun 9, 2018 at 13:44

Lukasz Szozda · Accepted Answer · 2018-06-09 15:08:06Z

3

I would add OPTION(RECOMPILE):

SET @SQL = @Pre + @Select + @SQL + @GroupBy
=>
SET @SQL = @Pre + @Select + @SQL + @GroupBy + ' OPTION(RECOMPILE)';

I would use this clause to generate execution plan and/or cardinality estimations for dynamic SQL every time(it is some kind of report so I assume that the overhead shouldn't be too high). More info: OPTION (RECOMPILE) is Always Faster; Why?

It's a good practice to end every statement with semicolon.

edited Jun 9, 2018 at 15:08

answered Jun 9, 2018 at 12:01

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Smith Over a year ago

You should explain why you think this will help. (e.g. improve cardinality estimates of the table variables) rather than leaving it as a mysterious incantation.

Elham Azadfar Over a year ago

I added Option(Recompile) but it steel slow.

Collectives™ on Stack Overflow

How to improve SQL Server query performance in a stored procedure?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related