Calculated Column performance

Hi,
Is calulation more efficent in the SQL CODE or in a Calculated Column? If
I'm correct the Calculated Column is done on the client SELECT query every
time where as my INSERT TSQL code will only do it once on the INSERT?
Thanks
DECLARE
@.ACCDCC_Table TABLE
(cnt INT NULL,
Time INT NULL,
Location FLOAT NULL,
FPM1 FLOAT NULL,
FPM2 FLOAT NULL,
FPM_Diff AS (
CASE
WHEN
FPM1 IS NULL OR
FPM2 IS NULL THEN NULL
ELSE
FPM2 - FPM1
END),
AccDcc VARCHAR(10) NULL
)
as per doing this:
UPDATE
@.ACCDCC_Table
SET
FPM_Diff = FPM2 - FPM1
WHERE
FPM_Diff IS NULL
--
don> Is calulation more efficent in the SQL CODE or in a Calculated Column? If
> I'm correct the Calculated Column is done on the client SELECT query every
> time where as my INSERT TSQL code will only do it once on the INSERT?
That's correct.
The flip side is that you will have to constantly maintain the value in the
"calculated" column if you're going to rely on doing it manually.
A|||Thanks
"AB - MVP" wrote:

> That's correct.
> The flip side is that you will have to constantly maintain the value in th
e
> "calculated" column if you're going to rely on doing it manually.
> A
>
>|||Think you meant you have to maintain it when you are doing it once, on
update. If it's calculated automatically, every time you "select" the
column, the value is not being stored in database, It's being re-calculated
every time you do a select, so there's no maintenance required.
Which is better depends on whether you need
A) Insert/Update Performance, and/Or storage Size Constraints -- Use
Calculated Column, or
B) Select Performance is the main concern -- Then Use persisted Column and
maintain it upon every Insert/Update
"AB - MVP" wrote:

> That's correct.
> The flip side is that you will have to constantly maintain the value in th
e
> "calculated" column if you're going to rely on doing it manually.
> A
>
>|||Thanks
"CBretana" wrote:
> Think you meant you have to maintain it when you are doing it once, on
> update. If it's calculated automatically, every time you "select" the
> column, the value is not being stored in database, It's being re-calculate
d
> every time you do a select, so there's no maintenance required.
> Which is better depends on whether you need
> A) Insert/Update Performance, and/Or storage Size Constraints -- Use
> Calculated Column, or
> B) Select Performance is the main concern -- Then Use persisted Column and
> maintain it upon every Insert/Update
> "AB - MVP" wrote:
>|||I guess someone missed the meaning of "quotes" around "calculated"... <sigh>
"donron" <donron@.discussions.microsoft.com> wrote in message
news:2CCDA1CD-B43E-4D0E-AEFF-6C77615F5755@.microsoft.com...
> Thanks
> "CBretana" wrote:
>|||that would be me... but rereading, (I may be just dense this am), but I'm
still not sure what you mean by it... I thought it was just a typo...
"AB - MVP" wrote:

> I guess someone missed the meaning of "quotes" around "calculated"... <sig
h>
>
>
> "donron" <donron@.discussions.microsoft.com> wrote in message
> news:2CCDA1CD-B43E-4D0E-AEFF-6C77615F5755@.microsoft.com...
>
>

Calcualted Cells Performance Problem

Hello,

Initially the business divided its Practices into two groups, Industrial and Functional which were maintained in the relational source system as two seperate tables. The business has decided to expand the number of practices it reports on, however changes to the relational source system has not fully been implemented. We require reporting on the Budget data now so we've created a dimension named "Practices" which follows the same dimensional structure as the "Inudstry" and "Function" dimensions except that there is an extra parent level of "Practice Type". I would like to hide this new all inclusive "Practices" dimension from end users and effectively look up the corresponding value in the Industry or Function dimension from the Budget fact table.

I have the following calculated cell which achieves the desired results but has rather slow perforamce.

CREATE CELL CALCULATION CURRENTCUBE.[Budget Functional Practice]

FOR

'({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS)'

AS'

(StrToMember("Measures.[" + Measures.CurrentMember.Name + "_]"),

StrToMember("[Practices].[Hierarchy].[Practice Class].&[Function Practice].&[" + [Function].[Func Practice Def].CurrentMember.Name + "]"),

[Function].[Func Practice].[All])'

I was wondering if:

a) Is a calculated cell the best solution?

b) Is there a more efficient MDX which can be used?

Thank you.

If I am understanding correctly, you have a measure called Budget_ which you want to be the budget at the All functions level. If this is correct the following scope should work and perform significantly better. All of the string and currentmember references would have been slowing things down.

SCOPE ({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS);

(Measures.[Budget_]) = ([Function].[Func Practice].[All], [Measures].[Budget]);

END SCOPE;

|||

Thank you for the reply Darren, I think this is a step in the right direction.

The budget fact table contains one dimension which accounts for all of our practices (this dimension is hidden from the users). What I want to be able to do is, based on a selected member from either of the visible industry practice or function practice dimensions, lookup the corresponding value in the practices dimesnsion and show the budget measure.

|||

If you're using AS 2005, you might consider an alternative approach, using many-to-many dimensions. But first (if I interpreted your scenario correctly) you would need to set up 2 "Practices" dimension security roles, 1 for users of Function and the other for users of Industry (both roles would have Visual Totals enabled). Role#1 would allow access to the "Function", and Role#2 to the "Industry" member, at the "Practice Type" level - these would also be the Default Members for the respective roles. This would ensure that Function users only access Function data and Industry users likewise. Role#1 would be denied access to the Industry dimension and Role#2 to the Function dimension.

For the many-to-many dimension modelling, there would be a bridge table (could be a named query) which maps Function dimension table rows to the Practices dimension, and a separate bridge table to map Industry. Once a measure group is created for each bridge table, 1 bridging Function and Practices and the other bridging Industry and Practices, both Function and Industry can be configured as many-to-many dimensions for the Budget measure group.

Calcualted Cells Performance Problem

Hello,

Initially the business divided its Practices into two groups, Industrial and Functional which were maintained in the relational source system as two seperate tables. The business has decided to expand the number of practices it reports on, however changes to the relational source system has not fully been implemented. We require reporting on the Budget data now so we've created a dimension named "Practices" which follows the same dimensional structure as the "Inudstry" and "Function" dimensions except that there is an extra parent level of "Practice Type". I would like to hide this new all inclusive "Practices" dimension from end users and effectively look up the corresponding value in the Industry or Function dimension from the Budget fact table.

I have the following calculated cell which achieves the desired results but has rather slow perforamce.

CREATE CELL CALCULATION CURRENTCUBE.[Budget Functional Practice]

FOR

'({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS)'

AS'

(StrToMember("Measures.[" + Measures.CurrentMember.Name + "_]"),

StrToMember("[Practices].[Hierarchy].[Practice Class].&[Function Practice].&[" + [Function].[Func Practice Def].CurrentMember.Name + "]"),

[Function].[Func Practice].[All])'

I was wondering if:

a) Is a calculated cell the best solution?

b) Is there a more efficient MDX which can be used?

Thank you.

If I am understanding correctly, you have a measure called Budget_ which you want to be the budget at the All functions level. If this is correct the following scope should work and perform significantly better. All of the string and currentmember references would have been slowing things down.

SCOPE ({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS);

(Measures.[Budget_]) = ([Function].[Func Practice].[All], [Measures].[Budget]);

END SCOPE;

|||

Thank you for the reply Darren, I think this is a step in the right direction.

The budget fact table contains one dimension which accounts for all of our practices (this dimension is hidden from the users). What I want to be able to do is, based on a selected member from either of the visible industry practice or function practice dimensions, lookup the corresponding value in the practices dimesnsion and show the budget measure.

|||

If you're using AS 2005, you might consider an alternative approach, using many-to-many dimensions. But first (if I interpreted your scenario correctly) you would need to set up 2 "Practices" dimension security roles, 1 for users of Function and the other for users of Industry (both roles would have Visual Totals enabled). Role#1 would allow access to the "Function", and Role#2 to the "Industry" member, at the "Practice Type" level - these would also be the Default Members for the respective roles. This would ensure that Function users only access Function data and Industry users likewise. Role#1 would be denied access to the Industry dimension and Role#2 to the Function dimension.

For the many-to-many dimension modelling, there would be a bridge table (could be a named query) which maps Function dimension table rows to the Practices dimension, and a separate bridge table to map Industry. Once a measure group is created for each bridge table, 1 bridging Function and Practices and the other bridging Industry and Practices, both Function and Industry can be configured as many-to-many dimensions for the Budget measure group.

Cached query result

I want to check the performance of m query and i just want to remove cached query results. Is there any suggestion how can i do this.

I just want to check after each modificatin how much improvement in performance

Check the following dbcc commands in BOL.

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

DO NOT ATTEMPT THIS ON A PRODUCTION SERVER!!!!!!!!

AMB

cache in sql server 2000

There is a command or option that let me change or manipulate
the size of procedure and data cache in order to obtain better
performance ratios in sql server 2000?
--
ThanksNo there is not one for the procedure cache. You can set the MAX size the
memory pool uses with MAX Server Memory but you can't actually control the
individual sizes. Your best bet is to make sure you optimize the calls so
that the plans are reused and the proc cache will stay small and manageable.
--
Andrew J. Kelly SQL MVP
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:7944D92A-F413-49ED-8DED-6F7B50968889@.microsoft.com...
> There is a command or option that let me change or manipulate
> the size of procedure and data cache in order to obtain better
> performance ratios in sql server 2000?
> --
> Thanks|||Just to add to Andrew's reply, please write to sqlwish@.microsoft.com and let
them know if this is something you'd like to see... It would be nice to put
some pressure on the SQL Server team to get this feature in.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:7944D92A-F413-49ED-8DED-6F7B50968889@.microsoft.com...
> There is a command or option that let me change or manipulate
> the size of procedure and data cache in order to obtain better
> performance ratios in sql server 2000?
> --
> Thanks|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ui4$dIv5FHA.2560@.TK2MSFTNGP12.phx.gbl...
> Just to add to Andrew's reply, please write to sqlwish@.microsoft.com and
> let them know if this is something you'd like to see... It would be nice
> to put some pressure on the SQL Server team to get this feature in.
>
I think it's a philosophy thing. It's best to have that managed
automatically. You have one knob to turn (total server memory). Other than
that tune your application, not the database server.
David|||If you want that "feature," go back to version 6.5 or earlier. Better yet,
convert to Oracle, then you can spend all day twiddling knobs, EVERY DAY.
That will be the only work you will ever get to do.
As for me, I would rather spend more of my time helping the developers build
better designs, access methods, and scaling our architecture.
Sincerely,
Anthony Thomas
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u11NMOv5FHA.1420@.TK2MSFTNGP09.phx.gbl...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ui4$dIv5FHA.2560@.TK2MSFTNGP12.phx.gbl...
> > Just to add to Andrew's reply, please write to sqlwish@.microsoft.com and
> > let them know if this is something you'd like to see... It would be nice
> > to put some pressure on the SQL Server team to get this feature in.
> >
> I think it's a philosophy thing. It's best to have that managed
> automatically. You have one knob to turn (total server memory). Other
than
> that tune your application, not the database server.
> David
>|||If you work on an enterprise-level application you'll quickly find that
certain "automatic" management features just don't do a good enough job.
They're great for small to medium applications, but it's nice to tweak
things for larger setups.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uNrSwBw5FHA.444@.TK2MSFTNGP11.phx.gbl...
> If you want that "feature," go back to version 6.5 or earlier. Better
> yet,
> convert to Oracle, then you can spend all day twiddling knobs, EVERY DAY.
> That will be the only work you will ever get to do.
> As for me, I would rather spend more of my time helping the developers
> build
> better designs, access methods, and scaling our architecture.
>|||Yea, well, I currently work on more than 200 of those applications across
more than 50 SQL Server installations, with 6 or more of those on
medium-scaled clustered configurations.
So, I would push SQLWISH to keep driving at making those autonomic features
more reliable so there would be less need for the missing knobs.
We run Oracle in this shop too, and that is all I see those poor guys do all
day long.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ueZqAxw5FHA.2716@.TK2MSFTNGP11.phx.gbl...
> If you work on an enterprise-level application you'll quickly find that
> certain "automatic" management features just don't do a good enough job.
> They're great for small to medium applications, but it's nice to tweak
> things for larger setups.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:uNrSwBw5FHA.444@.TK2MSFTNGP11.phx.gbl...
> > If you want that "feature," go back to version 6.5 or earlier. Better
> > yet,
> > convert to Oracle, then you can spend all day twiddling knobs, EVERY
DAY.
> > That will be the only work you will ever get to do.
> >
> > As for me, I would rather spend more of my time helping the developers
> > build
> > better designs, access methods, and scaling our architecture.
> >
>|||Let's not confuse "automation" with "defaults." I'll agree with Adam in
that as you get into larger and more critical systems, the system
configuration and database object defaults become less and less useful;
however, the concept that the system parameters are dynamically set and
"automatically" managed remains valid. I would go as far as to say that the
dynamic configuration management becomes even more critical with larger
scale systems.
The whole point of computing platforms and solutions is the drive to
systematically apply logical algorithms to manual processes in an automation
mechanism. The DBMS is no less critical to this function: manual, labor
intensive processes are automated freeing up time to redirect human
resources to higher level, abstracted tasks and functions such as design and
architecture.
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8uq99w5FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Yea, well, I currently work on more than 200 of those applications across
> more than 50 SQL Server installations, with 6 or more of those on
> medium-scaled clustered configurations.
> So, I would push SQLWISH to keep driving at making those autonomic
features
> more reliable so there would be less need for the missing knobs.
> We run Oracle in this shop too, and that is all I see those poor guys do
all
> day long.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ueZqAxw5FHA.2716@.TK2MSFTNGP11.phx.gbl...
> > If you work on an enterprise-level application you'll quickly find that
> > certain "automatic" management features just don't do a good enough job.
> > They're great for small to medium applications, but it's nice to tweak
> > things for larger setups.
> >
> >
> > --
> > Adam Machanic
> > Pro SQL Server 2005, available now
> > http://www.apress.com/book/bookDisplay.html?bID=457
> > --
> >
> >
> > "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> > news:uNrSwBw5FHA.444@.TK2MSFTNGP11.phx.gbl...
> > > If you want that "feature," go back to version 6.5 or earlier. Better
> > > yet,
> > > convert to Oracle, then you can spend all day twiddling knobs, EVERY
> DAY.
> > > That will be the only work you will ever get to do.
> > >
> > > As for me, I would rather spend more of my time helping the developers
> > > build
> > > better designs, access methods, and scaling our architecture.
> > >
> >
> >
>

Cache Hit Ration on SAN

We recently moved a database to the SAN and it is not performing so well. Watching performance logs I have noticed that the Cache hit ratio is only around 60%. I checked memory right away and that seems to be fine. Plenty of physical memory available and only about 40% is committed to use.

The SAN is currently configured to have 200 mg of Read Cache, I would imagine that is a little low, but not sure.

This is the first time we have ever put a database on the SAN so our knowledge of optimization is lacking. Any ideas on how to improve performance would be helpful.

Thanks muchWhat do you mean "plenty" and what edition of OS and SQL are you running?|||By pleanty I mean 650000 K. We are running Windows 2000 and SQL 2000.

Thanks much.|||650MB is plenty? OK then. And I know that you were running some sort of Windows (SQL doesn't run on anything else) with some version of SQL. The question was: "...what edition of OS and SQL?"|||Try this link for some performance testing info (http://www.devarticles.com/c/a/SQL-Server/How-to-Perform-a-SQL-Server-Performance-Audit/1/)|||i screwed up
get more ram|||I don't think Ram is the problem. We have a totol of 2GB, with 650MB remaining when the server is running slow. Wouldn't remaining RAM drop lower than that if we needed more? If I watch Pages/sec it rarely goes above 1. Watching SQL Memory, the target memory is always equal to the total memory. I think this is a problem with IO on the SAN.

Thanks much

buying a new server to put SQL2000 onto

Our SQL Developer asked for a new server with a separate small hard disk for the Transaction Log alone to reside on, to increase performance. This will be hard to do, since the servers we have been looking at are low-profile rackmount, and only hold 2 SATA disks. I hate to waste our only expansion bay on a small HD. Is this really something important, or will a Quad-core processor and plenty of RAM make the performance difference negligible? We have 32-bit SQL2000 licensed per processor, and our database is only about 26GB. I was hoping to get 1 large disk and partition it into a 20GB OS partition, and the rest would be for SQL. Am I totally on the wrong track?

My 2nd question is about RAM - if we get 4GB of RAM, will it decrease the performance if we get 64-bit O/S pre-installed instead of 32-bit? I know 64-bit O/S *can use* more RAM than 4GB, but does it *need* more RAM for the same level of performance that we have now? (We are planning to expand that to at least 8-12GB whenever we upgrade to 64-bit SQL2005, but the budget does not allow it just yet.)

Thanks!

Ideally, there would be separate disk arrays for the Transaction Log, the database file, and the TempDb database. Notice, I mentioned disk arrays (Or LUNs on a SAN or NAS). Now that is for a high performance, high activity enterprise critical system. You needs may not be so critical.

You didn't mention if this server was for Development work, or for Production. (Development work can get by with considerably less server capability.)

I would choose the 64bit OS, it will use memory more efficiently and will allow for easier upgrade of your SQL Server. I would 'fast track' the SQL Server upgrade to 64 bit, and additional memory. And then a SAN or NAS, or disk array.

So for now, you 'may' be able to 'live' with the two SATA disks -put the TLog files on one, and the TempDb and Datafiles on the other. Don't expect a lot of performance improvement if your current situation is 'disk bound' -you may get some improvement, just don't expect it and hopefully you will be pleasantly surprised.

|||

Thanks, here are a few more details if it helps. This is a production server, but its for a small business and high availability is not that critical. (We have time to restore from tape if needed.) According to our Developer, we are processor-bound right now, we're maxing out an older single 32-bit 3GHz Xeon. We are currently running everything on one disk and currently running with 3GB of RAM. And we're not hurting all that bad for SQL performance, we are rearranging our hardware to allow for an Exchange Server upgrade, and we're planning to move SQL to a new machine because we think it would benefit from it more than Exchange would.

Thanks again for your advice

Business Scorecard Manager

hello -
We are looking for a 3rd party tool for dashboarding, gauges and Key
Performance Indicators (KPIs).
We are already using SSRS 2005 for reporting purposes.
Is MS's Business Scorecard Manager
(http://office.microsoft.com/en-us/FX012225041033.aspx) anyway related
to SSRS 2005?
Is Business Scorecard Manager another BI module in SQL Server 2005?
any inputs are appreciated.
thanks
- jasthiWe just had a demo for Business Scorecard Manager. It is a separate
application that runs on Sharepoint (Services or Portal). It can pull data
from any datasource directly or through cubes from multiple datasources. It
can incorporate reports from Reporting Services (2000 or 2005).
I'm totally new at Scorecard Manager, but this is my understanding so far.
So anyone can correct me or add more...
"siva.jasthi@.gmail.com" wrote:
> hello -
> We are looking for a 3rd party tool for dashboarding, gauges and Key
> Performance Indicators (KPIs).
> We are already using SSRS 2005 for reporting purposes.
> Is MS's Business Scorecard Manager
> (http://office.microsoft.com/en-us/FX012225041033.aspx) anyway related
> to SSRS 2005?
> Is Business Scorecard Manager another BI module in SQL Server 2005?
> any inputs are appreciated.
> thanks
> - jasthi
>

Business Objects and Db Performance

I'm new to Business Objects, and I have a question to ask,

Does creating universe in the designer actually creates indexes in the DB? Or universe is just a reference model for the BO app.

If so, won't poor designed universe slow down DB performance?

For those who have experience in BO, please advise. Thanks.In my experience BO does not create any indexes in the DB. And yes, universe design is very important as poor universe design will lead to poor performance.. I am using BO 5.1.?, I'm not sure what version you are using.

Any more questions please let me know.

Thanks,

Originally posted by Patrick Chua
I'm new to Business Objects, and I have a question to ask,

Does creating universe in the designer actually creates indexes in the DB? Or universe is just a reference model for the BO app.

If so, won't poor designed universe slow down DB performance?

For those who have experience in BO, please advise. Thanks.