1

Topic: Request with a considerable quantity logical I/O

Good afternoon!
There is a request:

SELECT DISTINCT e. Tbl1CUID
FROM Sg. Tbl1 AS e WITH (NOLOCK)
JOIN Sg. [Srv] s
ON s. SrvID = e. SrvID
LEFT JOIN [dbo]. [W_SrvCf] c
ON c. SrvID = s. SrvID
JOIN Sg. Ftr f
on f. SrvID = s. SrvID
JOIN Sg. PgFtrMp pakF
ON pakF.FtrID = f. FtrID
JOIN Sg. RLCap cap (NOLOCK)
ON cap. PkID = pakF.PkID
JOIN dbo. W_UPrMp uMap (NOLOCK)
ON uMap. UGUID = cap. RLID
JOIN dbo. CCMGr g (NOLOCK)
on g. OwBGrID = uMap. AuthRLID
AND uMap. IsPP = 1
JOIN dbo. W_NDtl det (NOLOCK)
ON det. NID = g. GID
LEFT JOIN dbo. W_RLFtrMp relF (NOLOCK)
ON relF.AmUID = uMap. AUID
AND relF.FtrId = pakF.FtrID
LEFT JOIN dbo. W_NWFtrMp netF (NOLOCK)
ON netF.NID = det. NID
AND netF.FtrId = pakF.FtrID
JOIN dbo. CCUM mem (NOLOCK)
on mem. MBOfGID = det. NID
WHERE mem. AmUID = @AUID AND det. NID = @GID and
ISNULL (netF.IsA, ISNULL (relF.IsA
,CASE WHEN pakF.IIMTID IN (1,2) Then 1 - ON, OFF
ELSE 0
END)) =1 and
ISNULL (netF.IE
,ISNULL (relF.IE
,CASE pakF.IIMTID WHEN 1 Then 1 - ON
ELSE 0
END)) =1

The most problem part here:

ISNULL (netF.IsA, ISNULL (relF.IsA
,CASE WHEN pakF.IIMTID IN (1,2) Then 1 - ON, OFF
ELSE 0
END)) =1 and
ISNULL (netF.IE
,ISNULL (relF.IE
,CASE pakF.IIMTID WHEN 1 Then 1 - ON
ELSE 0
END)) =1

Whether here at first it is checked there is a value in netF.IsA if it is, this value, if NULL it is checked relF.IsA if also it NULL it is checked pakF.IIMTID undertakes. Too most and in the second condition.
Whether it is possible to rewrite differently somehow this part that was less logical I/O? It is request it is fulfilled very often and creates loading on the server.
Thanks.

2

Re: Request with a considerable quantity logical I/O

Miracles, , does not happen.
Unless only is shorter

coalesce (netF.IsA, relF.IsA, CASE WHEN pakF.IIMTID IN (1,2) Then 1 ELSE 0 END) = 1

3

Re: Request with a considerable quantity logical I/O

And in a more way and  - it is necessary DISTINCT to liquidate, for it is grouping, and grouping - universal harm.
And at  under universal angrily push join heaps of tables. Though, on sense all it is reduced to

SELECT DISTINCT e. Tbl1CUID FROM Sg. Tbl1 AS e
where exists (select * from Sg. [Srv] s
LEFT JOIN [dbo]. [W_SrvCf] c
ON c. SrvID = s. SrvID
JOIN Sg. Ftr f
on f. SrvID = s. SrvID
JOIN Sg. PgFtrMp pakF
ON pakF.FtrID = f. FtrID
JOIN Sg. RLCap cap (NOLOCK)
ON cap. PkID = pakF.PkID
JOIN dbo. W_UPrMp uMap (NOLOCK)
ON uMap. UGUID = cap. RLID
JOIN dbo. CCMGr g (NOLOCK)
on g. OwBGrID = uMap. AuthRLID
AND uMap. IsPP = 1
JOIN dbo. W_NDtl det (NOLOCK)
ON det. NID = g. GID
LEFT JOIN dbo. W_RLFtrMp relF (NOLOCK)
ON relF.AmUID = uMap. AUID
AND relF.FtrId = pakF.FtrID
LEFT JOIN dbo. W_NWFtrMp netF (NOLOCK)
ON netF.NID = det. NID
AND netF.FtrId = pakF.FtrID
JOIN dbo. CCUM mem (NOLOCK)
on mem. MBOfGID = det. NID
WHERE mem. AmUID = @AUID AND det. NID = @GID and
ISNULL (netF.IsA, ISNULL (relF.IsA
,CASE WHEN pakF.IIMTID IN (1,2) Then 1 - ON, OFF
ELSE 0
END)) =1 and
ISNULL (netF.IE
,ISNULL (relF.IE
,CASE pakF.IIMTID WHEN 1 Then 1 - ON
ELSE 0
END)) =1
and s. SrvID = e. SrvID
)

And, it is not eliminated that Tbl1CUID in Sg. Tbl1 it is unique and distinct  it is not necessary.
Well and further - only indexes... Before indexed representations, inclusively.

4

Re: Request with a considerable quantity logical I/O

Removal DISTINCT did not refine a situation.

5

Re: Request with a considerable quantity logical I/O

Roust_m wrote:

Removal DISTINCT did not refine a situation.

If all so is simple - delete request absolutely.
There is no request - there is no problem.
. Stupid removal DISTINCT is useless.

6

Re: Request with a considerable quantity logical I/O

aleks222 wrote:

it is passed...
If all so is simple - delete request absolutely.
There is no request - there is no problem.
. Stupid removal DISTINCT is useless.

Stalin approaches, however...

7

Re: Request with a considerable quantity logical I/O

aleks222 wrote:

join heaps of tables.

Problem not in Where - and as it has been told - in heaps join - .

8

Re: Request with a considerable quantity logical I/O

I would make expressions from WHERE request calculated fields and constructed on them an index. And, added in it the necessary columns and made the filter, i.e. it is not necessary to store an index on conditions which in request will be filtered:
CREATE INDEX (...) INCLUDE (...) WHERE
Something of type of the materialized representations. Then the request will take the data directly from an index. And only what are really necessary. It is most real method to reduce logical reads.

9

Re: Request with a considerable quantity logical I/O

Massa52 wrote:

it is passed...
Problem not in Where - and as it has been told - in heaps join - .

Problem in WHERE. If to remove a heap enclosed ISNULL and to request one field logical I/O practically disappear.
Here only the result of request becomes wrong.

10

Re: Request with a considerable quantity logical I/O

Roust_m wrote:

it is passed...
Problem in WHERE. If to remove a heap enclosed ISNULL and to request one field logical I/O practically disappear.
Here only the result of request becomes wrong.

If to remove request - logical I/O disappear absolutely => a problem, , in request.

11

Re: Request with a considerable quantity logical I/O

[V. Liseev] I would make expressions from WHERE request calculated fields and constructed on them an index. And, added in it the necessary columns and made the filter, i.e. it is not necessary to store an index on conditions which in request will be filtered:
CREATE INDEX (...) INCLUDE (...) WHERE
Something of type of the materialized representations. Then the request will take the data directly from an index. And only what are really necessary. It is most real method to reduce logical reads. [/quote]
It is impossible to create indexed representation, for in request is "left join". sad

12

Re: Request with a considerable quantity logical I/O

Roust_m;
And if to rewrite c the help union?
Certainly not so will be compact to look, but can help the optimizer

13

Re: Request with a considerable quantity logical I/O

Ilnur26 wrote:

Roust_m;
And if to rewrite c the help union?
Certainly not so will be compact to look, but can help the optimizer

Eagles,  - classical .
For, if it was necessary"to it, it would result determinations of tables, convergence on number of records in them and the request plan.
And it of a current .

14

Re: Request with a considerable quantity logical I/O

Roust_m wrote:

it is passed...
It is impossible to create indexed representation, for in request is "left join". sad

it is visible, I not clearly expressed.
It is important to make request "" (  ). For this purpose it is necessary to remove calls of functions and CASE from WHERE. That is, instead of

ISNULL (A, B) = 1

it is better to write

A IS NULL AND B = 1 OR A = 1

Then the optimizer can use indexes. Otherwise no indexes help and will be .
And the following stage - all calculations to do not at a stage , and at an inserta/update stage. That is, to add in the table a calculated field c mark PERSISTED. And to construct on it an index (  ). Here these here CASE it is possible to push in a calculated field.
Any representations here it is not necessary

15

Re: Request with a considerable quantity logical I/O

[V. Liseev] it is better to write

A IS NULL AND B = 1 OR A = 1

[/quote] So it is better not to write.
At least so:

(A IS NULL AND B = 1) OR A = 1

16

Re: Request with a considerable quantity logical I/O

Minamoto wrote:

it is passed...
So it is better not to write.
At least so:

(A IS NULL AND B = 1) OR A = 1

It is possible and (And * B) + 5, but, I remember a priority of operators (  ) and I consider that all programmers remember it. Since Okkam taught me to have a shave (entia non sunt multiplicanda praeter necessitatem), I do not multiply real needlessly. Accordingly, brackets are necessary only for explicit change of a priority.
However, it is a question of style and the agreements accepted in a command.

17

Re: Request with a considerable quantity logical I/O

[V. Liseev] it is passed...
It is possible and (And * B) + 5, but, I remember a priority of operators (  ) and I consider that all programmers remember it. Since Okkam taught me to have a shave (entia non sunt multiplicanda praeter necessitatem), I do not multiply real needlessly. Accordingly, brackets are necessary only for explicit change of a priority.
However, it is a question of style and the agreements accepted in a command. [/quote]
Totally ludicrous.  to write with brackets, instead of to slobber about a priority of operators and to rake another's misses in their hierarchy

18

Re: Request with a considerable quantity logical I/O

V. Liseev;
And yes razor Okkama taught you to nothing, for entities here is not added

19

Re: Request with a considerable quantity logical I/O

[V. Liseev] it is passed...
It is possible and (And * B) + 5, but, I remember a priority of operators (  ) and I consider that all programmers remember it. Since Okkam taught me to have a shave (entia non sunt multiplicanda praeter necessitatem), I do not multiply real needlessly. Accordingly, brackets are necessary only for explicit change of a priority.
However, it is a question of style and the agreements accepted in a command. [/quote]
, taught to have a shave (???)
"We should not accept any position as not subject to a substantiation, if only it not a logical output, or something which have been checked up on experience, or the pious instruction demanding from us to arrive so, instead of differently ..."

20

Re: Request with a considerable quantity logical I/O

TaPaK wrote:

V. Liseev;
And yes razor Okkama taught you to nothing, for entities here is not added

Here brackets which are not necessary are added.

21

Re: Request with a considerable quantity logical I/O

aleks222 wrote:

it is passed...
Eagles,  - classical .
For, if it was necessary"to it, it would result determinations of tables, convergence on number of records in them and the request plan.
And it of a current .

I so cannot simply spread determination of tables, I even ciphered names of tables, for at us the organization serious.
It is necessary to rename all columns and tables.

22

Re: Request with a considerable quantity logical I/O

[V. Liseev] it is passed...
Probably, I not clearly expressed.
It is important to make request "" (  ). For this purpose it is necessary to remove calls of functions and CASE from WHERE. That is, instead of

ISNULL (A, B) = 1

it is better to write

A IS NULL AND B = 1 OR A = 1

Then the optimizer can use indexes. Otherwise no indexes help and will be .
And the following stage - all calculations to do not at a stage , and at an inserta/update stage. That is, to add in the table a calculated field c mark PERSISTED. And to construct on it an index (  ). Here these here CASE it is possible to push in a calculated field.
Any representations here it is not necessary [/quote]
Problem in that fields from different tables as them to add in one table I not absolutely I understand:
ISNULL ( netF.IsA , ISNULL ( relF.IsA
,CASE WHEN pakF.IIMTID IN (1,2) Then 1 - ON, OFF
ELSE 0
END)) =1
90 % of time of execution of request are necessary on index seek only one table relF.IsA,  there are not present, but as seek is fulfilled about 1200 times, there are many logical reads. 99 % of logical reads too have on the same table.
I tried to write something like:
(netF.IsA = 1 or (netF.IsA is NULL and relF.IsA = 1) or
(netF.IsAis NULL and relF.IsA is NULL and pakF.IIMTID IN (1,2)))
It at all did not change an amount of logical reads and the plan, similar such.

23

Re: Request with a considerable quantity logical I/O

Roust_m wrote:

I so cannot simply spread determination of tables, I even ciphered names of tables, for at us the organization serious.

Then there is no sense .

Roust_m wrote:

the Problem that fields from different tables as them to add in one table

I did not say that in one.

Roust_m wrote:

but as seek it is fulfilled about 1200 times, there are many logical reads.

is normally from 10 million "much". When any LOOP JOIN twists in a cycle heavy enough subquery.

Roust_m wrote:

It at all did not change an amount of logical reads and the plan, similar such.

Here it is difficult to guess that happens, if all is coded.