Is a dynamic SQL stored procedure bad for many records?

Question

I have a table with almost 800,000 records, and I'm currently using dynamic sql to generate a query on the back panel. The front part is a search page that accepts about 20 parameters and depending on whether a parameter has been selected, it adds “AND ...” to the base query. I am wondering if dynamic sql is the right way (not like because it is slow). I am only considering creating a denormalized table with all my data. Is this a good idea or should I just assemble the query and not build it in parts using dynamic sql. Lastly, is there a way to speed up dynamic sql?

Answer 1

It is more likely that your indexing (or lack thereof) is slower than dynamic SQL.

What does the implementation plan look like? Is the same request slow when executed in SSMS? How about when it is stored in the procedure?

If your table is an unindexed heap, it will not work well as the number of records increases - this is independent of the query, and the dynamic query may actually work better because the nature of the table changes, since a dynamic query is more likely to re-evaluate the query plan if it is not is in cache. This is usually not a problem (and I would not classify it as an advantage of dynamic queries), with the exception of the early stages of the system, when the SPs were not recompiled, but the statistics and query plans are outdated, but the data volume has just changed dramatically.

Not yet static. I have a dynamic query, but it does not provide any optimizations. Should I run it with a static query and give suggestions, applying them to a dynamic query? - Xaisoft (41 minutes ago)

Yes, dynamic query (EXEC (@sql)) probably will not be parsed unless you parsed the workload file. - Cade Roux (33 minutes ago)

When you have a search query on several related tables, the columns with indexes should be the search columns as well as the primary key / foreign key columns, but this depends on the power of the various tables. This analyzer should show this. - Cade Roux (22 minutes ago)

Answer 2

I would like to note that if you use this style of additional parameters:

AND (@EarliestDate is Null OR PublishedDate < @EarliestDate) 

The query optimizer will not know if this parameter is present or not when it creates a query plan. I have seen cases where the optimizer makes bad choices in these cases. The best solution is to build sql that uses only the parameters you need. In these cases, the optimizer will make the most efficient execution plan. Be sure to use parameterized queries so that they are reused in the plan cache.

Answer 3

As in the previous answer, check your indexes and plan.

The question is whether you are using a stored procedure. This is not obvious from how you formulated it. A stored procedure creates a query plan at startup and saves that plan until it is recompiled. With an SQL variable, you may encounter a poor query plan. You can do a few things:

1) Add WITH RECOMPILE to the SP definition, which will cause a new plan to be created each time it runs. This includes some overhead that may be acceptable.

2) Use separate SPs, depending on the options provided. This will improve query plan caching.

3) Use the SQL client generated by SQL. This will create a query plan every time. If you use parameterized queries, this may allow you to use cached query plans.

Answer 4

The only difference between “dynamic” and “static” SQL is the parsing / optimization phase. Once they are completed, the request will be executed identically.

For simple queries, this phase of the parsing plus network traffic is a significant percentage of the total transaction time, so it is recommended that you try and reduce these times.

But for large complex queries, this processing is small compared to the actual path chosen by the optimizer.

I would concentrate on optimizing the request itself, including, possibly, denormalizing if you think this is appropriate, although I would not do it the first time I walk around myself.

Sometimes denormalization can be performed at runtime in an application, for example, using cached lookup tables, rather than maintaining this database.

Answer 5

Not a fan of dynamic Sql, but if you are stuck with it, you should probably read this article: http://www.sommarskog.se/dynamic_sql.html It really deeply understands the best ways to use dynamic SQL, and its use can create .

As others have said, indexing is the most likely culprit. When indexing, one thing that people often forget to do is put the index in the FK fields. Since PK automatically creates an index, many also assume FK. Unfortunately, creating an FK does not create an index. So make sure all the fields you join are indexed.

There may be more efficient ways to create dynamic SQL, but without seeing the code, it's hard to say. I would at least look to see if it uses subqueries and replaces views instead. Also, any dynamic SQl that uses a cursor should be slow.

Answer 6

If the parameters are optional, the trick that is often used is to create such a procedure:

 CREATE PROCEDURE GetArticlesByAuthor ( @AuthorId int, @EarliestDate datetime = Null ) AS SELECT * --not in production code! FROM Articles WHERE AuthorId = @AuthorId AND (@EarliestDate is Null OR PublishedDate < @EarliestDate) 

Answer 7

There are some good query examples with additional search criteria here: How do I create a stored procedure that will not necessarily look for columns?

Answer 8

As already noted, if you are making a massive query, indexes are the first bottleneck to look at. Make sure that indexed columns are more likely to be indexed. Also, make sure your query checks all indexed parameters before checking non-indexed parameters. This ensures that the results are first filtered using indexes and then a slow linear search is performed only if necessary. Therefore, if col2 is indexed, but col1 is not, it should look like this:

 WHERE col2 = @col2 AND col1 = @col1 

You may be tempted to go overboard with indexes, but keep in mind that too many indexes can cause slow recording and massive disk usage, so don't go too crazy.

I avoid dynamic queries if I can for two reasons. Firstly, they do not save the query plan, so the statement is compiled every time. Another is that it is difficult for them to manipulate, test and troubleshoot. (They just look ugly).

I like Dave Kemp's answer above.

Answer 9

I had some success (in a limited number of instances) with the following logic:

 CREATE PROCEDURE GetArticlesByAuthor ( @AuthorId int, @EarliestDate datetime = Null ) AS SELECT SomeColumn FROM Articles WHERE AuthorId = @AuthorId AND @EarliestDate is Null UNION SELECT SomeColumn FROM Articles WHERE AuthorId = @AuthorId AND PublishedDate < @EarliestDate 

Answer 10

If you are trying to optimize a range below 1s, it may be important to estimate approximately how long it will take to parse and compile dynamic sql relative to the actual query execution time:

  SET STATISTICS TIME ON; 

and then execute the dynamic SQL string “statically” and check the Messages tab. I was surprised by these results for a 10-line dynamic SQL query that returns two rows from a 1M row table:

  SQL Server parse and compile time: CPU time = 199 ms, elapsed time = 199 ms. (2 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 4 ms. 

Index optimization will doubtfully move the 199 ms barrier (except perhaps due to some analysis / optimization included during compilation time).

However, if dynamic SQL uses parameters or repeats, then the compilation results can be cached according to: See "Caching Query Plans" , which will eliminate compilation time. It would be interesting to know how long cache entries are kept, size, sharing between sessions, etc.