Optimizing view and Table

  • christoph.kauflin wrote:

    Thanks (to all) for the advice and links. This seems to be helpful too.

    Organizational structuring is not so difficult. But the structuring of the cost (6 levels) is really confusing. Up to now I didn’t know how to do this with a cost view. (I have one for direct cost, one for indirect cost and one for personnel cost.) At the end of the process we had several stored procedures which we called cosmetic, taking more and more time to run. Every time something changed in the organization or renaming of cost, which happened surprisingly regularly, we had to run the “cosmetic” procedures.

    I was just looking at this part of your reply again. Using linked tables can help with business changes. I can't tell you if it will help in your situation without more details but I have used them for this before. As an example if you need to keep a record of the price changes for a product this is quite easy to do with a 'product price history' table which you link to your main table and, usually, allows calculations for different date ranges without having to change views or stored procedures.

     

  • I am so happy: I have shrunk the table to half its size. And I am frustrated, because I had different collations on my local server...  so I learned to script collation changes :

    ALTER TABLE tblASAP_Detail_v4

    ALTER COLUMN [ggr nr] nvarchar(10) collate

    SQL_Latin1_General_CP1_CI_AS

    and I will definitely install the developer edition

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • do you need Nvarchar - could you go to varchar? it halfs the size of the field

    MVDBA

  • thanks for the advice. I will check that.

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • I did not see anyone mention compressing the table - have you looked at those options to see if either row or page compression reduces the size of the table(s) enough?  If so, then you can test performance to see how the compression impacts the performance of your queries.

    I would bet this type of table will compress a lot using page compression.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    I did not see anyone mention compressing the table - have you looked at those options to see if either row or page compression reduces the size of the table(s) enough?  If so, then you can test performance to see how the compression impacts the performance of your queries.

    I would bet this type of table will compress a lot using page compression.

    that does work really well. just read up on it first - I have some huge tables that I use this on. jeff is 100% on the nose about trying it and testing

    MVDBA

Viewing 6 posts - 16 through 20 (of 20 total)

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