Post reply

How to convert views to tables en masse

  • December 10, 2009 at 7:11 am

    #214664

    I know this sounds somewhat illogical, but does anyone know how to read through all of the views in a given database and convert them to tables?

    What you're probably thinking is why I would want to turn a view, which is made up of tables into a table. Why not just use the underlying tables? The vendor for a piece of software that I use has these support tables prefixed with SPT_ that are used in their views. They advise us not to use the tables directly, but rather use the views since their structure will not change. The SPT_ tables will have static data throughout a given day, so I'm thinking just turn all of the views into the tables once in the morning and do all of our querying/reporting off of the tables instead of waiting 15 seconds every time a view is used.

    Does that make sense and does anyone know how to do a mass convert?

    Thanks,

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • December 10, 2009 at 1:37 pm

    #1090861

    From the limited information that's available:

    From my point of view the vendor is right with his advice since otherwise any change on a source table would affect your querys .

    However, waiting 15sec pe select on a view seems pretty long (depending on data volume nd query complexitiy, of course). I'd look into performance optimization of the current views (step 1) and indexed views (step 2) before re-deploying each view as a table each day (last resort).

    Please note that step 2 and 3 will increase the amount of data that is stored since both solutions will physically store the results on disc. If the underlying querys really take that long we might talk about a significant amount of disc space.

    Again: still guessing.

    Please describe your scenario more detailed including a sample view (probably using modified table and column names due to copyright issues) together with the number of rows per table and the number of rows for the resulting view.

    This would help a lot to get a little closer where the real issue is. If copyright is not an issue, table definition (including index def) and an actual execution plan for a given view would be best.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply