Is a dynamic sql stored procedure a bad thing for lots of records?

Question

Hello,

I have a table with almost 800,000 records and I am currently using dynamic sql to generate the query on the back end. The front end is a search page which takes about 20 parameters and depending on if a parameter was chosen, it adds an " AND ..." to the base query. I'm curious as to if dynamic sql is the right way to go ( doesn't seem like it because it runs slow). I am contemplating on just creating a denormalized table with all my data. Is this a good idea or should I just build the query all together instead of building it piece by piece using the dynamic sql. Last thing, is there a way to speed up dynamic sql?

Answer 1

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

What does the execution plan look like? Is the same query slow when executed in SSMS? What about when it's in a stored procedure?

If your table is an unindexed heap, it will perform poorly as the number of records grows - this is regardless of the query, and a dynamic query can actually perform better as the table nature changes because a dynamic query is more likely to have its query plan re-evaluated when it's not in the cache. This is not normally an issue (and I would not classify it as a design advantage of dynamic queries) except in the early stages of a system when SPs have not been recompiled, but statistics and query plans are out of date, but the volume of data has just drastically changed.

Not the static one yet. I have with the dynamic query, but it does not give any optimizations. If I ran it with the static query and it gave suggestions, would applying them affect the dynamic query? – Xaisoft (41 mins ago)

Yes, the dynamic query (EXEC (@sql)) is probably not going to be analyzed unless you analyzed a workload file. – Cade Roux (33 mins ago)

When you have a search query across multiple tables that are joined, the columns with indexes need to be the search columns as well as the primary key/foreign key columns - but it depends on the cardinality of the various tables. The tuning analyzer should show this. – Cade Roux (22 mins ago)

Answer 2

If the parameters are optional, a trick that's often used is to create a procedure like this:

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 3

The only difference between "dynamic" and "static" SQL is the parsing/optimization phase. Once those are done, the query will run identically.

For simple queries, this parsing phase plus the network traffic turns out to be a significant percentage of the total transaction time, so it's good practice to try and reduce these times.

But for large, complicated queries, this processing is overall insignificant compared to the actual path chosen by the optimizer.

I would focus on optimizing the query itself, including perhaps denormalization if you feel that it's appropriate, though I wouldn't do that on a first go around myself.

Sometimes the denormalization can be done at "run time" in the application using cached lookup tables, for example, rather than maintaining this o the database.

Answer 4

As previous answer, check your indexes and plan.

The question is whether you are using a stored procedure. It's not obvious from the way you worded it. A stored procedure creates a query plan when run, and keeps that plan until recompiled. With varying SQL, you may be stuck with a bad query plan. You could do several things:

1) Add WITH RECOMPILE to the SP definition, which will cause a new plan to be generated with every execution. This includes some overhead, which may be acceptable.

2) Use separate SP's, depending on the parameters provided. This will allow better query plan caching

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

Answer 5

There are some good examples of queries with optional search criteria here: http://stackoverflow.com/questions/205526/how-do-i-create-a-stored-procedure-that-will-optionally-search-columns

Answer 6

As noted, if you are doing a massive query, Indexes are the first bottleneck to look at. Make sure that heavily queried columns are indexed. Also, make sure that your query checks all indexed parameters before it checks un-indexed parameters. This makes sure that the results are filtered down using indexes first and then does the slow linear search only if it has to. So if col2 is indexed but col1 is not, it should look as follows:

WHERE col2 = @col2 AND col1 = @col1

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

I avoid dynamic queries if I can for two reasons. One, they do not save the query plan, so the statement gets compiled each time. The other is that they are hard to manipulate, test, and troubleshoot. (They just look ugly).

I like Dave Kemp's answer above.

Answer 7

I'd just like to point out that if you use this style of optional parameters:

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

The query optimizer will have no idea whether the parameter is there or not when it produces the query plan. I have seen cases where the optimizer makes bad choices in these cases. A better solution is to build the sql that uses only the parameters you need. The optimizer will make the most efficient execution plan in these cases. Be sure to use parameterized queries so that they are reusable in the plan cache.

Answer 8

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 He really goes in depth on the best ways to use dynamic SQL and the isues using it can create.

As others have said, indexing is the most likely culprit. In indexing, one thing people often forget to do is put an index on the FK fields. Since a PK creates an index automatically, many assume an FK will as well. Unfortunately creating an FK does nto create an index. So make sure that any fields you join on are indexed.

There may be better ways to create your dynamic SQL but without seeing the code it is hard to say. I would at least look to see if it is using subqueries and replace them with derived table joins instead. Also any dynamic SQl that uses a cursor is bound to be slow.

Answer 9

--I've 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