Measuring the duration of encrypted stored procedure

Hi Everybody,
I am trying to measure the difference in execution between the encrypted
version versus the non-encrypted version of a stored procedure. I found that
in some tries the encrypted version of the stored procedure takes less time
to execute than the unencrypted version!!! (I an using SQL Profiler and a
custom template to store the result in a table, which is on a different sql
server on a different machine).
Any thoughts on why this is happening would be highly appreciated.
Thanks.
Regards,
Soumitra BanerjeeI'm not aware of an reasons the encrypted proc should be any different.
There certainly could be reaons I'm not aware of though...
what types of performance differences are you seeing?
<<
I found that
> in some tries the encrypted version of the stored procedure takes less
time
> to execute than the unencrypted version!!!
needless to say... a proc isn't guaranteed to take the same amount of time
every time... are you sure there's a correlation in time based on whether
it's encrypted? Could it simply be that your proc takes different amounts of
time to run based on load on the server or the parameters you're passing in?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:%231sdZal%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> Hi Everybody,
> I am trying to measure the difference in execution between the encrypted
> version versus the non-encrypted version of a stored procedure. I found
that
> in some tries the encrypted version of the stored procedure takes less
time
> to execute than the unencrypted version!!! (I an using SQL Profiler and a
> custom template to store the result in a table, which is on a different
sql
> server on a different machine).
> Any thoughts on why this is happening would be highly appreciated.
> Thanks.
> Regards,
> Soumitra Banerjee
>|||Thanks for the mail. The result of my testing are as follows:
SQL Server was restarted before TRY 1 . After TRY 1 the same stored
procedure is executed 4 times and the time differences are noted using SQL
Profiler.
SET 1
Non Encrypted Version
Encrypted Version
TRY 1 4694
4589.4
TRY 2 635.2
406.2
TRY 3 330
346.2
TRY 4 534
589
TRY 5 357
303.2
SET 2
Non Encrypted Version
Encrypted Version
TRY 1 8439
8435
TRY 2 1350
956
TRY 3 944
856
TRY 4 1014
813
TRY 5 911
792
SET 3
Non Encrypted Version
Encrypted Version
TRY 1 8900
2800
TRY 2 1197
1356
TRY 3 1059
1033
TRY 4 764
1150
TRY 5 1095
1086
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:ubCjbjl#DHA.3232@.TK2MSFTNGP10.phx.gbl...
> I'm not aware of an reasons the encrypted proc should be any different.
> There certainly could be reaons I'm not aware of though...
> what types of performance differences are you seeing?
> <<
> I found that
> time
> needless to say... a proc isn't guaranteed to take the same amount of time
> every time... are you sure there's a correlation in time based on whether
> it's encrypted? Could it simply be that your proc takes different amounts
of
> time to run based on load on the server or the parameters you're passing
in?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
> news:%231sdZal%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> that
> time
a
> sql
>|||Soumitra,
Are you using
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
between executions, as well as exactly the same parameter set?
If not, you cannot expect similar results.
And, even if you are, you still cannot expect standardized results until
you're working in a sandbox.
Machine load variations are a huge factor.
James Hokes
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:%231sdZal%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> Hi Everybody,
> I am trying to measure the difference in execution between the encrypted
> version versus the non-encrypted version of a stored procedure. I found
that
> in some tries the encrypted version of the stored procedure takes less
time
> to execute than the unencrypted version!!! (I an using SQL Profiler and a
> custom template to store the result in a table, which is on a different
sql
> server on a different machine).
> Any thoughts on why this is happening would be highly appreciated.
> Thanks.
> Regards,
> Soumitra Banerjee
>

Measuring the duration of encrypted stored procedure

Hi Everybody,
I am trying to measure the difference in execution between the encrypted
version versus the non-encrypted version of a stored procedure. I found that
in some tries the encrypted version of the stored procedure takes less time
to execute than the unencrypted version!!! (I an using SQL Profiler and a
custom template to store the result in a table, which is on a different sql
server on a different machine).
Any thoughts on why this is happening would be highly appreciated.
Thanks.
Regards,
Soumitra BanerjeeI'm not aware of an reasons the encrypted proc should be any different.
There certainly could be reaons I'm not aware of though...
what types of performance differences are you seeing?
<<
I found that
> in some tries the encrypted version of the stored procedure takes less
time
> to execute than the unencrypted version!!!
needless to say... a proc isn't guaranteed to take the same amount of time
every time... are you sure there's a correlation in time based on whether
it's encrypted? Could it simply be that your proc takes different amounts of
time to run based on load on the server or the parameters you're passing in?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:%231sdZal%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> Hi Everybody,
> I am trying to measure the difference in execution between the encrypted
> version versus the non-encrypted version of a stored procedure. I found
that
> in some tries the encrypted version of the stored procedure takes less
time
> to execute than the unencrypted version!!! (I an using SQL Profiler and a
> custom template to store the result in a table, which is on a different
sql
> server on a different machine).
> Any thoughts on why this is happening would be highly appreciated.
> Thanks.
> Regards,
> Soumitra Banerjee
>|||Thanks for the mail. The result of my testing are as follows:
SQL Server was restarted before TRY 1 . After TRY 1 the same stored
procedure is executed 4 times and the time differences are noted using SQL
Profiler.
SET 1
Non Encrypted Version
Encrypted Version
TRY 1 4694
4589.4
TRY 2 635.2
406.2
TRY 3 330
346.2
TRY 4 534
589
TRY 5 357
303.2
SET 2
Non Encrypted Version
Encrypted Version
TRY 1 8439
8435
TRY 2 1350
956
TRY 3 944
856
TRY 4 1014
813
TRY 5 911
792
SET 3
Non Encrypted Version
Encrypted Version
TRY 1 8900
2800
TRY 2 1197
1356
TRY 3 1059
1033
TRY 4 764
1150
TRY 5 1095
1086
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:ubCjbjl#DHA.3232@.TK2MSFTNGP10.phx.gbl...
> I'm not aware of an reasons the encrypted proc should be any different.
> There certainly could be reaons I'm not aware of though...
> what types of performance differences are you seeing?
> <<
> I found that
> > in some tries the encrypted version of the stored procedure takes less
> time
> > to execute than the unencrypted version!!!
> >>
> needless to say... a proc isn't guaranteed to take the same amount of time
> every time... are you sure there's a correlation in time based on whether
> it's encrypted? Could it simply be that your proc takes different amounts
of
> time to run based on load on the server or the parameters you're passing
in?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
> news:%231sdZal%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> > Hi Everybody,
> >
> > I am trying to measure the difference in execution between the encrypted
> > version versus the non-encrypted version of a stored procedure. I found
> that
> > in some tries the encrypted version of the stored procedure takes less
> time
> > to execute than the unencrypted version!!! (I an using SQL Profiler and
a
> > custom template to store the result in a table, which is on a different
> sql
> > server on a different machine).
> >
> > Any thoughts on why this is happening would be highly appreciated.
> >
> > Thanks.
> > Regards,
> >
> > Soumitra Banerjee
> >
> >
>|||Soumitra,
Are you using
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
between executions, as well as exactly the same parameter set?
If not, you cannot expect similar results.
And, even if you are, you still cannot expect standardized results until
you're working in a sandbox.
Machine load variations are a huge factor.
James Hokes
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:%231sdZal%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> Hi Everybody,
> I am trying to measure the difference in execution between the encrypted
> version versus the non-encrypted version of a stored procedure. I found
that
> in some tries the encrypted version of the stored procedure takes less
time
> to execute than the unencrypted version!!! (I an using SQL Profiler and a
> custom template to store the result in a table, which is on a different
sql
> server on a different machine).
> Any thoughts on why this is happening would be highly appreciated.
> Thanks.
> Regards,
> Soumitra Banerjee
>

meassure groups with different amount of dimensions

Hi everybody,

I've got a Fact Data table with a value and 16 dimensions.

Now I want to create a second measure group Color with a value and 3 dimensions.

I've filled this table with values and id's for each dimension.

Bu when I make an mdx query with a measure from the FactData and a measure from the second measure group (Color), only the first measure has a value, the second (the measure from Color) is null.

Is these something I've forgotton to set in the cube?

thanks in advance

Filip

Hi,

If you run a simple query (below) do you get two columns of data in the results?

select {[Measures].[Measure Group 1],[Measures].[Measure Group Color]} on 0

from [Cube]

results:

Measure Group 1 Measure Group Color

123231 123213

If not I suspect something else is wrong, perhaps check that you have set up the joins between the dimensions and the facts correctly. If you do get values in both, perhaps it is worth while posting your query.

Hope it helps,
Matt

|||

Hi,

that query runs.

but I have another problem now, this measure group I've created for storing color information, only can't have a Sum or Count as aggregation.

I cannot see the 65280 or ... value for the color I want to use.

I didn't included a period dimension to this measure group.

Is that the cause?

Filip

|||

Hi,

Sorry for delay I was investigating. If the measure is not aggregatable then you will get Null in the measure unless you go down to the granularity in which the data is held at. This is an area not that familar with and finding it difficult to prove.

But if you don't include a dimension in measure and the measures are aggregatable it will not causes nulls to appear, you get funny results. e.g.

select {[Measures].[Measure Group 1],[Measures].[Measure group 2]} on 0,

[Dimension only on measure group 1] on 1

from [cube]

Results:

