SQL Server Performance

View with hard coded values

Discussion in 'Performance Tuning for DBAs' started by merrillaldrich, Dec 1, 2005.

  1. merrillaldrich New Member

    I am working with a vendor-supplied database, and my team found a very curious structure in their schema. In place of a table, there is a view containing only hard-coded values, with this definition:

    (names and values changed to protect the ... well, whomever)

    create view dbo.thestatus as
    select 0 status,'foo' status
    union
    select 1,'bar'
    union
    select 2,'grak'
    union
    select 3,'gnict'
    union
    select 4,'schmoo'
    union
    ... etc.

    I do not have a tremendous long experience with database design, but at face value this seems like a very slow structure, because I have a hunch it cannot be indexed, and that the query optimizer would probably react with only fear and puzzlement when asked to use this to compose a query result. We are in the process of evaluating/tuning for performance on this system.

    Am I crazy? Is there a reason anyone can think of that would make this structure a legit design, that I am not aware of? I want to just change this to a table with the same name.


    Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.
    merrillaldrich, Dec 1, 2005
    #2

  2. Luis Martin Moderator

    As far I know, you are not crazy, they are.
    I never see something like this.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



    Luis Martin, Dec 1, 2005
    #2

  3. mmarovic Active Member

    I've seen such stuff. Having a table is better, however I don't think you need an index on such table.
    mmarovic, Dec 1, 2005
    #2

  4. FrankKalis Moderator

    You should ask the vendor before changing anything in their structure. Maybe they think this way they can avoid to create two objects: a table and the view to access the table. Looks unusual, but I would say nothing dramatic. And who knows, maybe there is a good reason for it. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
    FrankKalis, Dec 2, 2005
    #2

  5. merrillaldrich New Member

    The thing is, I think it needs a view OR a table, but not both. There's nothing other than the hard coded values in there, so I can't think why you'd need or want both a view and a table.

    Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.
    merrillaldrich, Dec 2, 2005
    #2

  6. satya Moderator

    Well I believe rather than working on short-term solutions, it is better to involve vendor to speedup the performance, take the issues to management and explain the standards that can reduce the overhead in performance.

    I know this sounds more as administration issue than a technical solution, but it is always better to tryout all the possibilities.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Dec 4, 2005
    #2

  7. Chappy New Member

    I agree with mmarovic.

    Even if the view was a table, and it had an index, I suspect sql server would perform a table scan and not use the index anyway (because the table is so small).

    Its not how id design it, but I dont see any huge problem with it being a view.
    I would at least change the UNION to a UNION ALL though. Theres no need to ask sql to remove duplicates since we know there are none
    Chappy, Dec 5, 2005
    #2

  8. merrillaldrich New Member

    Thanks all for your suggestions. We did some QA execution plans with each method, and while the view causes that to be ugly, it does not seem to have a dramatic impact in the context of all the other work performed for the query. Go figure <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />We are changing it to a table anyway, just so we can sleep at night.<br /><br />Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.
    merrillaldrich, Dec 7, 2005
    #2

Share This Page