November 2, 2016 at 3:24 am
Hi,
I was just trying to find out all the modules (procedures, functions, triggers) that uses functions in the WHERE clause
this is what I have come up with. However, it does not limit the number of records (trying it out on AdventureWorks2008 DB)
SELECT * FROM sys.sql_modules AS sm JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition like '%where%)%[=<>]%' --to find a function being used in left hand side operand
OR sm.definition like '%where%@%[=<>]%' --to find a variable being used in left hand side operand
/*
The intention in this query is to find something like
WHERE UPPER(column) = @Variable
WHERE YEAR(column) =@Var_Year
WHERE LTRIM(column)= 'abcd'
sm.definition like '%where%)%[=<>]%'
function will be within parenthesis so using ")" as a separator between WHERE keyword and operator
I know I'm ignoring "IN" predicate here hoping there might not be anything as such
the logic works like
anything(%)
where (keyword)
anything(%)
) (separator between WHERE keyword and operator)
anything(% - due to possible multiple spaces)****This is where this code fails to limit to fewer records
[=<>] (operator that separates left and right hand operands
anything(% - rest of the code module)
*/
Any help would be appreciated.
Thanks,
Santhosh
Thanks,
Santhosh
November 2, 2016 at 5:33 am
Just curious, what about the right hand side? You can write a query that puts a function against a column on either side of the operand and it will behave exactly in the same way (depending on the operand, of course).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 2, 2016 at 5:50 am
Grant Fritchey (11/2/2016)
Just curious, what about the right hand side? You can write a query that puts a function against a column on either side of the operand and it will behave exactly in the same way (depending on the operand, of course).
In where clause, I am assuming the left hand side operand are Table columns and right hand operands are search arguments. (Hopefully)
The purpose is to identify these scenarios and mofify the queries to make use of index.
Thanks,
Santhosh
Thanks,
Santhosh
November 2, 2016 at 6:04 am
OK, but just using the equals (=) as an example, you can put the column on either side of that and a variable, parameter, or hard coded value on the other and everything will work the same way. Further, when dealing with JOIN criteria, it's likely to be a column on both sides of the operator and either one could have a function against it. We can't make the assumption that only the left side will have functions. It could be on either.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 2, 2016 at 6:10 am
Grant Fritchey (11/2/2016)
OK, but just using the equals (=) as an example, you can put the column on either side of that and a variable, parameter, or hard coded value on the other and everything will work the same way. Further, when dealing with JOIN criteria, it's likely to be a column on both sides of the operator and either one could have a function against it. We can't make the assumption that only the left side will have functions. It could be on either.
The JOINs, Yes....need to find them too...
Thanks,
Santhosh
Thanks,
Santhosh
November 2, 2016 at 7:33 am
This kind of query is going to return so many false positives I would think it would be more efficient to simply make a list of every procedure, view, function etc and put eyeballs on each of them. Make a spreadsheet and get cracking.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 2, 2016 at 8:17 am
Not only will you get a huge number of false positives, as Sean stated, but it's mostly going to be useless even if you could make it work perfectly because it's sometimes NOT a problem. For example, if you have an ISNULL(SomeStatusColumn,"A")<>"A", it might not actually be causing a problem worth looking into because there might be other columns in the WHERE the provide a resolute-enough bit of SARGability so that fixing the indiscretion won't actually make any substantial difference.
It would be far better to concentrate on the problems that ARE actually performance problems. For example, in the Object Explorer window of SSMS, right click on the instance name, select {Repoorts} and follow your nose to the performance-related reports. With the understanding that what returns there is based on the last time the code recompiled (or the SQL Service restarted, either of which could be minutes or days), there will be a strong indication as to what your real performance problems are. THOSE are what need to be fixed.... not necessarily something that's a "bad practice".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2016 at 9:14 am
Sean Lange (11/2/2016)
This kind of query is going to return so many false positives I would think it would be more efficient to simply make a list of every procedure, view, function etc and put eyeballs on each of them. Make a spreadsheet and get cracking.
Yes,I agree with false positives...
My intention was to at least reduce the number of procedures to be looked up. There are 2000 odd procedures, even if some 500-800 are eliminated that would be a bit effort saver(:?)
From Jeff's comments:
Not necessarily "bad practice" makes a lot of sense at this time.
Thanks,
Santhosh
Thanks,
Santhosh
November 2, 2016 at 9:53 am
To help (but not prevent) false positives, you can start by joining SYSTEM_SQL_MODULES.DEFINITION on a set of actual function names rather than attempting to parse the WHERE clause for what looks like a function reference.
select object_name(m.object_id) as module_name
, f.name as function_name
, m.definition as module_definition
from sys.system_sql_modules as m
cross join sys.sysobjects as f
where f.xtype in ('AF','FN','FS','FT','IF','TF')
and m.definition like '%' + f.name + '%';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 3, 2016 at 11:47 am
You could try limiting the pattern matching to only the text between "[whitespace-char]WHERE[whitespace-char]" and the next occurrence of "GROUP BY" or "SELECT". Of course that's also not perfect, but it should help cut down on false positives.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 3, 2016 at 2:14 pm
ScottPletcher (11/3/2016)
You could try limiting the pattern matching to only the text between "[whitespace-char]WHERE[whitespace-char]" and the next occurrence of "GROUP BY" or "SELECT". Of course that's also not perfect, but it should help cut down on false positives.
It will also eliminate true positives. For example.
SELECT *
FROM TableA a
WHERE NOT EXISTS ( -- start of range 1
SELECT 1 --- end of range 1
FROM TableB b
WHERE a.ID = b.ID -- start of range 2
GROUP BY b.somefield -- end of range 2
HAVING MAX(somevalue) > someconstant
)
AND dbo.somefunction(a.anotherfield) = 0 -- function in WHERE clause
I'd rather err on the side of including false positives than throwing out true positives.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 3, 2016 at 3:55 pm
drew.allen (11/3/2016)
ScottPletcher (11/3/2016)
You could try limiting the pattern matching to only the text between "[whitespace-char]WHERE[whitespace-char]" and the next occurrence of "GROUP BY" or "SELECT". Of course that's also not perfect, but it should help cut down on false positives.It will also eliminate true positives. For example.
SELECT *
FROM TableA a
WHERE NOT EXISTS ( -- start of range 1
SELECT 1 --- end of range 1
FROM TableB b
WHERE a.ID = b.ID -- start of range 2
GROUP BY b.somefield -- end of range 2
HAVING MAX(somevalue) > someconstant
)
AND dbo.somefunction(a.anotherfield) = 0 -- function in WHERE clause
I'd rather err on the side of including false positives than throwing out true positives.
Drew
I'm assuming one would concatenate a " SELECT" at the end to force a recognition of the final WHERE clause(s).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 3, 2016 at 4:35 pm
ScottPletcher (11/3/2016)
drew.allen (11/3/2016)
ScottPletcher (11/3/2016)
You could try limiting the pattern matching to only the text between "[whitespace-char]WHERE[whitespace-char]" and the next occurrence of "GROUP BY" or "SELECT". Of course that's also not perfect, but it should help cut down on false positives.It will also eliminate true positives. For example.
SELECT *
FROM TableA a
WHERE NOT EXISTS ( -- start of range 1
SELECT 1 --- end of range 1
FROM TableB b
WHERE a.ID = b.ID -- start of range 2
GROUP BY b.somefield -- end of range 2
HAVING MAX(somevalue) > someconstant
)
AND dbo.somefunction(a.anotherfield) = 0 -- function in WHERE clause
I'd rather err on the side of including false positives than throwing out true positives.
Drew
I'm assuming one would concatenate a " SELECT" at the end to force a recognition of the final WHERE clause(s).
That was just one example. What about when the function is used in a CTE and then the result of that expression is used in a later query WHERE clause? How are you going to include that?
The point I'm trying to make is that T-SQL is a context-free language, and the native tools we have in SQL cannot even adequately describe a regular expression, so they're certainly inadequate to describe a context-free language.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 3, 2016 at 6:41 pm
KBSK (11/2/2016)
Grant Fritchey (11/2/2016)
Just curious, what about the right hand side? You can write a query that puts a function against a column on either side of the operand and it will behave exactly in the same way (depending on the operand, of course).In where clause, I am assuming the left hand side operand are Table columns and right hand operands are search arguments. (Hopefully)
The purpose is to identify these scenarios and mofify the queries to make use of index.
Thanks,
Santhosh
Don't waste your time.
With this approach you'll be digging through heaps of false positives and miss the real ones.
Did you think about views/derived tables/CTE's using DISTINCT keyword?
What about pivoted tables joined to some other tables?
You don't always need a function in WHERE clause to screw things up.
Better find queries with "fat" execution plans (Jeff pointed you into the right direction) and try to find out what is causing those "fat lines" on the execution plans.
_____________
Code for TallyGenerator
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply