A Dynamic T-SQL WTF Puzzle
-
I had to write this bit of evilness a while back to work around a performance problem with a vendor's SQL database -- specifically views that control security. Can you find the original WTFery by looking at my solution? (Hint: I have to join parts of view *names* against values in a column in a table, and use that data to rewrite the views)
Names have been changed to conceal the guilty:
if exists ( select * FROM syscomments sc1
inner JOIN sysobjects so ON so.id = sc1.id
WHERE so.type ='V'
and so.name like 'SV\_%' escape '\'
and sc1.text like '% IN (%)' )
begin
select 'alter view dbo.' + so.name +
' AS select e.* from entity e, securityTable l ' +
' where e.id = l.entityID ' +
' AND l.entityListID = ' +
right(so.name, len(so.name)-charindex('_', so.name))
as alterViewCommand
into #viewCorrections
FROM syscomments sc1
inner JOIN sysobjects so ON so.id = sc1.id
WHERE so.type ='V'
and so.name like 'SV_%'
and sc1.text like '% IN (%)'
declare @sql varchar(4000)
while exists (select 1 from #viewCorrections)
begin
select top 1 @sql = alterViewCommand from #viewCorrections
exec ( @sql )
delete from #viewCorrections where alterViewCommand = @sql
waitfor delay '000:00:05'
end
drop table #viewCorrections
end
-
What bugs me is, why do you need a five second delay after each exec? That really doesn't scream "reliability" at me...
-
One-time-hacks are often quick&dirty, so if it worked as expected, it's not at all a WTF.
-
I'm going to go ahead and guess that this piece:
' AND l.entityListID = ' +
right(so.name, len(so.name)-charindex('_', so.name))
as alterViewCommandWas not originally a hardcoded value -- that it used to figure it out (with an IN clause, one would suppose, which is really inefficient in MS SQL) upon each execution of the view?
-
Ding Ding Ding! We have a winner. Despite the existence of a table one could simply join to, this application, in code and at any time during the day, made views out of ALL the possible joins against that security table, using the form "where id in ( 1, 3, 127, 237, 637, ... <snip hundreds of values> 8657, 9872 )." The part I found really creative was that it put the identity value used for each of those joins into the NAME of the corresponding view, then used dynamic SQL to insert the view name into queries at run time. As I write this, there are about 1500 such views.
The hack-around I wrote and posted here runs as a SQL agent job, so, while the basic WTFery is still in play, the agent job silently rewrites any views having the dreaded IN clause with a simple join. It moves the server performance up to "dog slow" from "the phone will not stop ringing because no one can work," which I have to say is a plus. The 5 second delay in my hack is to prevent contention on the view definitions as they get updated.
On the up side, we pointed out to the vendor that this was perhaps ... er ... undesirable, so they jumped right on the solution: keep the basic structure in place, because it's "proven" code that is essential to their whole application, but add some logic that will make the view definition a join in cases where there are less than 8 matches, otherwise it remains an "In" clause. Brillant! Now my job only finds the "smaller" views and fixes them, instead of fixing all of them, and as a bonus we get to keep the overhead of the server and application maintaining them. Yippers.
Good sleuthing, Colin!