Measure Group 1 Measure Group 2

dim a 45 1234 1234 being the total in measure group 2

dim b 23 1234

dim c 78 1234

Sorry I could answer it more positively.

Matt

|||

thank you for your help!

it works at a certain level.

at the granularity level, it works fine,

but once it starts aggregating, the value 256 becomes a sum of x times 256.

I have to solve that problem...

thansk for you help

Filip

mdx script context execution

Hi everybody.

I have a mdx script on a cube involving several cells through subcube. The subcube takes the values of some cells and use them to calculate the values of some other cells.

I have a user who has limited access rights on the dimensions, so that he can't see all the cells of the cube.

But I need that all the users can perform all the calculations in the cube even if there are some members (used to evaluate the calculations) they are not allowed to.

I know that the calculations script are evalueted in the context of the user querying the cube.

I wonder if there is a command to make the script being evaluated in the context of a specified user, an administrator in this case (something like "execute as"). So that the calculations are applied first, and after that, the user with reduced rights can see only the cells he is allowed to...

thank you very much.

Sorry, but there is no 'Execute As' feature and I can't think of anyway that you could reference a secured cell in a calculation.|||

Actually one possible solution to this would be to write a .Net stored procedure, that would would use AdoMd to connect back to the user with a new connection and execute an MDX query and return the results. When you deploy a stored proc assembly you have the option of specifying the account to impersonate.

Performance could be an issue with this approach as there is a bit of overhead involved in calling out to a stored procedure, but if your calc is at a fairly high level this might be a solution.

|||

Well, you give me a wisdom, thank you very much!

A more question, because I've never used a .Net stored procedure for Analysis Services. Do you mean that through it I can make the calculations in the script of the cube being executed by a specified user with all the necessary rights, and, after the calculations are realized on all the involved cells, then I can make the application to connect back as the current user to query the cube (therefore, seeing the previous results but only for the allowed cells)?

It'd be great.

have you any examples?

Thank you very very much!!!!

|||Check out this link for examples: http://www.codeplex.com/ASStoredProcedures|||

The AS Stored Procedure project has some good examples (I wrote some of them). However I don't think we have any that do exactly what you are after.

What would happen is that from a "limited" user, they would request a value.

That value would be calculated by a stored procedure. When the stored procedure is deployed it can be configured to run under a specific user account. In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query. This would return a cellset and the stored proc would extract the value of the required cell and return that value|||

Thank you very much again.

You say: “

v In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.

v This would return a cellset and the stored proc would extract the value of the required cell and return that value

“

But I wonder if in this way the “poor” user (with limited rights) in Role1, issuing the query through the specified user for th AS SP, would be allowed to see also the cells he is forbidden to… In fact I need him to see only the cells he is allowed to, but with the values calculated with calculations involving all the cells (even the ones he is not allowed to). Is this possible this way?

However, I give you an example of what I need to do. Let’s say I need to do something like in the following script (a very simplified version of my script, but just to understand the type of calculation).

I have a dimension [DIM Mesi] with four key members [01], [02], [03], [00] and a one-level attribute hierarchy [Dim Mesi].

Role1 can access only the “[DIM Mesi].[DIM Mesi].&[01]”, “[DIM Mesi].[DIM Mesi].&[02]” and the ”existing” with these ones (I mean the ancestors of these members, which in this case, is the AllMember).

There is a basket member [DIM Mesi].[DIM Mesi].&[00] from where I take the values to be reversed on the three members [01], [02], [03] from the Measure [Costi1] into the same measure (or even in a different measure).

The calculations are something similar to the ones below.

I’d like the users of Role1 be allowed to see 1/3 of the values of cells of the &[00] member in the cells of &[01] and &[02], but they should not be able to see nor the &[00] member related cells neither the &[03] member related cells.

Further, the [DIM Mesi].[DIM Mesi].&[00] should be assigned a 0 value (even if the user can’t see that value) so that the total is not twice, in case I don’t use VisualTotals.

If possible, I’d like to even be able to decide whether he must see the Visual Totals or not, but I suppose that through the AS SP executed by another user I can only see the Actual Totals, or am I wrong?

THANK YOU VERY MUCH for your kind help!

CALCULATE;

CREATE SET CURRENTCUBE.[GoodMembers]

AS {

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[01]")), {}, [DIM Mesi].[DIM Mesi].&[01]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[02]")), {}, [DIM Mesi].[DIM Mesi].&[02]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[03]")), {}, [DIM Mesi].[DIM Mesi].&[03])

};

scope

(

{[Measures].[Costi1]}

,

{

[GoodMembers]

}

);

this= [DIM Mesi].[DIM Mesi].&[00]/3

;

freeze(this);

end scope;

scope

([Measures].[Costi1],

{

[DIM Mesi].[DIM Mesi].&[00]

}

);

