Infinitive recursion for my AS2000 calculated member

Hi,

Can someone please help me on this. I get following error when I browse my virtual AS2000 cube:

<Infinite recursion detected during execution of calculated member Sum({Descendants....>

I have implemented a 'dummy' utility dimension with one calculated member: (I have used a parentchild dimension because that's only way I know how to get the formula a from source view):

--

view [dbo].[vdimUtilityCalculation] as

select
'CalculationID' = 1,
'ParentID' = 1,
'CalculationName' = 'Currency',
'Formula' = 'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", [Amount Fixr], [Amount Flor]) * ValidMeasure([Rate]))',
'MemberOption' = 'SOLVE_ORDER=''-1'''
--

Any idea?

Thanks, Christer

You need to change coordinate in the utility dimension in order to prevent infinite recursion. I.e. something like that:

'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", (UtilityDim.DefaultMember,[Amount Fixr]), (UtilityDim.DefaultMember,[Amount Flor])) * ValidMeasure([Rate]))',

|||

Thanks for your reply.

I added a second 'dummy' member to my utility dimension and pointed to in the formula like this:

Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", ([CalculationUtility].&[2],[Amount Fixr]), ([CalculationUtility].&[2],[Amount Flor])) * ValidMeasure([Rate]))

It works for some accounts (account dimension with calculated members), not accounts with formula like account1/account2, they are not calculated correctly... and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

Not sure how to get this working, is this easier to implement in AS 2005?

Thanks, Christer

|||

and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

It should work. I suggest you start troubleshooting by creating a calculated member in this utility dimension with SOLVE_ORDER=-1 and then sending MDX query with Aggregate over Period dimension. It isn't easy to help through newsgroup, but if you will take it slow, step by step, you should be able to see that it does work.

|||

Thanks! After some struggle I got it to work!

sql

Infinitive recursion for my AS2000 calculated member

Hi,

Can someone please help me on this. I get following error when I browse my virtual AS2000 cube:

<Infinite recursion detected during execution of calculated member Sum({Descendants....>

I have implemented a 'dummy' utility dimension with one calculated member: (I have used a parentchild dimension because that's only way I know how to get the formula a from source view):

--

view [dbo].[vdimUtilityCalculation] as

select
'CalculationID' = 1,
'ParentID' = 1,
'CalculationName' = 'Currency',
'Formula' = 'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", [Amount Fixr], [Amount Flor]) * ValidMeasure([Rate]))',
'MemberOption' = 'SOLVE_ORDER=''-1'''
--

Any idea?

Thanks, Christer

You need to change coordinate in the utility dimension in order to prevent infinite recursion. I.e. something like that:

'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", (UtilityDim.DefaultMember,[Amount Fixr]), (UtilityDim.DefaultMember,[Amount Flor])) * ValidMeasure([Rate]))',

|||

Thanks for your reply.

I added a second 'dummy' member to my utility dimension and pointed to in the formula like this:

Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", ([CalculationUtility].&[2],[Amount Fixr]), ([CalculationUtility].&[2],[Amount Flor])) * ValidMeasure([Rate]))

It works for some accounts (account dimension with calculated members), not accounts with formula like account1/account2, they are not calculated correctly... and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

Not sure how to get this working, is this easier to implement in AS 2005?

Thanks, Christer

|||

and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

It should work. I suggest you start troubleshooting by creating a calculated member in this utility dimension with SOLVE_ORDER=-1 and then sending MDX query with Aggregate over Period dimension. It isn't easy to help through newsgroup, but if you will take it slow, step by step, you should be able to see that it does work.

|||

Thanks! After some struggle I got it to work!

Inefficient case stmt

I have a section in a sproc's WHERE statement that is causing the sproc to
be inefficient, but I don't know why.
When I look at the execution plan when this line is included, it shows that
it does a table scan on hcpfn_el3parser - which does return a table. The
part that is confusing to me, is that it does a table scan when @.el3 is
null. As you can clearly see below, when @.el3 is null, it shouldn't even
get to the function. But it does - why? And can I rewrite it in a better
way? My sproc runs in 17 seconds with this line (and a null @.el3), or 7
seconds if I rem out this line.
and dl.el3 in ( case when @.el3 is null then dl.el3 else ( select el3 from
dbo.hcpfn_el3parser ( @.el3 )) end )
Thanks, AndreTry,
what data type is dl.el3?
AMB
"Andre" wrote:

> I have a section in a sproc's WHERE statement that is causing the sproc to
> be inefficient, but I don't know why.
> When I look at the execution plan when this line is included, it shows tha
t
> it does a table scan on hcpfn_el3parser - which does return a table. The
> part that is confusing to me, is that it does a table scan when @.el3 is
> null. As you can clearly see below, when @.el3 is null, it shouldn't even
> get to the function. But it does - why? And can I rewrite it in a better
> way? My sproc runs in 17 seconds with this line (and a null @.el3), or 7
> seconds if I rem out this line.
> and dl.el3 in ( case when @.el3 is null then dl.el3 else ( select el3 from
> dbo.hcpfn_el3parser ( @.el3 )) end )
> Thanks, Andre
>
>|||@.el3 varchar(72) = null|||>> The part that is confusing to me, is that it does a table scan when
@.el3 is null. As you can clearly see below, when @.el3 is null, it
shouldn't even get to the function. <<
Wrong. A CASE expression has to evaluate *all* the THEN clauses to
determine the data type of the expression.
If you post some DDL and the actual code,then someone can help. I am
sure that names like "el3" are clear and meaningful in your industry
and just some silly sequential numbering used to fake an array, but
none of us can read it.
The obvious thing is that you need to get rid of "hcpfn_el3parser ()"
and use a query. You should start writing SQL that looks like SQL
instead of OO or procedural code.|||On Tue, 17 May 2005 10:16:56 -0700, Andre wrote:

>I have a section in a sproc's WHERE statement that is causing the sproc to
>be inefficient, but I don't know why.
>When I look at the execution plan when this line is included, it shows that
>it does a table scan on hcpfn_el3parser - which does return a table. The
>part that is confusing to me, is that it does a table scan when @.el3 is
>null. As you can clearly see below, when @.el3 is null, it shouldn't even
>get to the function. But it does - why? And can I rewrite it in a better
>way? My sproc runs in 17 seconds with this line (and a null @.el3), or 7
>seconds if I rem out this line.
>and dl.el3 in ( case when @.el3 is null then dl.el3 else ( select el3 from
>dbo.hcpfn_el3parser ( @.el3 )) end )
>Thanks, Andre
>
Hi Andre,
Hard to tell without knowing more about your tables, data and the
function. But you might try if this works:
AND ( @.el3 IS NULL
OR dl.el3 = (SELECT el3 FROM dbo.hcpfn_el3parser (@.el3) )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Indexes ineffective in MSSQL Server 2000

Indexes defined on my tables seems to be ineffective.
Eventhough execution plans shows a Index Seek or Index Scan it takes up
the same time as the query would take when it shows a Table scan in
Execution plan.
This behaviour seems to occur when the number of rows returned by the
query are > than 1/10 th of the total no of rows in the table.
If the number of rows is less than 1/10th of the total rows, the time
difference between IndexSeek and Table Scan seems to be alright.
Is this behaviour (To take up same time for Index Seek and Table Scan
when number of rows returned are more) shown by SQL server expected?
Also the Recompute statistics on my indexes are on still i would like
to know how i can recompute statistics of individual indexes defined on
the table?
Thanks
Vishal.can you run update statistics tablename and see if this solves the problem.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>|||Hi
In additon, please take a look at
http://www.sql-server-performance.c...ate_indexes.asp
http://www.sql-server-performance.c..._statistics.asp
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>|||On 03.10.2006 10:53, Vishal wrote:
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
If you pull more than a certain percentage of rows from a table a table
scan is more efficient. The amount of that percentage differs depending
on DB product, schema, query etc. So, yes it is expected and typically
optimizers take this into account when creating the execution plan.
Kind regards
robert|||Thanx for this info Robert...
Robert Klemme wrote:
> On 03.10.2006 10:53, Vishal wrote:
> If you pull more than a certain percentage of rows from a table a table
> scan is more efficient. The amount of that percentage differs depending
> on DB product, schema, query etc. So, yes it is expected and typically
> optimizers take this into account when creating the execution plan.
> Kind regards
> robert

Indexes ineffective in MSSQL Server 2000

Indexes defined on my tables seems to be ineffective.
Eventhough execution plans shows a Index Seek or Index Scan it takes up
the same time as the query would take when it shows a Table scan in
Execution plan.
This behaviour seems to occur when the number of rows returned by the
query are > than 1/10 th of the total no of rows in the table.
If the number of rows is less than 1/10th of the total rows, the time
difference between IndexSeek and Table Scan seems to be alright.
Is this behaviour (To take up same time for Index Seek and Table Scan
when number of rows returned are more) shown by SQL server expected?
Also the Recompute statistics on my indexes are on still i would like
to know how i can recompute statistics of individual indexes defined on
the table?
Thanks
Vishal.can you run update statistics tablename and see if this solves the problem.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>|||Hi
In additon, please take a look at
http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp
http://www.sql-server-performance.com/nb_execution_plan_statistics.asp
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>|||On 03.10.2006 10:53, Vishal wrote:
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
If you pull more than a certain percentage of rows from a table a table
scan is more efficient. The amount of that percentage differs depending
on DB product, schema, query etc. So, yes it is expected and typically
optimizers take this into account when creating the execution plan.
Kind regards
robert|||Thanx for this info Robert...
Robert Klemme wrote:
> On 03.10.2006 10:53, Vishal wrote:
> > Indexes defined on my tables seems to be ineffective.
> > Eventhough execution plans shows a Index Seek or Index Scan it takes up
> > the same time as the query would take when it shows a Table scan in
> > Execution plan.
> > This behaviour seems to occur when the number of rows returned by the
> > query are > than 1/10 th of the total no of rows in the table.
> > If the number of rows is less than 1/10th of the total rows, the time
> > difference between IndexSeek and Table Scan seems to be alright.
> > Is this behaviour (To take up same time for Index Seek and Table Scan
> > when number of rows returned are more) shown by SQL server expected?
> If you pull more than a certain percentage of rows from a table a table
> scan is more efficient. The amount of that percentage differs depending
> on DB product, schema, query etc. So, yes it is expected and typically
> optimizers take this into account when creating the execution plan.
> Kind regards
> robert

Indexes ineffective in MSSQL Server 2000

Indexes defined on my tables seems to be ineffective.
Eventhough execution plans shows a Index Seek or Index Scan it takes up
the same time as the query would take when it shows a Table scan in
Execution plan.
This behaviour seems to occur when the number of rows returned by the
query are > than 1/10 th of the total no of rows in the table.
If the number of rows is less than 1/10th of the total rows, the time
difference between IndexSeek and Table Scan seems to be alright.
Is this behaviour (To take up same time for Index Seek and Table Scan
when number of rows returned are more) shown by SQL server expected?
Also the Recompute statistics on my indexes are on still i would like
to know how i can recompute statistics of individual indexes defined on
the table?
Thanks
Vishal.
can you run update statistics tablename and see if this solves the problem.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegrou ps.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>
|||Hi
In additon, please take a look at
http://www.sql-server-performance.co...te_indexes.asp
http://www.sql-server-performance.co...statistics.asp
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegrou ps.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>
|||On 03.10.2006 10:53, Vishal wrote:
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
If you pull more than a certain percentage of rows from a table a table
scan is more efficient. The amount of that percentage differs depending
on DB product, schema, query etc. So, yes it is expected and typically
optimizers take this into account when creating the execution plan.
Kind regards
robert
|||Thanx for this info Robert...
Robert Klemme wrote:
> On 03.10.2006 10:53, Vishal wrote:
> If you pull more than a certain percentage of rows from a table a table
> scan is more efficient. The amount of that percentage differs depending
> on DB product, schema, query etc. So, yes it is expected and typically
> optimizers take this into account when creating the execution plan.
> Kind regards
> robert

Indexes degrade too fast

(Win2003, SQL Server 2000 SP4)

I have a database of about 5Gb of size. Some queries where taking more than 1 minute to complete execution (all of them are stored procedures). Because of that lack of performance, I call the command DBREINDEX for each table, executed the sp_updatestats system stored procedure and finally I executed the sp_recompile system stored procedure for each sp in my database.

After all this task, queries completed in a matter of a few seconds instead of minutes. Strange enough is that some hours later (about 6 hrs), after normal use (this database belong to a Client/Server information system), the problem appeared again: Queries started to take too long to complete.

I am assuming that indexes are degrading too fast so that they required another ReIndex, but I am not sure.

Any thoughts? How can I prevent this behaviour?

Thank a lot in advanced.Have a look at the execution plan before and after the re-indexing routine. This will give you the "good" plan, and the "bad" plan. If the plans are identical, then the reindexing helped (doubtful but possible on a 5Gb system). More likely, the sp_recompile bumped the plan back from the "bad" plan to the "good" plan. By now, you are wondering if I just like putting "good" and "bad" in quotes. Well, no, I am not particularly fond of it, but it does remind you that not all plans are created equal. Can these procedures take in parameters that can determine a range? If so, then the "bad" plan may be good for some ranges, and the "good" plan may be bad for the same range.|||Thanks. What I really find confusing is why the performance of the database degrade in a matter of just 6 hours or so.

Of all the customers that I have distributed the same DB design, this one is the only one that presents this problem; and the size of the database is not the issue: I have other customer that has a 15 Gb database with the same DB design, then same DBMS version but with a not-as-good hardware. However, this customer does not have the same problem.|||What does DBCC SHOWCONTIG show you on the tables in question once the slow down restarts? Do you see high fragmentation numbers and low density numbers? Then yes your indices have degraded. have you traced the 2 systems. Does the one that is experiencing the issue have more insert and updates goinng on? Is it growing at a faster rate?|||Thanks. I will check that out. Just in case: Which DBCC command allow me to defrag all the tables of a database? I am planning to run that command and later run a DBCC REINDEX command for each index of every table. I suppose that this would produce a clean database, am I right?

Thanks in advance.|||There is no command to rebuild all of the tables/indexes at once. You can create or google for scripts that will do that for you, however.