1

Topic: Performance of function of 1 times

Good afternoon;
There is such request

Select * from myTable Where userID = dbo.fn_current_user_id ()

Whether there is a possibility to give the help that function was fulfilled only 1 time, instead of for each record?
PS: for what reasons I do not know, but noted that the code works faster more low

Where userID = (Select dbo.fn_current_user_id ())

2

Re: Performance of function of 1 times

Samir, yes, is.

declare @userId int;
select @userId = dbo.fn_current_user_id ();
Select * from myTable Where userID = @userId;

3

Re: Performance of function of 1 times

And why it is impossible to appropriate at first in a variable, and then to reuse this variable?

4

Re: Performance of function of 1 times

Minamoto;
There is one problem which forgot to mention.
This function is used in representations for preliminary filtering.
It is possible there somehow DECLARE ?

5

Re: Performance of function of 1 times

Samir wrote:

PS: for what reasons I do not know, but noted that the code works faster more low

Where userID = (Select dbo.fn_current_user_id ())

This  transforms not determined function to the determined.
It is known since MS SQL 2000.
Truth then, there was convergence that "does not help" with new versions.
But if helps - use.

6

Re: Performance of function of 1 times

Samir wrote:

Minamoto;
There is one problem which forgot to mention.
This function is used in representations for preliminary filtering.
It is possible there somehow DECLARE ?

It is possible, but will be  :-D

7

Re: Performance of function of 1 times

Samir wrote:

Minamoto;
There is one problem which forgot to mention.
This function is used in representations for preliminary filtering.
It is possible there somehow DECLARE ?

It is possible. To alter representation in function or stored procedure.
On my taste, function usage in representation is a bad form.

8

Re: Performance of function of 1 times

Samir;

Samir wrote:

Good afternoon;
There is such request

Select * from myTable Where userID = dbo.fn_current_user_id ()

Whether there is a possibility to give the help that function was fulfilled only 1 time, instead of for each record?
PS: for what reasons I do not know, but noted that the code works faster more low

Where userID = (Select dbo.fn_current_user_id ())

And so it is possible?

Select * from myTable
inner join
(Select dbo.fn_current_user_id () user_curr) u
on userID = user_curr

9

Re: Performance of function of 1 times

Samir wrote:

Minamoto;
There is one problem which forgot to mention.
This function is used in representations for preliminary filtering.
It is possible there somehow DECLARE ?

It is possible. In CTE.

10

Re: Performance of function of 1 times

LSV wrote:

it is passed...
It is possible. In CTE.

It as?
It would be desirable to look at an example.

11

Re: Performance of function of 1 times

Whether

Samir wrote:

There is a possibility to give the help that function was fulfilled only 1 time, instead of for each record?

Helps are not necessary also all of them equally are not present. Simply function should be determined.
For this purpose:
- Function should not address to the data
- Should be created with an option schemabinding

12

Re: Performance of function of 1 times

iap wrote:

it is passed...
It as?
It would be desirable to look at an example.

ok

declare @x int
set @x=12;
with sss
as
(select ID from Item where ID <@x)
select * from sss

: Strangely enough, but without ";" . smile

13

Re: Performance of function of 1 times

Truth CTE it  only in specific .
I.e. to use it it is possible only repeatedly copying in different pieces of the code.
But it is convenient, parametric it is controlled and unlike the normal I twist knows about we say lies. Tables.

14

Re: Performance of function of 1 times

petre wrote:

Samir;
[/code]

And so it is possible?

Select * from myTable
inner join
(Select dbo.fn_current_user_id () user_curr) u
on userID = user_curr

I can write Cross Apply is gives to speed?