1

Topic: in temporary barracks much more slowly, than simply request (returns 8 lines)

in temporary barracks much more slowly, than simply request (returns 8 lines)
Visually - second against three
Request
[spoiler]

use **********
SET STATISTICS TIME ON
declare @p_samAccountName nvarchar (100) = ' ******** ';
declare
@Activities table
(
[ActivityDimKey] [int] NULL;
[Activity_id] [nvarchar] (256) NULL;
[Activity_Title] [nvarchar] (500) NULL;
[Activity_assignedToUser] [nvarchar] (500) NULL;
[Activity_StatusValue] [nvarchar] (256) NULL;
[select_note] [nvarchar] (200) NULL;
[Activity_Area] [nvarchar] (4000) NULL;
insertDate datetime
)
/*insert into @Activities
(
ActivityDimKey;
Activity_id;
Activity_Title;
Activity_StatusValue;
[Activity_assignedToUser];
select_note;
Activity_Area;
insertDate
)*/
SELECT a. ActivityDimKey;
a.id Activity_id;
a.title Activity_title;
AStatus. ActivityStatusValue Activity_StatusValue;
pers.sAMAccountName Activity_AssignedToUser_sAMAccountName;
SUBSTRING (a.id, 1, 2) + ': ' + N' and subordinates ' select_note;
AreaDS.displayName Activity_Area;
getdate ()
FROM [DWDataMart]. [dbo]. [ActivityDimvw] AS A
JOIN [DWDataMart]. [dbo]. [WorkItemDimvw] AS WI ON A.EntityDimKey = WI.EntityDimKey
JOIN [DWDataMart]. [dbo]. [WorkItemAssignedToUserFactvw] AS WIAssignedTo ON WIAssignedTo. WorkItemDimKey = WI.WorkItemDimKey AND WIAssignedTo. DeletedDate IS NULL
inner merge JOIN dbo.get_table_persons (@p_samAccountName) pers on pers. UserDimKey = WIAssignedTo.WorkItemAssignedToUser_UserDimKey
/*Status vypolnenija*/
LEFT JOIN [DWDataMart]. [dbo]. [ActivityStatus] AS AStatus ON AStatus. ActivityStatusId = A.Status_ActivityStatusId
LEFT JOIN [DWDataMart]. [dbo]. [ActivityAreavw] AS AArea ON AArea. ActivityAreaId = A.Area_ActivityAreaId
LEFT JOIN DisplayStringDimMV AS AreaDS ON AArea. EnumTypeId = AreaDS.BaseManagedEntityId AND AreaDS.LanguageCode = ' RUS'
WHERE ISNULL (A.IsDeleted, 0) = 0 - Action has not been remote
AND A.ActualStartDate IS NOT NULL - Action started to fulfill
AND A.Status_ActivityStatusId <> 3 - Action is not cancelled
AND isnull (AStatus. ActivityStatusValue, ' ') not in (' Completed ', ' Skipped ');

[/spoiler]
Without an insertion in temporary barracks

 A SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(8 row (s) affected)
SQL Server Execution Times:
CPU time = 2627 ms, [b] elapsed time = 934 ms. [/b]

With an insertion in temporary barracks

 a SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 3594 ms, [b] elapsed time = 3705 ms. [/b]
(8 row (s) affected)

Yours faithfully, Andrey

2

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

And such  works quickly

declare
@Activities table
(
[ActivityDimKey] [int] NULL;
[Activity_id] [nvarchar] (256) NULL;
[Activity_Title] [nvarchar] (500) NULL;
[Activity_assignedToUser] [nvarchar] (500) NULL;
[Activity_StatusValue] [nvarchar] (256) NULL;
[select_note] [nvarchar] (200) NULL;
[Activity_Area] [nvarchar] (4000) NULL;
insertDate datetime
)
insert into @Activities
(
ActivityDimKey;
Activity_id;
Activity_Title;
Activity_StatusValue;
[Activity_assignedToUser];
select_note;
Activity_Area;
insertDate
)
SELECT top 100 a. ActivityDimKey;
a.id;
a.title;
null;
null;
null;
null;
null
FROM [DWDataMart]. [dbo]. [ActivityDimvw] AS A

3

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

Unfortunately, at the plan I can not look... The rights did not give

4

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

So how many pages the first variable and how many the second as a result occupies?
I.e. 1 page second it differently;
And 8 lines, where  a line (probably) on half-pages;
These are 8 lines, moreover and not in dedicated extent, and in mixed.
I.e. generally can be in 8 different extents;
And all it is necessary

5

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

Yasha123;
Well it is direct selection of pages so much time it it is anomalous. I would sin on the variable table, replace on

6

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

andreymx wrote:

unfortunately, the plan I can not look... The rights did not give

Your way that, it is simple  and  the same  in a variable also different plans has?
Well except the operator , certainly, which is not present in the foreground

7

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

TaPaK;
Loading on  can at it ;
Which also lives in one file.

8

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

TaPaK wrote:

Yasha123;
Well it is direct selection of pages so much time it it is anomalous. I would sin on the variable table, replace on

By the way that generally for surprise such;
tempdb contention it very much even the widespread phenomenon;
Just because of here such   in <= 8 pages

9

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention
Purely for acquaintance.
If at it is not present showplan;
That of whom already  waitings on .
---
Still my hypothesis can be checked up so:
To launch the same code for the temporary table
And for a constant.
And here if temporal all the same brake, it it

10

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

The example is a part of a table valued function.
Remaining assumptions I will check up in the evening when I will reach a computer.

11

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

Yasha123;
Here then there was a question not about slow , and "ALL BRAKES RESCUE HELP" smile

12

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

As I already wrote, idle time  flies to temporary barracks of hundred lines

13

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

andreymx;
And try to leave inner merge join, but only to make null as Activity_AssignedToUser_sAMAccountName instead of pers.sAMAccountName as Activity_AssignedToUser_sAMAccountName...

14

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

andreymx wrote:

As I already wrote, idle time  flies to temporary barracks of hundred lines

To go nuts. Compared
SELECT top 100
And
5 join + 1 merge join to a table valued function (not the fact that )
c WHERE and (| all) without indexes
...
Yes at you can be anything you like, beginning spooling and finishing server .
...
If there is no access to SHOWPLAN;
Add to both requests statistics on IO
And OPTION (MAXDOP 1)
Add, as recommended, the third insert query in #activities

15

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

andreymx wrote:

As I already wrote, idle time  flies to temporary barracks of hundred lines

You interposed only 1 page, and you interpose not , and something of type replicate (N'a ', 4000)

16

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

andreymx;
Without  at you the parallel plan - CPU is much more elapsed. Therefore and second instead of three.

17

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

Damirovich wrote:

is passed...
To go nuts. Compared
SELECT top 100
And
5 join + 1 merge join to a table valued function (not the fact that )
c WHERE and (| all) without indexes
...
Yes at you can be anything you like, beginning spooling and finishing server .
...
If there is no access to SHOWPLAN;
Add to both requests statistics on IO
And OPTION (MAXDOP 1)
Add, as recommended, the third insert query in #activities

No, not so.
It  something is simple  the same, where 5 join and with an insertion ** the same ** in a variable

18

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

invm;
It is remembered, somehow in 2012 Pol White explained that very much does not like an engine to do parallel merge join.
There truth went speech about "There are ways to achieve running whole query plans on multiple threads over exclusive data set ranges, but they require trickery that not everyone will be happy with (and will not be supported by Microsoft or guaranteed to work in the future)" concerning the partitioned tables, but nevertheless.

19

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

invm wrote:

andreymx;
Without  at you the parallel plan - CPU is much more elapsed. Therefore and second instead of three.

something similar came a head

20

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

invm wrote:

andreymx;
Without  at you the parallel plan - CPU is much more elapsed. Therefore and second instead of three.

it is similar, all indeed
Added in request OPTION (MAXDOP 1), and both variants started to work on three
Adding OPTION (MAXDOP 3)  did not accelerate

21

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

Rewrote on the cursor - there was quickly, one
But this heap of fields and variables is not pleasant, in  all the same cursors it is more convenient
: I will leave in storage just in case

22

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

andreymx wrote:

rewrote on the cursor - there was quickly, one

Fairly, . To you told that the insertion in a tabular variable will be serial always. In the temporary table can be . From that that you write MAXPOD 3 it does not force a sequel  parallelism. For this purpose  others are used.

23

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

Tabular variables very slow. Use only in extreme cases.
Normal temporary barracks .
: greetings

24

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

AlanDenton wrote:

it is passed...
Fairly, . To you told that the insertion in a tabular variable will be serial always. In the temporary table can be . From that that you write MAXPOD 3 it does not force a sequel  parallelism. For this purpose  others are used.

thanks, I will think, good luck

25

Re: in temporary barracks much more slowly, than simply request (returns 8 lines)

LSV wrote:

Tabular variables very slow. Use only in extreme cases.
Normal temporary barracks .
: greetings

the function returning tabular data is necessary to me
Or even so:
It is necessary for me to generate the report in which it is a lot of formations, the data from different sources (tables and )
Normal  it is possible to draw, but it will be very bulky and
Here I want to break formations on procedures or functions
Advise, how it is correct