go backsqlschool blogs list

When dynamic SQL might be the BEST way!

by Antonios Chatzipavlis

Posted @ 2/20/2011 9:31 PM By Kimberly L. Tripp
Posted in SQL Server Magazine

Πριν μερικές μέρες είχα δημοσιεύσει ένα άρθρο “Dynamic SQL ή μήπως μπορώ και αλλιώς;”. Σήμερα διάβασα το παρακάτω άρθρο της Kimberly Trip το οποίο καλύπτει μια άλλη πτυχή την οποία δεν είχα καλύψει στο δικό μου. Και τα δύο άρθρα είναι σωστά και έρχονται να επιβεβαιώσουν ότι τίποτα μα τίποτα δεν είναι defacto στον SQL Server. Στην ουσία για μένα είναι ακόμα μια επιβεβαίωση της γνωστής απάντησης που σε αρκετές περιπτώσεις δίνω και δεν είναι άλλη από το ΕΞΑΡΤΑΤΑΙ. Τα πάντα είναι θέμα ποιότητας και ποσότητας δεδομένων.!!!

Question: This is a follow-up question to the Q&A post: Statistics, literals, recompilation… confusion! posted last week.

Would it actually be better to use OPTION(RECOMPILE), rather than dynamic SQL (DSE), as in reality, both options are likely to re-compile each time, and forcing re-compilation (with OPTION(RECOMPILE)) does not have the security implications of dynamic string execution?

Answer: Yes and no. Unfortunately, using only OPTION (RECOMPILE) as a single/uniform solution can be even more problematic. While recompilation can be helpful for some statements, it’s a waste when it’s unnecessary, it may eat up too much CPU and there are some statements that it just cannot help. In fact, if you have a system with a lot of dynamic statements and/or recompilations – you might make things worse. As a general practice I try not to use it and I tend to use it sparingly – only if I can’t come up with a better solution. For example, the optimal execution plan for some statements can vary between table scans and nonclustered index with [bookmark] lookups because of the volatility of the parameters *AND* the fact that a better index doesn’t exist. Sometimes I can create an index (usually a covering index) and make the plan more stable/consistent without requiring recompilation.

And, there are other cases where OPTION (RECOMPILE) doesn’t help the execution plan because it’s the statement itself that is the problem. I see this often. As a generalization, I see this when the supplied parameters vary and a single procedure has been created to try and satisfy all of the possible parameter combinations (some of which might not be supplied at all). Usually the WHERE clause looks something like the following:

WHERE (ColumnX = @variableX OR @variableX IS NULL)
AND (ColumnY = @variableY OR @variableY IS NULL)
AND (ColumnZ = @variableZ OR @variableZ IS NULL)

When the procedure ends up looking like this, I call it a multi-purpose procedure. The simple fact is that when the Transact-SQL statement itself is poorly written then often even recompilation can’t help. There are some cases where building the statement dynamically yields better results - especially in cases where the number of supplied parameters (vs. those which are null) changes from execution to execution.

It’s in these cases where I BUILD the exact statement (with only the non-null parameters) instead of one that includes numerous variables that are set to NULL. To execute the constructed statement I’ll use either: dynamic string execution or sp_executesql. Choosing which one to use correctly requires knowledge of your data, some plan analysis and thorough testing.

As a simple rule of thumb, use sp_executesql if the plan chosen by each specific set of parameters is consistent (you’ll only know this through thorough testing). When the distribution of the data for specific parameters causes the plan to change, then use dynamic string execution (EXEC(‘string’)) rather than sp_executesql to execute the statement. And, you’ll need to protect your code from SQL Injection. To do this effectively often requires other features such as: string protection (w/REPLACE and/or QUOTENAME()), EXECUTE AS, and login-less users.

For more information on protecting from SQL Injection, check out this post: Little Bobby Tables, SQL Injection and EXECUTE AS

For more information on multi-purpose parameters, check out this post: Stored Procedure Parameters giving you grief in a multi-purpose procedure?

This is definitely a complicated subject and it requires three key things:

  1. Knowing your data
  2. Knowing your workload
  3. Knowing how SQL Server works

It’s that last one that people aren’t always looking out for. I hope this posts helps you get closer!

Enjoy,
kt

Ημερομηνία: 23 February 2011 16:57
Αξιολόγηση: ( 1 )
Κατηγορίες:
Tags:
Share it:

Σχετικά Blog Post

Σχόλια - Comments

user-gravatar

Στις 03 Aug 2013 @ 12:24 AM o/η ΝΙΚΟΣ ΚΥΛΠΑΣΗΣ έγραψε:

Όλοι έχουμε έναν πίνακα customers και μπορούμε να δούμε ότι στον Profiler τις διαφορές στην ανάλωση των resources που χρησιμοποιεί το query χωρίς το option (recompile) είναι πάρα πολλά σε CPU, Reads και duration.Με όσους συνδυασμούς παραμέτρων και να το δοκίμασα δεν έκανε σε καμία περίπτωση Index Seek αν και τα data το ευνοούν.Με το option (recompile) δεν έχω πάρει ποτέ χειρότερο αποτέλεσμα από 300% γρηγορότερο και με μεγάλη οικονομία στα Resources.Ακόμα κει στην περίπτωση που εκτελεί Index scan τα Reads που κάνει είναι υποπολλαπλάσια αυτού που το αφήνω στην τύχη του.DECLARE @MEMBID INT = nullDECLARE @EPON VARCHAR(50) = 'ΠΑΠΑ%'DECLARE @ONOM VARCHAR(50) = ‘NI%’select MEMBID, ONOM, eponfrom NEWCAMPWHERE ( (@membid is null) or MEMBID = @MEMBID) and ( (@EPON is null) or (EPON like @EPON))and ( (@ONOM is NULL ) or (ONOM like @ONOM))OPTION ( RECOMPILE )Παράδειγμα αναζήτησης τεμπέλη χρήστη σε πίνακα με 3,5 εκατομμύρια records.Results set 4429 records. CPU Reads DurationΜε Recompile 78 457 145Χωρίς 1980 16222 633Για αναζήτηση με ID άστο καλύτεραΤώρα για το sqlexec μη μου πεις ότι ο κώδικας θα βγει πιο καθαρός (σπόντα για τους ASPάδες.)

user-gravatar

Στις 03 Aug 2013 @ 12:51 AM o/η Antonios Chatzipavlis έγραψε:

Είναι προφανές ότι θέμα parameter sniffing δες http://www.sqlschool.gr/WebCast/5/Content.aspx

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS