Is a dynamic sql stored procedure a bad thing for

问题:

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?

回答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)

回答2:

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.

回答3:

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.

回答4:

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.

回答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 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.

回答6:

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)

回答7:

There are some good examples of queries with optional search criteria here: How do I create a stored procedure that will optionally search columns?

回答8:

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.

回答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

回答10:

If you are trying to optimize to below the 1s range, it may be important to gauge approximately how long it takes to parse and compile the 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 199ms barrier much (except perhaps due to some analyzation/optimization included within the compile time).

However if the dynamic SQL uses parameters or is repeating than the compile results may be cached according to: See Caching Query Plans which would eliminate the compile time. Would be interesting to know how long cache entries live, size, shared between sessions, etc.