this= 0;

end scope

mdx script context execution

Hi everybody.

I have a mdx script on a cube involving several cells through subcube. The subcube takes the values of some cells and use them to calculate the values of some other cells.

I have a user who has limited access rights on the dimensions, so that he can't see all the cells of the cube.

But I need that all the users can perform all the calculations in the cube even if there are some members (used to evaluate the calculations) they are not allowed to.

I know that the calculations script are evalueted in the context of the user querying the cube.

I wonder if there is a command to make the script being evaluated in the context of a specified user, an administrator in this case (something like "execute as"). So that the calculations are applied first, and after that, the user with reduced rights can see only the cells he is allowed to...

thank you very much.

Sorry, but there is no 'Execute As' feature and I can't think of anyway that you could reference a secured cell in a calculation.|||

Actually one possible solution to this would be to write a .Net stored procedure, that would would use AdoMd to connect back to the user with a new connection and execute an MDX query and return the results. When you deploy a stored proc assembly you have the option of specifying the account to impersonate.

Performance could be an issue with this approach as there is a bit of overhead involved in calling out to a stored procedure, but if your calc is at a fairly high level this might be a solution.

|||

Well, you give me a wisdom, thank you very much!

A more question, because I've never used a .Net stored procedure for Analysis Services. Do you mean that through it I can make the calculations in the script of the cube being executed by a specified user with all the necessary rights, and, after the calculations are realized on all the involved cells, then I can make the application to connect back as the current user to query the cube (therefore, seeing the previous results but only for the allowed cells)?

It'd be great.

have you any examples?

Thank you very very much!!!!

|||Check out this link for examples: http://www.codeplex.com/ASStoredProcedures|||

The AS Stored Procedure project has some good examples (I wrote some of them). However I don't think we have any that do exactly what you are after.

What would happen is that from a "limited" user, they would request a value.

That value would be calculated by a stored procedure.

When the stored procedure is deployed it can be configured to run under a specific user account.

In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.

This would return a cellset and the stored proc would extract the value of the required cell and return that value|||

Thank you very much again.

You say: “

v In the stored proc it would run .Net code to run a query back against the current cube, but using a new connection (running as the account specified when the assembly was deployed). It would have to issue a full MDX query.

v This would return a cellset and the stored proc would extract the value of the required cell and return that value

“

But I wonder if in this way the “poor” user (with limited rights) in Role1, issuing the query through the specified user for th AS SP, would be allowed to see also the cells he is forbidden to… In fact I need him to see only the cells he is allowed to, but with the values calculated with calculations involving all the cells (even the ones he is not allowed to). Is this possible this way?

However, I give you an example of what I need to do. Let’s say I need to do something like in the following script (a very simplified version of my script, but just to understand the type of calculation).

I have a dimension [DIM Mesi] with four key members [01], [02], [03], [00] and a one-level attribute hierarchy [Dim Mesi].

Role1 can access only the “[DIM Mesi].[DIM Mesi].&[01]”, “[DIM Mesi].[DIM Mesi].&[02]” and the ”existing” with these ones (I mean the ancestors of these members, which in this case, is the AllMember).

There is a basket member [DIM Mesi].[DIM Mesi].&[00] from where I take the values to be reversed on the three members [01], [02], [03] from the Measure [Costi1] into the same measure (or even in a different measure).

The calculations are something similar to the ones below.

I’d like the users of Role1 be allowed to see 1/3 of the values of cells of the &[00] member in the cells of &[01] and &[02], but they should not be able to see nor the &[00] member related cells neither the &[03] member related cells.

Further, the [DIM Mesi].[DIM Mesi].&[00] should be assigned a 0 value (even if the user can’t see that value) so that the total is not twice, in case I don’t use VisualTotals.

If possible, I’d like to even be able to decide whether he must see the Visual Totals or not, but I suppose that through the AS SP executed by another user I can only see the Actual Totals, or am I wrong?

THANK YOU VERY MUCH for your kind help!

CALCULATE;

CREATE SET CURRENTCUBE.[GoodMembers]

AS {

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[01]")), {}, [DIM Mesi].[DIM Mesi].&[01]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[02]")), {}, [DIM Mesi].[DIM Mesi].&[02]),

iif(IsError(StrToMember("[DIM Mesi].[DIM Mesi].&[03]")), {}, [DIM Mesi].[DIM Mesi].&[03])

};

scope

(

{[Measures].[Costi1]}

,

{

[GoodMembers]

}

);

this= [DIM Mesi].[DIM Mesi].&[00]/3

;

freeze(this);

end scope;

scope

([Measures].[Costi1],

{

[DIM Mesi].[DIM Mesi].&[00]

}

);

this= 0;

end scope