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 alterViewCommand 

    Was 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!
     


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.