Optimizing view and Table

Question

Optimizing view and Table

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

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

as_1234 SSCrazy Points: 2842 More actions · Answer 1

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.

christoph.kauflin SSC-Addicted Points: 415 More actions · Answer 2

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

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

MVDBA

christoph.kauflin SSC-Addicted Points: 415 More actions · Answer 4

thanks for the advice. I will check that.

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

Jeffrey Williams SSC Guru Points: 90253 More actions · Answer 5

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

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