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, Jun 09 '18 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, Jun 09 '18 at 12:20
Thanks for your help, I am weak in sql can you give me some hints... please. — Elham Azadfar, Jun 09 '18 at 13:18
Please see [paste the plan](https://www.brentozar.com/pastetheplan/) for a better way to include an execution plan in your question. — HABO, Jun 09 '18 at 13:44
Thanks for your help https://www.brentozar.com/pastetheplan/?id=r1m43JclQ — Elham Azadfar, Jun 10 '18 at 00:10

Lukasz Szozda · Answer 1 · 2018-06-09T15:08:06.993

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 09 '18 at 15:08

answered Jun 09 '18 at 12:01

Lukasz Szozda

162,964
23
234
275

6

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. – Martin Smith Jun 09 '18 at 14:15
1

I added Option(Recompile) but it steel slow. – Elham Azadfar Jun 11 '18 at 06:23

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

1 Answers1