Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get a Return Every Time. Expand / Collapse
Author
Message
Posted Monday, March 3, 2008 11:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 8, 2011 9:12 AM
Points: 393, Visits: 74
Comments posted to this topic are about the item Get a Return Every Time.

Thank You,

Charlie

Charles Eaton
Post #463584
Posted Tuesday, March 4, 2008 12:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 2:04 AM
Points: 12, Visits: 101
Hello

I am currently working on a system where Dynamic SQL is used extensively (including dynamic table names, and cursors ( ). It's a nightmare to work with and debug - to make things worse SQL doesn't usually cache dynamic queries - so they are incredibly inefficient (one procedure i'm working on takes 26 min to run and got trimmed down to 2 min by making it static as possible)

To debug use PRINT() statements (specially on @@ROWCOUNT) in your code since they are ignored by applications, but can be seen in Enterprise manager when debugging.

Robin
Post #463588
Posted Tuesday, March 4, 2008 1:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 4:15 PM
Points: 443, Visits: 52
Hi,

I prefer to use static SQL in all cases where possible and only use dynamic SQL when there are a lot of where clause criteria to the stored proc. This is due to:

- Far easier writing and debugging
- Reuseable execution plans (in most cases)
- Much tighter security

Easier writing
With static SQL you have WYSIWYG with syntax highlighting and no problem with missing spaces, spelling mistakes etc.

Reusable execution plans
When resorting to dynamic SQL remember that the query plans are not reuseable when you use the EXEC(). Using the "sp_executesql" gives you that option along with the possibility of using both input and output variables. "sp_executesql" is almost always superior in both flexibility and performance to the EXEC() alternative.

Tighter security
With static SQL you can grant access to stored procs onlyt. With dynamic SQL you will have to grant the executing user SELECT access to all the references tables. That may work for you, but it's definitely not to my taste.

To sum it up there are certainly situations where dynamic SQL is beneficial, but it is necessary to understand the implications that may tag along.

Sincerely,
Lasse
Post #463611
Posted Tuesday, March 4, 2008 3:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 16, 2009 7:18 AM
Points: 2, Visits: 9
I have found dynamic SQL statements to be faster in many situations where the exact nature (and number, when using optional parameters) of passed parameters is unknown at the time a static stored procedure is saved.

In these cases the query is often not optimised to the execution plan that is the best plan for the parameters employed and their values and a dynamic query that forces parsing and optimisation will run 2x to 3x faster.

Additional Tips for enhanced readability of code:

Use plenty of whitespace, it makes no discernable difference in execution time;

Build string literals into your dynamic queries using double quotes and then use the Replace() function to fold them to single quotes before execution;

SET @SQL =
'
SELECT LastName + ", " + FirstName

FROM MyTable

WHERE LastName LIKE "knigh%"
'

SET @SQL = Replace(@SQL, '"', '''')

EXEC(@SQL)


I use these methods in an 8000+ user classic ASP app, with SQL 2000 databases having 6 to 10 million records in the main transaction and master balance tables--with very acceptable performance.
Post #463633
Posted Tuesday, March 4, 2008 4:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 27, 2009 4:01 AM
Points: 14, Visits: 40
Robin, Lasse, you guys need to leave the 90's and learn about sp_executesql.

Charles, personally I think you're creating a nightmare for whoever comes after you. All the code being in strings means a mass of red coloured text in QA or Management studio. People might also quite easily try and guess the first param, see the a 0 returns *something* and think their query just returns no rows. There's also an added layer of gotcha's of making sure all your quotes are correctly double quoted, etc.

I'm gonna put my hands up here in that I'm no expert on execution plans or the inner workings of SQL, but I think all those IF statements could cause the execution plan to be recreated a lot and cause extra overhead for SQL to check the truth of every IF statment.

You're better off letting a proper programming language do the logic and letting SQL do what it does best, set based queries!
Post #463653
Posted Tuesday, March 4, 2008 4:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 24, 2008 6:50 AM
Points: 7, Visits: 7
I am quite surprised that someone thinks querystring based queries are faster than compiled queries.

Cliff - in your example, you will mostlikely have same performace as a compiled SP since you query is using only a single filter which may be having an index on it. So the performance may be more or less the same.

If you are getting better performance using dynamic it will most likely boil down to your statistics being out of date. DBAs are supposed to keep updating the stats or conigure auto-stats so that the compiled SP can make better decision. So the db size does not matter much if you are using only 1 index which can be easily determined. The problem more or less come when you have complicated sql which may time take to determine and best way is to have the query compiled with updated stats.


Using dynamic query is gives rise to SQL Injection - way to hack into your db and even delete your data. So you have to be very carefully and should avoid using it.
Post #463660
Posted Tuesday, March 4, 2008 5:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 24, 2008 6:50 AM
Points: 7, Visits: 7
@Charles Eaton

Your SP is fine to use if you are using it for maintenance purposes. For transactional purposes, this is typical what you should not be doing.

Post #463669
Posted Tuesday, March 4, 2008 6:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
Use the feedback you are receiving constructively. There are many solid, spot-on points that have been mentioned. In my humble opinion, it is always good practice to seek out and recognize the “gurus” that you work with as you have done in your article. They are the ones that help us learn more or give us a cool technique that can be extended or morphed.

The dynamic SQL stuff that you are doing is pretty cool – build on it more, use it for other things, and use the feedback in this forum constructively. The
sp_executesql

function will most definitely broaden your horizons. I suggest you look into it and perhaps incorporate some of the cool techniques that you wrote about.

Great article and keep up the good work.

-Mike Di Renzo
Post #463707
Posted Tuesday, March 4, 2008 6:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
Have to agree with the others...

Whilst I understand that you use this pattern as a way of debugging and inspecting what your procs do, I think good commenting, some debug prints (commented out for production) and the inherent problems with
* formatting
* ease of maintenance
* potential problems with SQL injection (although I see you've used quotename)
* performance
* ..... etc
outweigh any benefits.

Having said that, I definitely appreciate you writing the article and getting to see different approaches to how people work.

You should also be aware that if you wished to have a break in your strings you can do something like
set @sql = 'this is the first line
and this is the second line'

without needing cumbersome @crlf variables, plus signs, extra quotes, etc.

Your runmode 0 can be done without dynamic SQL as you need simply include
where @runMode <> 0

at the end of your select clauses (or use and @runMode <> 0 if you already have a where clause)

runmode 1, where you show the query text, is probably best accomplished by just examining the stored procedure with syntax highlighting, etc.
runmode 2 is obviously where you want to execute and return data - the case above for mode 0 takes care of this.



Post #463718
Posted Tuesday, March 4, 2008 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 24, 2008 6:50 AM
Points: 7, Visits: 7
@Charles

Check this query out - I am sure this will be lot faster than your dynamic one AND this allows use of variables in the WHERE clause which do not need to be passed as well. This is just to give you an idea how you can do without dynamic queries.


SELECT C.CustomerID AS CUSTOMER_ID, C.CompanyName AS COMPANY_NAME,
C.ContactName AS CO_CONTACT, C.City AS CITY, C.Country AS COUNTRY,
C.Phone AS CUST_PHONE,
O.OrderID AS ORDER_NUMBER,
SUBSTRING(E.FirstName,1,1) + char(32) + E.LastName AS ORDER_ENTERED_BY
FROM Customers C
JOIN Orders O on C.CustomerID = O.CustomerID
JOIN Employees E on O.EmployeeID = E.EmployeeID

WHERE (C.Country = ' + QUOTENAME(@Country, '''') OR @Country = '')
AND (E.LastName LIKE '%' + QUOTENAME(@Employee, '''') OR @Employee = '')

ORDER BY COMPANY_NAME


Also another pointer - dont use LIKE operator with % at the begining of the search pattern - this will cause a table/index scan and hamper query's performance. Instead use CONTAINS function that works along with Fulltext indexing i.e. MS Search Engine.


Cheers
Randeep
Post #463746
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse