1

Topic: Whether it is possible such LEFT OUTER JOIN?

There is a request of type such,  a record count (> 1) works not quickly and causes locks.
Whether it is possible to dodge in another way somehow to accelerate?

SELECT t1_id, t2_name, t3_int, t4_text, t5_value
FROM t1
LEFT OUTER JOIN t2 ON (t2_id = t1_id)
LEFT OUTER JOIN t3 ON (t3_id = t1_id)
LEFT OUTER JOIN t4 ON (t4_id = t1_id)
LEFT OUTER JOIN t5 ON (t5_id = t1_id)

2

Re: Whether it is possible such LEFT OUTER JOIN?

Rolg Hupin;
The plan () is necessary to us

3

Re: Whether it is possible such LEFT OUTER JOIN?

Rolg Hupin;
All data from tables without any restrictions is selected?

4

Re: Whether it is possible such LEFT OUTER JOIN?

So... If to drag on the client "> 1" records - it always will be "".
Though of a skin get out.

5

Re: Whether it is possible such LEFT OUTER JOIN?

iap wrote:

Rolg Hupin;
All data from tables without any restrictions is selected?

Simplified and passed, there is there a restriction on principal type such
...where t1_id in (select t_id from t where parent_id = parent_id)

6

Re: Whether it is possible such LEFT OUTER JOIN?

Rolg Hupin;
In simple variant Recompile + to look the plan, in a difficult variant to look the plan smile

7

Re: Whether it is possible such LEFT OUTER JOIN?

Rolg Hupin;
Indexes though any already are?

8

Re: Whether it is possible such LEFT OUTER JOIN?

... where
In it...  it is covered.

9

Re: Whether it is possible such LEFT OUTER JOIN?

Hupin wrote:

Is request of type such,  a record count (> 1) works not quickly and causes locks.

to Look, whether there are indexes on t2_id etc.
If it is the cluster PC some acceleration of sampling (for the account of deceleration of an insertion) can give creation not cluster unique  a type t2_id include (t2_name)
Indexes on the table t1, samplings corresponding to conditions are still necessary.
If in request there is a line order by indexes should consider it.
Certainly, is better to optimize, looking at the plan instead of so, it is speculative.
Well and not to forget that obtaining of a considerable quantity of the data on the client too needs time.

10

Re: Whether it is possible such LEFT OUTER JOIN?

Here on any the index on t1_id is necessary, and  nobody can prompt to you without details (the plan, indexes, the sizes of tables, etc.).
Want to avoid locks, with (nolock) helps, if business model allows.

11

Re: Whether it is possible such LEFT OUTER JOIN?

Idol_111 wrote:

Want to avoid locks, with (nolock) helps, if business model allows.

What for a foolish habit to advise to use  on the left and to the right?  is an extreme measure when other approaches do not work. All right, if you understand for what this is necessary nolock and in what it can result, but a heap of beginners see such councils and think that they now to themselves make all mega quickly and get rid of locks, these start to put  in all requests absolutely thoughtlessly! And then also to another will advise. And what turns out as a result?
In the given specific case of the HARDWARE did not result the request execution plan, did not tell what at it there are indexes. What for to it to advise ?

12

Re: Whether it is possible such LEFT OUTER JOIN?

Sybex;

wrote:

In the given specific case of the HARDWARE did not result the request execution plan, did not tell what at it there are indexes. What for to it to advise ?

For the sake of interest, tell to us as the description of indexes affects usage NOLOCK

13

Re: Whether it is possible such LEFT OUTER JOIN?

TaPaK wrote:

for the sake of interest, tell to us as the description of indexes affects usage NOLOCK

Directly - in any way. I only spoke about to learning people to push thoughtlessly  apropos and without. And the description of indexes is necessary for councils about optimization of specific request of the HARDWARE. Probably after optimization at it the problem with locks disappears. And here if it optimizes all, but the problem of locks remains, here already and councils about NOLOCK, isolation level UNCOMMITED and other, other, other will be necessary... Problems should be solved in process of their arrival.

14

Re: Whether it is possible such LEFT OUTER JOIN?

Sybex;
You should look at a problem in all its volume, instead of to pull out the suitable facts and parameters.
Initially we have GREAT VOLUME of the data and even if you help the guy to adjust indexes, with the big share of probability the request all the same will be slow enough and will lock  processes.
I hope you guess that great volume of the data do not pull out to analyze, correct and push reversely. It is explicit  (or that type). And here in this case absolutely not professionally not to use nolock.
Whence you took that it is the last boundary? It is not necessary to confuse yellow to a giraffe, indexes for one,  for another.
I (new and old) kicks drive the developers, that they used  where it probably.
Naturally, you are right, it is necessary to understand when and what for you use them, but  sphere (and not only) generally a place where in the beginning it is necessary to think, and then to do.

15

Re: Whether it is possible such LEFT OUTER JOIN?

wrote:

And here in this case absolutely not professionally not to use nolock.

Professionally to use READUNCOMMITED... Well well
Locks solves RCSI (if business the model allows), and to optimize on a request piece it is a fantasy. And yes in the majority  it means more that you do something not so

16

Re: Whether it is possible such LEFT OUTER JOIN?

TaPaK wrote:

it is passed...
Professionally to use READUNCOMMITED... Well well
Locks solves RCSI (if business the model allows), and to optimize on a request piece it is a fantasy. And yes in the majority  it means more that you do something not so

+1
Here neither to add, nor to lower.

17

Re: Whether it is possible such LEFT OUTER JOIN?

Kolosov wrote:

... where
In it...  it is covered.

Not absolutely, more precisely...  - it everywhere, but in this case it was in Outer Join.
Removed all of them, replaced on  (select more precisely.... from... where) also became on the order faster, well and accordingly left dead-ovskie locks.

18

Re: Whether it is possible such LEFT OUTER JOIN?

Hupin wrote:

Removed all of them, replaced on  (select more precisely.... from... where) also became on the order faster, well and accordingly left dead-ovskie locks.

C all request overall runtime, and time to first fetch decreased not.

19

Re: Whether it is possible such LEFT OUTER JOIN?

Rolg Hupin;

wrote:

Removed all of them, replaced on  (select more precisely.... from... where) also became on the order faster, well and accordingly left dead-ovskie locks.

as it

20

Re: Whether it is possible such LEFT OUTER JOIN?

TaPaK wrote:

it is passed...
Professionally to use READUNCOMMITED... Well well
Locks solves RCSI (if business the model allows), and to optimize on a request piece it is a fantasy. And yes in the majority  it means more, what you do something not so

judging by logicians of your reasonings you not work as the administrator, and ?
The administrator works with that that is already created. And cases, when in OLTP system pushed (or want) something from OLAP (type  or the analysis), pretty often.
And your surprise surprises me.
Visible your reality differs from mine, and to you carried more smile.